Understanding the ACID Properties of Database Transactions

Before we get into ACID properties of database transactions, let's look into transactions first.

Transaction

A transaction consists of one or more database operations, such as inserting, updating, or deleting records, grouped as a single, indivisible unit of work.

A transaction has four important properties, collectively known as ACID properties: Atomicity, Consistency, Isolation, and Durability. These properties ensure that transactions are processed reliably and consistently.

Transaction Lifespan

The lifespan of a transaction begins when it starts executing and ends when it is either committed or rolled back. During this period, the transaction performs one or more database operations, such as inserting, updating, or deleting records. These operations are executed as a single, indivisible unit of work, which means that if any part of the transaction fails, the entire transaction must be rolled back.

When a transaction starts, it enters an active state. During this state, the transaction executes its database operations and may acquire locks on database objects to prevent interference from other transactions.

If the transaction completes successfully, it enters a commit state. During this state, the changes made by the transaction are permanently saved to the database, and any locks acquired by the transaction are released.

If an error occurs during the transaction, it enters an abort state. During this state, any changes made by the transaction are rolled back, and any locks acquired by the transaction are released.

The lifespan of a transaction is significant because it determines the behavior of the database during and after the transaction.

Transaction Example

Let's take an example of a transaction. Suppose a bank employee transfers Rs 100 from SHA's account to SNE's account. This very simple and small transaction involves several low-level tasks.

Atomicity

It ensures that either all of the changes made by the transaction are committed to the database, or none of them are.

The atomicity property ensures that a transaction is treated as a single, indivisible unit of work and that either all of 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 must be rolled back.

Atomicity ensures that a transaction is processed reliably, even in the presence of system failures, network errors, or other types of interruptions. If a transaction is not atomic, it could leave the database in an inconsistent state, where some changes have been made but others have not.

For example, consider a banking system where a customer transfers money from one account to another. The transaction should either deduct the money from one account and add it to the other account or leave both accounts unchanged if any part of the transaction fails. If the transaction is not atomic, the money could be deducted from one account but not added to the other, leaving the database in an inconsistent state.

If the transaction fails after the completion of T1 but before the completion of T2. (say, after write(SHA) but before write(SNE)), then the amount has been deducted from SHA but not added to SNE. This results in an inconsistent database state. Therefore, the transaction must be executed in its entirety to ensure the correctness of the database state.

Consistency

It ensures that data remains accurate and valid by using constraints to specify rules that must be followed.

Database consistency* *states that only valid data will be written to the database. If a transaction is executed that violates the database's consistency rules, the entire transaction will be rolled back and the database will be restored to its original state. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules. There are two types of consistency: Read consistency and Write consistency.

Database consistency doesn't mean that the transaction is correct, only that the transaction didn't break the rules defined by the program. Database consistency is important because it regulates the data that is coming in and rejects the data that doesn't fit into the rules.

Consistency rules are often enforced through constraints at a field level. A constraint specifies a rule governing a given record or field-level value.

For example, a column in a database may only have the values for a coin flip as “heads” or “tails.” If a user were to attempt to put in “sideways,” consistency rules for the database would not allow it.

You may have experience with consistency rules about leaving a field on a web page form empty. When a person is filling out a form online and forgets to fill in one of the required spaces, a NULL value goes to the database, causing the form to be rejected until the blank space has something in it.

Isolation

It guarantees that concurrent transactions do not interfere with each other, leading to consistent and correct results.

Isolation is a property of a transaction that ensures that each transaction is executed in isolation from other transactions. This means that transactions should not interfere with each other, and each transaction should only see the state of the database that existed before it started.

Isolation ensures that the changes made by one transaction do not affect the results of another transaction. Without isolation, concurrent transactions could interfere with each other, leading to inconsistent or incorrect results.

For example, consider two customers accessing a banking system at the same time. Both customers want to transfer money from their accounts to a third account. If the transactions are not isolated, the transactions could interfere with each other, leading to incorrect results. For instance, if both transactions try to transfer money from the same account, one of the transactions may fail or both transactions may be processed with incorrect results.

Isolation Levels

Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level is defined by the following read phenomena:

• **Dirty Read - **A Dirty read is a situation when a transaction reads data that has not yet been committed. For example, Let's say transaction 1 updates a row and leaves it uncommitted, meanwhile, Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.
• **Non Repeatable read - **Non Repeatable read occurs when a transaction reads the same row twice and gets a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.
• **Phantom Read - **Phantom Read occurs when two same queries are executed, but the rows retrieved by the two are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.
• Lost update: Two transactions try to update the same record simultaneously, and one transaction's update is lost because it overwrites the other transaction's update without taking it into account. For instance, if two users try to book the same hotel room at the same time, the hotel reservation system may allow both bookings to proceed, resulting in a lost update of the room availability.

Based on these phenomena, The SQL standard defines four isolation levels:

• Read uncommitted is the lowest level of isolation and allows data to be accessed before the changes have been made.
• Read committed allows the data to be accessed after the data has been committed to the database, but not before then.
• Repeatable reads allow transactions to be accessed once the transaction has started, even if it hasn't finished. This level allows for phantom reads, or awareness of inserted or deleted rows even if changes to existing rows aren't readable.
• Serializable is the highest level, which means that one transaction must complete before another transaction can start.

Database Implementation of Isolation

• Pessimistic: In a pessimistic locking scenario, a transaction will acquire locks on data it intends to modify before it can read or modify it. For example, if a transaction wants to update a row in a table, it will acquire a lock on the row before it makes any changes. Other transactions will not be able to read or modify the same row until the first transaction releases its lock. This type of isolation level is typically used in high-concurrency environments where data consistency is more important than throughput.
• Optimistic: In an optimistic locking scenario, a transaction does not acquire locks on data it intends to modify until it is ready to commit. Instead, it assumes that no other transactions will modify the same data, and only checks for conflicts when it is ready to commit. If a conflict is detected, the transaction will roll back and retry. This type of isolation level is typically used in low-concurrency environments where data consistency is less important than throughput.
• Repeatable Read: In a repeatable read scenario, a transaction will acquire locks on all data it reads before it reads it. For example, if a transaction reads a row in a table, it will acquire a lock on the row before it reads it. Other transactions will not be able to modify or delete the same row until the first transaction releases its lock. This type of isolation level prevents non-repeatable reads, but phantom reads can still occur.
• Serializable: In a serializable scenario, a transaction will acquire locks on all data it reads and modifies before it reads or modifies it. For example, if a transaction wants to update a row in a table, it will acquire locks on the row before it reads or modifies it. Other transactions will not be able to read, modify, or delete the same row until the first transaction releases its locks. This type of isolation level prevents dirty reads, non-repeatable reads, and phantom reads, but it comes at the cost of reduced concurrency and increased locking.

There are a few main types of locks used in databases:

1. Row-level locks: These locks are used to lock individual rows in a table. When a transaction acquires a row-level lock on a particular row, no other transaction can modify that row until the lock is released.
2. Table locks: These locks are used to lock an entire table. When a transaction acquires a table lock, no other transaction can read or modify any part of the table until the lock is released.
3. Page locks: These locks are used to lock a range of pages in a table. When a transaction acquires a page lock on a particular range of pages, no other transaction can read or modify any data in that range of pages until the lock is released.
4. Shared locks: These locks allow multiple transactions to read a resource simultaneously but prevent any of them from modifying the resource.
5. Exclusive locks: These locks prevent other transactions from reading or modifying a resource while it is locked.

Durability

It guarantees that committed transactions will survive any kind of system failure and will not be lost or rolled back.

Durability is a property of a database system that ensures that once a transaction has been committed, its effects will persist even in the face of system failures such as power outages, hardware crashes, or software errors. This is typically achieved through techniques such as write-ahead logging and transaction commit protocols that guarantee that data changes are written to non-volatile storage and are recoverable in case of failure.

For example, Suppose you are running an e-commerce website that uses a database to store customer orders. When a customer places an order, the system creates a new transaction that inserts the order data into the database and updates the product inventory. Once the transaction is committed, the order is considered complete, and the inventory is reduced accordingly.

Now suppose that a power outage occurs just as the transaction is being committed, causing the server to shut down abruptly. If the database system has durability guarantees, it will ensure that the changes made by the transaction are saved to non-volatile storage (such as a disk or solid-state drive) before acknowledging the commit. When the server restarts, the system will recover the changes from the log and restore the database to a consistent state, even though the transaction was not fully completed before the power failure. As a result, the order will be correctly recorded in the database, and the inventory will be updated accordingly, ensuring that the e-commerce website remains reliable and consistent for its customers.

There are several durability techniques used in database systems to ensure that committed transactions are not lost, even in the event of hardware or software failures:

• Write-ahead logging (WAL): This technique involves writing all changes to the database to a log file before they are written to the actual database. If a failure occurs, the log can be replayed to bring the database back to a consistent state.
• Shadow paging: This technique involves making a copy of the entire database before any changes are made. If a failure occurs, the original database can be restored from the copy.
• Database replication: This technique involves maintaining multiple copies of the database on different machines or servers. If a failure occurs, one of the replicas can be used as a backup.
• Redundant arrays of independent disks (RAID): This technique involves using multiple disks to store data, with redundancy built in to ensure that data is not lost if one or more disks fail.
• Remote backup: This technique involves regularly backing up the database to a remote location, such as a cloud-based storage service or a secondary data center.