Transactions and ACID Properties

Introduction

A database transaction is a sequence of one or more SQL operations executed as a single unit of work. Transactions ensure data integrity and consistency, especially in multi-user environments where multiple processes access the database simultaneously. SQL databases follow the ACID properties to maintain reliability.

What is a Transaction?

A transaction represents a logical unit of work that must be executed completely or not at all. It starts with one or more SQL statements and can either be committed (saved) or rolled back (undone) if an error occurs.

Example of a Transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;

In this example, money is deducted from one account and added to another. If any statement fails, the changes can be rolled back to maintain consistency.

ACID Properties

The ACID properties ensure that database transactions are processed reliably. ACID stands for:

1. Atomicity

• Ensures that a transaction is treated as a single unit of work.
• If any part of the transaction fails, the entire transaction is rolled back.

Example:

START TRANSACTION;
DELETE FROM orders WHERE order_id = 1001;
INSERT INTO archive_orders VALUES (1001, 'Completed');
COMMIT;

If the INSERT statement fails, the DELETE statement is also rolled back, preventing partial execution.

2. Consistency

• Ensures the database remains in a valid state before and after the transaction.
• Transactions must follow database constraints, foreign keys, and rules.

Example: If a bank transfer violates the rule that account balances cannot be negative, the transaction is aborted.

3. Isolation

• Ensures that multiple concurrent transactions do not interfere with each other.
• Different isolation levels control how transactions interact with each other.

Isolation Levels:

• Read Uncommitted: Allows reading uncommitted data, leading to dirty reads.
• Read Committed: Ensures that only committed data is read.
• Repeatable Read: Prevents non-repeatable reads but allows phantom reads.
• Serializable: Highest level of isolation, preventing all concurrency issues.

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE id = 5;
UPDATE customers SET balance = balance - 500 WHERE id = 5;
COMMIT;

Using SERIALIZABLE ensures that no other transaction can modify or read the same data until this transaction is complete.

4. Durability

• Ensures that once a transaction is committed, it remains permanently stored in the database.
• Even if the system crashes, the changes persist.

Example: In MySQL, the InnoDB storage engine ensures durability by writing committed transactions to logs that persist after crashes.

Implementing Transactions in SQL

Using MySQL:

START TRANSACTION;
UPDATE inventory SET stock = stock - 10 WHERE product_id = 123;
UPDATE sales SET quantity_sold = quantity_sold + 10 WHERE product_id = 123;
COMMIT;

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

ROLLBACK;

Using PostgreSQL:

BEGIN;
UPDATE users SET status = 'active' WHERE user_id = 10;
COMMIT;

Using SQL Server:

BEGIN TRANSACTION;
UPDATE orders SET status = 'Shipped' WHERE order_id = 2023;
IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

Conclusion

Transactions and ACID properties ensure database reliability by maintaining consistency, integrity, and security. Understanding these concepts is essential for developing robust database applications, especially in scenarios requiring high data reliability, such as banking, inventory management, and user authentication systems.