This is not my own writing. Instead, I am assembling useful resources into a blog for my own understanding. Reference of resources will be present at the end of the blog.

A transaction is a collection of read/write operations succeeding only if all contained operations succeed. In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties.

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability

Atomicity: 

This property states that a transaction must be treated as an atomic unit. By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves following two operations.

—Abort: If a transaction aborts, changes made to database are not visible.

—Commit: If a transaction commits, changes made are visible.

Consistency:

This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.

Isolation:

In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

Durability:

The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Different Isolation levels:

In a relational database system, atomicity and durability are strict properties, while consistency and isolation are more or less configurable. We cannot even separate consistency from isolation as these two properties are always related.

Isolation level is about how much a transaction may be impacted by the activities of other concurrent transactions. It involves locking rows and/or tables in a database. Let’s consider some scenarios.

Dirty reads: Let’s say T1 transaction reads data from table A1 that was written by another concurrent transaction T2. If on the way T2 rollbacks, the data obtained by T1 is invalid one. 

Non-repeatable reads: Non Repeatable read occurs when a transaction reads a same row twice, and get a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.

Phantom reads: Phantom Read occurs when two same queries are executed, but the rows retrieved by the two are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.

You can obtain complete isolation level by implementing locking.That is preventing concurrent reads and writes to the same data from occurring. But it affects performance .Level of isolation depends upon application to application how much isolation is required.

Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.

Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.

Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

References:

  1. https://www.geeksforgeeks.org/acid-properties-in-dbms/
  2. https://vladmihalcea.com/a-beginners-guide-to-acid-and-database-transactions/
  3. https://www.tutorialspoint.com/dbms/dbms_transaction
  4. https://stackoverflow.com/questions/8490852/spring-transactional-isolation-propagation
  5. https://www.mssqltips.com/sqlservertip/4438/compare-repeatable-read-and-serializable-sql-server-transaction-isolation-levels/
Previous Article

Leave a Reply

Your email address will not be published. Required fields are marked *