Database Transactions

A database transaction is a unit of work performed within a database management system (DBMS) against a database, and it is often composed of one or more operations (usually in the form of SQL statements). The primary purpose of a transaction is to provide a reliable way to ensure data consistency and integrity.

Transactions are crucial in scenarios where multiple users or processes may concurrently access and modify the database to prevent data corruption and ensure reliability

Database Transaction Process

Begin Transaction

A transaction begins with the "BEGIN TRANSACTION" statement. This indicates the start of a unit of work.

Execute SQL Statements

SQL statements (e.g., INSERT, UPDATE, DELETE) are executed within the transaction to make changes to the database.

Commit or Rollback

After executing the SQL statements, the transaction can be either committed (if the changes are to be made permanent) or rolled back (if the changes need to be undone). The "COMMIT" statement is used to commit the transaction, and the "ROLLBACK" statement is used to undo the changes and discard the transaction.

ACID Approach

The ACID approach is a set of properties that guarantee the reliability of database transactions. ACID properties are crucial for ensuring the reliability and integrity of database transactions, especially in scenarios where multiple transactions may be occurring concurrently or in the presence of system failures. 

ACID stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit of work.

Either all the changes made by the transaction are committed to the database, or none of them are.

If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.

Atomicity Example: Bank Transfer

A 16-year-old named John wants to transfer $100 from his savings account to his checking account in an online banking system.

To ensure atomicity, the online banking system can follow the following steps:

1. Begin a transaction and lock both John's savings and checking accounts.
2. Verify that John's savings account has at least $100 available.
3. Deduct $100 from John's savings account.
4. Add $100 to John's checking account.
5. Commit the transaction and unlock both accounts.

If any part of the entire process fails, the whole process is cancelled and the database state is rolled back to before the transaction

Consistency

Consistency ensures that a transaction brings the database from one valid state to another.

The database should satisfy certain integrity constraints before and after the transaction.

If a transaction violates any integrity constraints, it is rolled back, maintaining the consistency of the database.

Consistency Example: Negative Bank Balance

• Imagine a situation where a database maintains a constraint that ensures the total balance of all accounts is always greater than zero.
  
• 
  
• If a transaction would violate this constraint (resulting in a negative bank balance) , it is automatically rolled back. The consistency property ensures that the database remains in a consistent state before and after each transaction, maintaining the integrity of the data.

Isolation

Isolation ensures that the execution of one transaction is isolated from the execution of other transactions.

Even though multiple transactions may be executing concurrently, the final outcome should be as if they were executed one after the other.

Isolation prevents interference between transactions, maintaining data integrity.

Transaction Isolation Example

Suppose there are two transactions trying to update the same bank account simultaneously.

The isolation property ensures that each transaction is executed independently, as if no other transactions are taking place concurrently. This prevents interference between transactions, and the final result is the same as if the transactions were executed sequentially.

Durability

Durability guarantees that once a transaction is committed, its effects are permanent, even in the face of system failures.

The changes made by a committed transaction persist, and the database can recover to a consistent state after a system failure.

Durability is typically achieved through mechanisms like transaction logs and database backups.

Database Durability Example

After a user successfully transfers money between accounts, the system confirms the transaction and stores it in the transaction log of the database.

The durability property guarantees that once a transaction is committed, its effects persist even in the face of system failures. If the system crashes or experiences a power loss, the committed transaction's changes are preserved when the system recovers.