Transactions and Concurrency Control

Have you ever tried writing a letter and halfway through, you decide to start over? In databases, that's where transactions come in handy. Today, we'll explore how transactions and concurrency control keep your data accurate and consistent. Let's get started!

Table of Contents

1. What Are Transactions?
2. COMMIT and ROLLBACK
3. Isolation Levels
4. Deadlocks and Locking Mechanisms

What Are Transactions?

A transaction is like a single unit of work in a database. It's a sequence of operations performed as a single logical action. Think of it as a package deal—all or nothing.

Why Use Transactions?

Transactions ensure data integrity. They help you maintain a consistent state, even when something goes wrong during data manipulation.

COMMIT and ROLLBACK

Ever made changes you wish you could undo? In SQL, you can!

COMMIT

COMMIT saves all the changes made during the transaction.

Example

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT;

This transfers $100 from account 1 to account 2 and then commits the changes.

ROLLBACK

ROLLBACK undoes all the changes made during the transaction.

Example

BEGIN TRANSACTION;

UPDATE Products
SET Stock = Stock - 1
WHERE ProductID = 10;

-- Oops, something went wrong
ROLLBACK;

Here, we started a transaction but decided to roll back the changes due to an error.

Isolation Levels

Imagine sharing a document with someone else and trying to edit it at the same time. In databases, isolation levels control how transactions interact with each other.

Common Isolation Levels

• Read Uncommitted: Transactions can see uncommitted changes from other transactions.
• Read Committed: Transactions only see committed changes.
• Repeatable Read: Ensures that if you read data twice within the same transaction, it won't change.
• Serializable: The highest isolation level. Transactions are completely isolated from each other.

Setting Isolation Levels

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

-- Your SQL operations here

COMMIT;

Here, we've set the isolation level to SERIALIZABLE for maximum isolation.

Deadlocks and Locking Mechanisms

Deadlocks are like two people trying to pass each other in a narrow hallway—they both block each other. In databases, deadlocks happen when two transactions wait indefinitely for each other to release locks.

Avoiding Deadlocks

• Access resources in a consistent order.
• Keep transactions short and simple.
• Use lower isolation levels when appropriate.

Example of a Deadlock Scenario

-- Transaction 1
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

-- Transaction 2
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Both transactions now try to update the other's account and get stuck.

To prevent this, make sure transactions access resources in the same order.