Transactions

Definition: Transactions are used to ensure the integrity of the database by grouping multiple operations into a single unit of work. The BEGIN statement starts a transaction, COMMIT saves the changes, and ROLLBACK undoes the changes if something goes wrong.

Example:

BEGIN TRANSACTION;

UPDATE Customers
SET Address = '456 New Street'
WHERE CustomerID = 1;

COMMIT;

This statement begins a transaction, updates a customer’s address, and commits the transaction. If all operations within the transaction succeed, the changes are saved permanently.

Usage Example:

If an error occurs during the transaction, you can roll back the changes:

BEGIN TRANSACTION;

UPDATE Customers
SET Address = '456 New Street'
WHERE CustomerID = 1;

-- Assume an error occurs here
ROLLBACK;

This statement undoes the changes made by the UPDATE statement because of the error.

Definition: Savepoints allow you to set points within a transaction to which you can later roll back. This is useful for partially undoing changes without affecting the entire transaction.

Example:

BEGIN TRANSACTION;

SAVEPOINT before_update;

UPDATE Customers
SET Address = '456 New Street'
WHERE CustomerID = 1;

-- If we decide to undo the update
ROLLBACK TO before_update;

COMMIT;

This statement begins a transaction, sets a savepoint, updates a customer’s address, rolls back to the savepoint, and commits the transaction. The ROLLBACK TO statement undoes the update without affecting the entire transaction.

Usage Example:

If you decide to proceed with some changes but undo others within the same transaction:

BEGIN TRANSACTION;

SAVEPOINT before_update;

UPDATE Customers
SET Address = '456 New Street'
WHERE CustomerID = 1;

-- Deciding to keep the update
COMMIT;

The update to the customer’s address is kept and the transaction is committed.

Definition: Transaction isolation levels define the degree to which the operations in one transaction are isolated from those in other transactions. This affects how changes made by one transaction are visible to others and helps prevent issues like dirty reads, non-repeatable reads, and phantom reads.

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT * FROM Customers;

COMMIT;

This statement sets the transaction isolation level to SERIALIZABLE, begins a transaction, selects data from the Customers table, and commits the transaction. SERIALIZABLE is the highest isolation level, ensuring complete isolation from other transactions.

• READ UNCOMMITTED: Allows dirty reads, where a transaction can see uncommitted changes from other transactions.
• READ COMMITTED: Prevents dirty reads by ensuring that only committed changes are visible.
• REPEATABLE READ: Prevents dirty and non-repeatable reads by holding locks on all rows a query touches.
• SERIALIZABLE: Prevents dirty, non-repeatable, and phantom reads by locking entire ranges of rows.

Usage Example:

Set a lower isolation level for better performance in read-heavy scenarios:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT * FROM Customers;

COMMIT;

This statement sets the isolation level to READ COMMITTED, ensuring that only committed changes are visible while allowing for better performance than SERIALIZABLE.

Let’s consider a practical example where we use transactions to ensure data integrity in a BankAccounts table.

First, let’s create a BankAccounts table to work with:

CREATE TABLE BankAccounts (
    AccountID int PRIMARY KEY,
    AccountHolder varchar(255),
    Balance decimal(10, 2)
);

This statement creates a BankAccounts table with AccountID, AccountHolder, and Balance columns.

Next, we insert some sample data into the BankAccounts table:

INSERT INTO BankAccounts (AccountID, AccountHolder, Balance)
VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

This statement inserts two rows into the BankAccounts table.

We will perform a transaction to transfer money from Alice’s account to Bob’s account:

BEGIN TRANSACTION;

UPDATE BankAccounts
SET Balance = Balance - 100.00
WHERE AccountID = 1;

UPDATE BankAccounts
SET Balance = Balance + 100.00
WHERE AccountID = 2;

COMMIT;

This transaction withdraws $100 from Alice’s account and deposits it into Bob’s account. The COMMIT statement saves the changes if both updates are successful.

If we want to ensure partial updates can be undone, we use savepoints:

BEGIN TRANSACTION;

SAVEPOINT before_withdrawal;

UPDATE BankAccounts
SET Balance = Balance - 100.00
WHERE AccountID = 1;

SAVEPOINT after_withdrawal;

UPDATE BankAccounts
SET Balance = Balance + 100.00
WHERE AccountID = 2;

-- If we decide to undo the deposit
ROLLBACK TO after_withdrawal;

COMMIT;

This transaction sets savepoints before and after withdrawing money from Alice’s account. If we decide to undo the deposit, we roll back to the after_withdrawal savepoint and commit the transaction.

To ensure data consistency during high-concurrency operations, we set an appropriate isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT * FROM BankAccounts
WHERE AccountID = 1;

-- Perform some operations

COMMIT;

Setting the isolation level to SERIALIZABLE ensures the highest level of isolation, preventing other transactions from affecting the current transaction’s operations.

By understanding and utilizing transactions, you can ensure the integrity and consistency of your database operations. This guide provides a detailed explanation of different aspects of transactions, including how to create, manage, and optimize them for various scenarios.