ACID property and types of database transaction
ACID property is a feature of a database transaction. The transaction is simply a state change and for the context of the database, it is a change of state in the database table through SQL queries. Database state change can be a change in a variable, row, or column or execution of a function or completion of a process. The ACID property ensures state changes are consistent, manageable, and tractable. There are four properties in the ACID framework.
Atomicity is the concept that the full transaction either occurs all at once or not at all. There is no middle ground, hence partial transactions do not take place. Each transaction is treated as a single entity and is either carried out entirely or not at all. The following two operations are involved.
— Abort: If a transaction fails, any database modifications are invisible.
— Commit: A transaction becomes visible after it has committed any modifications.
The “All or nothing rule” is another name for atomicity.
In order for the database to be consistent both before and after the transaction, integrity requirements must be upheld. It refers to a database’s integrity.
Multiple transactions can run simultaneously with no risk of the database’s state becoming inconsistent owing to isolation. Transactions take place without interruption and independently. Changes made in one transaction are not visible to changes made in any other transaction until the change in question has been committed or written to memory, whichever comes first. This property guarantees that the state created by concurrently running transactions will be the same as the state created by serially running them in some order.
Durability guarantees that after the transaction has finished running, the database updates and modifications are saved in and written to disk and persist even if a system failure takes place. These modifications are now kept in non-volatile memory and are permanent. Thus, the transaction’s effects are never lost.
The entirety of the ACID properties offers a complementary mechanism to guarantee the accuracy and consistency of a database in a way that each transaction is a collection of operations that function as a single unit, yields consistent results, operates independently of other operations, and updates that it makes are durably stored. This holistic complementary ACID ontology is achieved through save points or checkpoints. A save point, which generates a save point number for later use, can be marked during the execution of a transaction. The identity of the lock that was most recently acquired and the current state of the database context used by the transaction are maintained in special entries at each save point. When a transaction fails, it can resume from the saved state, restoring the context and releasing any locks that were acquired after the saved state. In the event of errors, rollback enables the database to be restored to a previous state.
There are several types of transactions that require ACID property. Single Level Transaction: A transaction is a transformation from one state to another. ACID properly and VCRP property. The transaction processing (TP) system is responsible for ensuring the ACID properties. A TP system generally consists of:
• a TP Monitor, which is an application to manage transactions and control access to a Database Management System.
• one or more Database Management Systems (DBMS). However, for flat transac- tions only one DBMS can be part of the TP system.
• a set of application programs containing transactions.
Type of Transaction:
Single transaction (only one action)
Hierarchical sequential dependent transaction (many dependent actions)
Distributed concurrent transactions ( many independent asynchronous actions)
Distributed Transaction: In the X/Open DTP model, the transaction manager is a functional component managing global transactions and coordinating the decision to start, commit or roll back, and ensures atomicity at a global level. Each participating resource manager is responsible for the ACID properties of its own resources. The distributed transaction uses a bottom-up approach where sub-transactions make up a single holistic whole transaction.
Nested Transaction: It uses a top-down approach to decompose a complex transaction into sub-transactions or child transactions according to their functionalities.
Single vs Multiple Level Transaction: A child transaction can only start after its parent starts and a parent can only commit after all its children have been completed. The commitment of a child transaction is conditional on the commitment of its parent. Each child is atomic, thus it can abort independently regardless of its parent and siblings. When it aborts, the parent will take an action, for example triggering another sub-transaction as an alternative. The aborted sub-transaction results as if it had not been executed. However, the aborted sub-transaction may have changed the state of the database so it can make the database inconsistent while the whole nested transaction still meets the consistency requirement.
(ACID Properties in SQL Server, 2017; Kaur, 2019; Wang et al., 2008)
ACID Properties in SQL Server. (2017, December 20). Tutorial Gateway. https://www.tutorialgateway.org/acid-properties-in-sql-server/
Kaur, A. (2019, January 31). ACID Properties in DBMS — GeeksforGeeks. GeeksforGeeks. https://www.geeksforgeeks.org/acid-properties-in-dbms/
Wang, T., Vonk, J., Kratz, B., & Grefen, P. (2008). A survey on the history of transaction management: from flat to grid transactions. Distributed and Parallel Databases, 23(3), 235–270. https://doi.org/10.1007/s10619-008-7028-1