ACID properties of transaction processing systems
Relational databases are characterized by transactions and are therefore also called transaction processing systems. These transactions were introduced and evolved logically for reasons of database concurrency. The four properties that are required for any transaction are atomicity, consistency, isolation, and durability.
Concurrency means that many users can access the database to view a page, insert new records, or update old records. A typical lot of user usage is a lot of people trying to book a train ticket online. Typical database operations that involve more than one person are bank transactions to transfer funds between two persons.
Let’s take the example of the later, a money transfer between two people has two main operations in the database. After reading the balance from the first person’s bank account, the transfer amount should be deducted from the first person’s bank account, then the second person’s bank account should be updated.
Consider the situation where there is a power cut after the first transaction, namely deducting the amount to be transferred to the bank account of the second party. There will be an error in the pair of transactions because the second part of the transaction, namely the one to increase the balance in the account of the second person, will not be completed, but the first transaction, namely the debit, will be completed. So it is necessary that both the transactions are executed together within the same transaction window and also if the second transaction is not completed then the first one should be rolled back. This gives rise to the Atomicity property of transactions. In popular relational database terminology, this is called committing and rolling back transactions,
The second property is consistency, the database must 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 always be a constant value.
The third property is the “isolation” of transactions. For this, let’s take an online railway reservation system as an example. For example let’s assume there are 2 users 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 seats available is only 3, then if these two transactions are executed at the same time, then due to the lack of any consistency of these two requests, it can be found that the seats allocated to the two users can be 2 respectively and 1 or 1 and 2. or 0 and 3 after one user’s transaction is fully committed would mean that these two transactions should not be running at the same time. They should be executed sequentially one after the other or in other words when 1 user has access to the reservation system, the corresponding seat reservation related record should be locked exclusively for that user. Other user requests should be queued and should be processed only when the first request completes. 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 transaction longevity. In the event that transactions are completed, a full write to disk should ensure that all updates are completed and nothing is left in the buffer, and no data update should be lost in the process. The database must enable this property of transactions so that in the event of a power failure, even if the transaction has completed but is queued to write to disk, the database must perform the write operation to disk after power is restored.
#ACID #properties #transaction #processing #systems