The DBMS manages the databases over multiple environments where numerous users are working. There may be chances of data loss over multiple environments and users. Therefore, to overcome such problems, the DBMS provides a mechanism to maintain data integrity within the DBMS. Transactions are used to ensure data integrity when multiple users access and modify data in a DBMS. A database transaction includes the interaction between the databases and users. In general, DBMS provides the feature of Atomicity, Consistency, Isolation, and Durability for each transaction in a database. These properties are collectively called the ACID properties in DBMS. SQL is a computer language that is used for storing, retrieving, and manipulating the data kept in a relational database.
The ACID properties are maintained by the transaction manager of DBMS to retain the integrity of the data over the database. Let’s take a look at ACID properties for transaction management.
1. Atomicity
The guarantee of either all or none of the tasks of a transaction to be performed is defined as atomicity. These properties provides an ability to save (commit) or cancel (rollback) the transaction as at any point, and controls all the statements of a transaction.
2. Consistency
The consistency property guarantees that the data remains in a legal state when the transaction begins and ends, implying that if the data used in the transaction is consistent before starting the transaction, it remains consistent even after the end of the transaction. If the data satisfies the integrity constraints of that type, it is known as consistency at or data in legal state.
For example, if an integrity constraint specifies that the age should not be a character and should be a positive value, a transaction is aborted during its execution if this rule is violated.
3. Isolation
The solution is the ability of the transaction to isolate or hide the data used by it from other transactions until the transaction ends. The solution is done by preparing locks on the data. The following set of problems may occur when the user perform concurrent operations on the data:
- Dirty Read:
Specifies that a transaction tries to read data from a row that has been modified but yet to be committed by other transactions. - Non-repeatable Read:
This occurs when the read lock is not acquired while performing the SELECT operation. For example, if you have selected data under the T1 transaction, and meanwhile if the same is being updated by some other transaction, say T2, then the T1 transaction reads two versions of data. This type of data read is considered as non-repeatable read. It can be avoided by preparing a read lock by transaction T1 on the data that is has selected. - Phantom Read:
Specifies the situation when the collection of rows, returned by the execution of two identical queries, are different. This can happen when range locks are not acquired while executing the SELECT query. Consider an example, wherein a transaction T1, you have on executed query Q1 and got some results (say 10 rows). It is possible that during transaction T1, another transaction T2 has made some changes due to which the execution of the query Q1 within T1 now results in a different number of rows (say 11 rows). This problem is referred to as phantom read problem, which happens if some other transaction inserts a new record that is being used by an already running transaction.
4. Durability
The durability property guarantees that the user has been notified of the successful transaction, which can persist all the statements in the transaction or leave the complete transaction unsaved. This property specifies that after the successful execution of the transaction, the system guarantees the updation of data in the database even if the computer crashes after the execution of the transaction.
