,

Understanding Transaction Isolation Levels – READ COMMITTED

jj prinsloo sql expert transaction isolation level read committed header

The READ COMMITTED transaction isolation level is the default setup of Microsoft SQL server. With the committed read transaction isolation level, data cannot be read from a data page until all UPDATE, DELETE, or INSERT operations performed by other users or connections on the table which you are trying to read from has been completed and committed to the database.

When running a select query on any database without specifying the isolation level, the query is executed under the read committed transaction isolation level. Hence it is the default.

Real world case study

To explain exactly what this means, I’ll take a financial system as an example. Senior management wants to check the revenue of the month by running a report. One of the managers is a bit ahead of the curve and tries to run the revenue report while the company HR is running the monthly payroll processes. The managers’ report just keeps running without returning any data. Eventually when the payroll process finishes, the managers’ report returns the revenue data. The manager, who was a bit hasty, had to wait for the payroll to finish before he could see the data and with good reason. If he was able to see the data before the payroll was completed, he would have seen false revenue figures as the salaries of all the employees have not yet been deducted which affects the revenue.

How do I specify the read committed transaction isolation level in my script?

With any default installation of SQL server, it is not necessary to specify the read committed transaction isolation level as this is the server default and any query that is run on the server without specifying an isolation level will run under the read committed transaction isolation level. If you would like to specify the read committed transaction isolation level to make sure the query runs under said transaction isolation level regardless of what the SQL Server default is, you can set it by adding the following statement to the beginning of your script or transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

The transaction isolation level will be applied to the entire transaction in which it is set.

Demonstration

To demonstrate the read committed transaction isolation level, open SQL Server Management Studio and run the following create script:

IF OBJECT_ID('Sales_E636F1418128466888ED3A3CECC07611') is not null
begin 
    DROP TABLE Sales_E636F1418128466888ED3A3CECC07611
end

Create table Sales_E636F1418128466888ED3A3CECC07611
(
    SalesID int identity(1,1),
    SalesDate datetime,
    CustomerFirstName varchar(50),
    CustomerLastName varchar(50),
    PaymentMethod varchar(50),
    SalesAmount decimal(16,2)
)

insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20160905','Alan','Wilkinson','Credit Card',59.50)

insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20161011','Joey','Hollins','Credit Card',712.63)

insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170406','Evander','Emmet','Cash',265.42)

insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170512','Martin','Curtis','Cash',34.78)

insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170518','Alton','Batts','Credit Card',275.65)

select * from Sales_E636F1418128466888ED3A3CECC07611

Open a new query tab and run the following UPDATE statement. This query tab will represent the first user or session:

BEGIN TRANSACTION

    UPDATE Sales_E636F1418128466888ED3A3CECC07611 SET PaymentMethod = 'EFT'
    where SalesID = 2

    WAITFOR DELAY '00:02:15'

COMMIT TRANSACTION

Open a new query tab and run the following SELECT statement. This query tab will represent the second user or session which is trying to read the same data that the first user or session is updating:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
    SalesDate,
    CustomerFirstName + ' ' + CustomerLastName as Customer,
    PaymentMethod,
    SalesAmount
FROM
    Sales_E636F1418128466888ED3A3CECC07611
WHERE
    SalesID = 2

You will notice that the second query keeps running and does not return any data until the first query which is updating the data finishes its execution. This is the read committed transaction isolation level in action. The data that the second query is requesting will only be returned once the first user or session has committed its changes to the database.

As an added measure, open a new query tab and run the following statement:

sp_who2 ‘active’

In the result, look for the spid of the second user or query (session 2 script). You can see the spid by looking at the tab. In my example the spid I am looking for is 55.

jj prinsloo sql expert spwho2

In the results of the sp_who2 script, find the spid. You will notice that your session 2 spid is being blocked by the spid of your first user or session query (Session 1 script). In my case the spid of the first session is 54.

jj prinsloo sql expert spwho2 result

This is because session 2 will first have to wait for session 1 to complete the update of the data and commit it to the database before it can read the data.

Conclusion

As with everything there will be pros and cons in using the read committed transaction isolation level in different situations. In the end, there is a reason why it is the SQL server default. You will always see the correct data.

One thing to keep in mind is that just as you cannot read data under the read committed transaction isolation level that other users are updating, other users cannot update data which you are reading using the read committed transaction isolation level. Their spid will be blocked until you have finished reading the data before their update can start. This prevents any data from being modified while you are doing a select and will prevent dirty data which is a clear difference from the read uncommitted transaction isolation level.

Pro tip: When transactions are taking long to complete like updating data, running reports etc. Always check sp_who2 ‘active’. You might find a lot of blocking transactions which could be explained or solved by using the correct transaction isolation levels.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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