Relational databases are characterized by transactions and are therefore also called transaction processing systems. These transactions have been launched and have evolved logically due to reasons of concurrent use of databases. The four properties that are required for any transaction are Atomicity, Consistency, Isolation, and Durability.
Concurrent use means that many users access the database to view a page, insert new records, or update old records. The typical use of multiple users is many people trying to book a train ticket online. Typical database operations involving more than one person are a bank transfer of funds transaction between two people.
Let’s take the example of the last one, a transfer of funds between two people has two main database operations. After reading the balance of the first person’s bank account, the amount to be transferred must be deducted from the first person’s bank account, then the second person’s bank account must be updated.
Consider the situation that there is a power failure after the first transaction, that is, deduct the amount to be transferred to the second person’s bank account. There will be an error in the transaction pair as the second part of the transaction, i.e. the increase in the balance in the second person’s account, will not complete, but the first transaction, i.e. the debit, will complete. Therefore, both transactions need to be executed together within the same transaction window and also if the second transaction does not complete, the first one needs to be rolled back. This gives rise to the atomicity property of transactions. In popular relational database terminology, this is called transaction commit and rollback,
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 must always be a constant value.
The third property is the “isolation” of transactions. To do this, let’s take the example of an online train reservation system. For example, suppose 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 seats available is only 3, then if these two transactions are executed simultaneously, due to the absence of a sequence of these two requests, it is possible that the seats assigned to the two users are 2 and 1 or 1 and 2 respectively. o 0 and 3 after completing a user transaction would mean that these two transactions should not be executed at the same time. They must be executed in series one after another, that is, when 1 user accesses the reservation system, the corresponding record related to the exclusive seat reservation for this user must be blocked. Request from other users should be queued and should be processed only upon completion of the first request. In popular database terminology, this translates to locking at the table and row level in the event that 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 Durability of transactions. In the event that transactions complete, the full disk write should ensure that all updates complete and that nothing is left in the buffer and that no data updates are lost in the process. A database should enable this transaction property so that in the event of a power outage, even if a transaction has completed but is queued to be written to disk, the database should perform the operation disk write after power is restored.