ACID Properties of Transaction Processing Systems

Relational databases are characterized by transactions and are hence also termed as transaction processing systems. These transactions have put in place and have logically evolved due to reasons of concurrent usage of databases. The four properties that are required for any transactions are Atomicity, Consistency, Isolation and Durability.

Concurrent usage means that many users access the database in order to view a page, inserting new records or updating old records. Typical multi user usage is many persons trying to book a train ticket online. Typical database operations that involve more than one person are a funds transfer banking transaction between two persons.

Let us take the example of the later, a funds transfer between two persons has two main database operations. After reading the balance from the first persons bank account the amount to be transferred has to be deducted from the first persons bank account, next the second persons bank account has to be updated.

Consider the situation that there is a power failure after the first transaction namely deducting the amount to be transferred to the second persons bank account. There will be an error in the pair of transactions as the second part of the transaction namely that of increasing the balance in the second person’s account will not be completed m but the first transaction namely the debit would be completed. So it is necessary that both the transactions execute together within the same transaction window and also if the second transaction is not completed, the first one should be reversed. This gives rise to the Atomicity property of transactions. In popular relational database terminology this is termed as commit and rollback of transactions,

The second property is consistency, the database should remain consistent at all times. In the example above the sum of the balance in the first account and the balance in the second account should be a constant value always.

The third property is “isolation” of transactions. For this let us take the example of an online railway reservation system. For example let us consider that there are 2 users who are trying to block 2 and 3 seats respectively on a train between the same destinations running on the same date and time. If the total number of available seats is only 3 then if these two transactions are executed simultaneously then due to absence of any sequencing of these two requests one may find that the seats alloted to the two users may be 2 and 1 or 1 and 2 respectively or 0 and 3 after performing one users transaction completely would mean that these two transactions should not be executed at the same time. They must be executed serially one after the other or in other words when 1 user accesses the reservation system the corresponding record related to the seat reservation should be locked exclusive to this user. The other users request must be placed in queue and should be processed only on completion of the first request. In popular database terminology this translates to table and row level locking in case more than one user tries to access the same physical transaction record. There are many types of locks namely exclusive, shared, table level locks and row level locks etc.,

The fourth property is called as Durability of transactions. In case the transactions are completed complete disk write should ensure that all updates are completed and nothing remains in the buffer and in the process no data update should be lost. A database should enable this property of transactions so that in the event a power failure occurs,even if a transaction has completed, but is in queue to be written to the disk, the database should perform the disk write operation after the power is restored.