Lesson 39: Transactions and ACID Properties

A Transaction is a sequence of one or more SQL operations that are executed as a single, logical unit of work. Transactions are essential to maintaining data integrity, especially in concurrent environments (multiple users accessing the data simultaneously).

The ACID Properties

Every reliable DBMS guarantees the integrity of its data by adhering to the ACID properties:

1. Atomicity (Atomicité)

• Concept: The transaction must be treated as a single, indivisible unit. Either all of the operations are completed successfully, or none of them are.
• Analogy: Transferring money from Account A to Account B. If the deduction from A succeeds but the addition to B fails, the entire transaction is rolled back.

2. Consistency (Cohérence)

• Concept: A transaction must bring the database from one valid state to another. It ensures that data written to the database must obey all defined rules and constraints (PK, FK, NOT NULL).

3. Isolation

• Concept: Concurrent transactions must be isolated from each other. The result of simultaneous transactions should be the same as if they were executed sequentially.

4. Durability

• Concept: Once a transaction is committed (finished successfully), its changes are permanent, even in the event of a system failure (power loss, crash).

Basic Transaction Control

sql -- Start a transaction block BEGIN TRANSACTION;

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

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

-- If everything is okay, make changes permanent COMMIT;

-- If an error occurs, undo all changes since BEGIN -- ROLLBACK;