ISOLATION LEVEL IN SQL Server

LOST UPDATE

A lost Update happens when one transaction updates a record and the same record is accidentally updated by another transaction.

A lost Update Occurs when two transactions read a record, one transaction writes information to the record, and the second record also writes updated information to the same record.

Suppose we have an EMPLOYEES table and we are given a task to increase the salary of employee X from $5000 to $7000, now you start a transaction and write a command to update the salary now you can see that the salary of an employee is set to $7000, While your transaction is running another transaction wants to update the address of the same employee, and they are using the same edit screen that you are and they update the entire record but they still see the salary as $5000 so now if they update the address the salary is also set back to $5000, that’s a lost update.

DIRTY READ

A dirty Read happens when a Session tries to read the data which is not yet committed, or when a session tries to read Uncommitted data.

Suppose USER 1 tries to read the data from a transaction and exactly at that point USER 2 tries to update the same row which is part of another transaction that is not yet complete. if the transaction of USER 2 Completes successfully then it's okay, but what if USER 2 doesn’t COMMIT or ROLLBACK his data?

USER 1 might get rows that were never committed to the database, so here we have a DIRTY READ.

We will go through a simple example of a DIRTY READ, we have created an EMPLOYEES table.

IF OBJECT_ID(N'EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE EMPLOYEES;
CREATE TABLE EMPLOYEES
(ID INT,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT);
INSERT INTO EMPLOYEES VALUES
(1,'SHANE','RAY',5000),(2,'JACK','STRONG',7000),(3,'OLGER','PHILLIPS',9000);
select * from EMPLOYEES;

We will run the below command to set the transaction isolation level to read uncommitted.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Suppose we are given a task to get the names of all the employees and their respective salaries, so we go ahead and start a TRANSACTIONS and will run a simple select command to get all the employee details and we get all the records we need.

while our transaction is still running another user starts a new transaction to update the salary of the employee with employee_id =1 by setting the salary to (one hundred million), but the user realizes that he has made a huge mistake by setting the salary to (one hundred million).

At this point, if we run our select query we will see the updated salary is (one hundred million), so we are actually seeing the data which is not yet committed and that’s a DIRTY READ.

At this point, the second user acknowledges his mistake and rolls back his update.

Now when you run your select query again the row with a salary of (one hundred million) is gone and there is no trace of that record.

NON-REPEATABLE READS

Non-repeatable reads happen when a session tries to read the data, get some rows, and run the query again and get a different result This can happen if, on the second run, the data was updated by someone else.

Below is an example of a Non-Repeatable Read, First we Set the transaction isolation level to READ UNCOMMITTED and begin a transaction.

While we are running our transaction another transaction sets the salary of one of the employees to (one hundred million)

Now if we run our select statement then we will see that update row with a salary of (one hundred million).

the send user recognizes his mistake and updates the record with the salary of (one hundred million) to its correct value of 6000.

if we run our select query again we will also see the correct value with the salary of 6000, That’s a NON-REPEATABLE read, we saw two different values for a particular row in 2 different runs.

PHANTOM READ

Phantom Reads Happen when one session is trying to read data and another session is trying to insert rows, it can happen that the first session might read only some of the rows inserted by the second session

We will look into the example of the PHANTOM READ Script.

Suppose we are trying to read data from the employee's table and another Transaction tries to update the data in the employee's table, the second transaction inserts a new row in the table.