An important property of SQL transaction, isolation refers to separating transactions from each other to maintain data integrity in databases. The question that merits answering, however, is how isolation is implemented.
Why is an isolation level required?
Large enterprises / public networking applications have a huge number of users accessing the same database(s) and table(s) at the same time while they are being developed. This often leads to data concurrency situations. Let’s discuss this situation under the four headers of:
1. Loss of Data
2. Dirty Read
3. Phantom Read
4. Inconsistency Analysis
Loss of Data
Let’s take an example. Suppose there are two users accessing the same table, at the same moment, to update the same row. Both users are unaware of each other’s transactions. User A updates the row and then User B updates the same row. The result is the last transaction made by User B overwriting User A’s updated record, leading to the latter losing his / her data in the table.
Dirty Read
This is otherwise known as Uncommitted Dependency. Let’s take another example. Suppose User A and User B are both accessing a particular table row at the same time. User A wants to read while User B wants to update the row. Both transactions are executed nearly simultaneously. The result is that User A reads the row before User B has managed to update it (during the update process). Consequently, User A gets the old record which may not be correct for his / her operation. This situation is known as Dirty Read.
Phantom Read
This is also known as a Phantom Problem. Let’s explain this with yet another example. Suppose User A inserts a row with User B simultaneously inserting the same row. Now, when User A tries to insert, he / she can’t do so. User A’s default reaction will be – “Hey you committed that this is available for me to insert, but you cheated on me and granted someone else the access to do so!” This is a classic example of a problem users may face while reserving online tickets (train, movie, and such).
Inconsistency Analysis
This is also known as a Non-Repeatable Problem. Let’s take the same example as before – that of User A and User B. Suppose User A executes a transaction having three queries – a stored procedure or transaction or individual query with a batch. The first query is to read a table row, the second is to update it, and the third is to read it again. By doing this, User A wants to generate a report. User B, meanwhile, accesses the table row in between the two ‘Read’ queries of User A and uses the ‘Delete’ function. The result is that User A, who has already modified the data, gets an inconsistency in data when he wants to read it again.
The aforementioned points illustrate why User A needs to be helped by a Database Administrator or Developer. The ‘Isolation Level’ is the tool that a developer needs to help User A maintain the integrity of his / her data.
Let’s understand the concept in greater detail.
Isolation Levels
SQL Server provides five isolation levels to implement with SQL transaction to maintain data concurrency in databases.
An isolation level is essentially locking a particular row while performing a task, so that other transactions on the same row cannot access or have to wait for the current transaction to finish its job.
Let’s write a transaction without an isolation level.
1. BEGIN TRANSACTION MyTransaction
2. BEGIN TRY
3. UPDATE Account SET Debit=100 WHERE Name=’John Cena’
4. UPDATE ContactInformation SET Mobile=’1234567890′ WHERE Name=’The Rock’
5. COMMIT TRANSACTION MyTransaction
6. PRINT ‘TRANSACTION SUCCESS’
7. END TRY
8. BEGIN CATCH
9. ROLLBACK TRANSACTION MyTransaction
10. PRINT ‘TRANSACTION FAILED’
11. END CATCH
In the code snippet above, we have not implemented an isolation level. Consequently, there are chances of data concurrency. Let us now go through all the five isolation levels and find the solution for our situation.
1. Read Uncommitted
When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transactions but is not yet committed. Suppose User A is trying to read a row which is being updated by User B, we allow User A to read the un-updated / uncommitted data, i.e. the old data.
Example:
<SET TRANSACTION ISOLATION LEVEL>
<READ UNCOMMITTED>
<BEGIN TRANSACTION MyTransaction>
<BEGIN TRY>
<UPDATE Account SET Debit=100 WHERE Name=’John Cena’>
<UPDATE ContactInformation SET Mobile=’1234567890′ WHERE Name=’The Rock’>
<COMMIT TRANSACTION MyTransaction>
<PRINT ‘TRANSACTION SUCCESS’>
<END TRY>
<BEGIN CATCH>
<ROLLBACK TRANSACTION MyTransaction>
<PRINT ‘TRANSACTION FAILED’>
<END CATCH>
<PRINT ‘TRANSACTION SUCCESS’>
<PRINT ‘TRANSACTION FAILED’>
2. Read Committed
This level prevents Dirty Read from occurring. When this level is set, the transaction cannot read the data that is being modified by a current transaction. This forces a user to wait for the current transaction to finish its job. Suppose User A is trying to read a row which is being updated by User B, we ask User A to wait for User B to finish his / her update task and then give the updated / correct data to User A. But the problem with this level is that it cannot resolve Phantom Read or Inconsistency Analysis, i.e. it asks User A to wait for ‘Read’ but not for ‘Update’ or ‘Insert.’
Example:
<SET TRANSACTION ISOLATION LEVEL>
<READ COMMITTED>
<BEGIN TRANSACTION MyTransaction>
<BEGIN TRY>
<UPDATE Account SET Debit=100 WHERE Name=’John Cena’>
<UPDATE ContactInformation SET Mobile=’1234567890′ WHERE Name=’The Rock’>
<COMMIT TRANSACTION MyTransaction>
<PRINT ‘TRANSACTION SUCCESS’>
<END TRY>
<BEGIN CATCH>
<ROLLBACK TRANSACTION MyTransaction>
<PRINT ‘TRANSACTION FAILED’>
<END CATCH>
<PRINT ‘TRANSACTION SUCCESS’>
<PRINT ‘TRANSACTION FAILED’>
3. Repeatable Read
This level does all the work that the Read Committed level does. However, it has an additional benefit. For instance, User A will wait for the transaction being executed by User B to execute the Update query as well – just like Read query. But Insert query doesn’t wait, and this creates the Phantom Read problem.
Example:
<SET TRANSACTION ISOLATION LEVEL>
<REPEATABLE READ>
4. Snapshot
This level takes a snapshot of current data. Every transaction works on its own copy of data. When User A tries to update or insert or read anything, he / she is asked to re-verify the table row once again from the start of its execution. This helps him / her work on fresh data with high-level changes in data integrity.
Example:
<SET TRANSACTION ISOLATION LEVEL>
<SNAPSHOT>
5. Serializable
This is the maximum level of isolation provided by a SQL Server transaction. The Phantom Read problem can be prevented from occurring by implementing this level of isolation by asking User A to wait for the current transaction for any kind of operation he / she wants to perform.
Example:
<SET TRANSACTION ISOLATION LEVEL>
<SERIALIZABLE>
Isolation level also has a problem called ‘Dead Lock’ in which two transactions lock an object and wait for each other to finish the job. A Dead Lock situation can be very dangerous because it decreases the concurrency and availability of a database and a database object. More about Dead Lock in the second part of this blog.