Contents
Transactions
Transactions in SQL are used to ensure the integrity of the database by grouping multiple operations into a single unit of work. Transactions follow the ACID properties (Atomicity, Consistency, Isolation, Durability) to guarantee reliable and consistent transactions.
1. BEGIN, COMMIT, ROLLBACK
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.1.1 Savepoints
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.
1.2 Transaction Isolation Levels
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.Isolation Levels:
- 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.Detailed Example with Explanations
Let’s consider a practical example where we use transactions to ensure data integrity in a
BankAccounts table.Step 1: Creating the 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.Step 2: Inserting Sample Data
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.Step 3: Performing a Transaction
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.Step 4: Using Savepoints
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.Step 5: Setting Transaction Isolation Levels
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.