Why Isolation Level Required?

Isolation is one of the ACID principles. Our objective here is not to make this article too lengthy, so we are proceeding with some important pointers. Please have a glance at them for quick revision:

  1. During the execution of concurrent transactions on the database system, isolation means each transaction completes its task independently.
  2. There are a few challenges that we face when the same set if records are being used by multiple transactions. Those are dirty reads, phantom reads, loss of data, and inconsistency.
  3. Loss of data: Suppose two transactions are accessing the same row from the table and one of them updates data before the second transaction update. the second transaction does not know about the first transaction. The second transaction lost the data update done by the first.
  4. Dirty read: Dirty read is also known as uncommitted dependency. It is a case when the first transaction reads the record while at the same time seconds, the transaction updates the records. So the first transaction read the record before a commit of the second transaction and the first transaction fetched old records.
  5. Phantom Read: It occurs when during the first transaction, the second transaction adds or deletes records.
  6. Inconsistency Analysis: Also known as a non-repeatable read problem. It is a problem where the transaction reads the data twice and gets different results each time. For instance, the first transaction performs a few tasks such as reading a set, updating a few records and reading again. In between the second transaction updates or deletes a record that changes the first transaction result.
 

These are some big issues with data concurrency in the database management system. To solve these problems we have five levels of isolation, please read the following to gain insight into them:

  1. Read Uncommitted: In this isolation level, a dirty read problem occurs, to know what a dirty read problem is refer above pointers.
  2. Read committed: This solves the dirty read problem but cannot solve the phantom read problem. At this level, transactions have to wait till one transaction completes its job. It does not allow transaction wait in the case of update and insert.
  3. Repeatable Read: This level is the extension to read committed as it additionally allows for updates and transactions can wait for read and update but not in the case of insert. So it again doesn’t solves a phantom read problem.
  4. Snapshot: This solves all the above problems but creates a synchronisation problem. Snapshot creates a copy for each transaction but it does not ensure if the transaction will run on fresh data.
  5. Serializable: It is the maximum level of isolation provided by SQL Server. It serially executes each transaction instead of concurrently. It also gives the benefits of retrying failed transactions due to serialization failures.