,

Understanding Transaction Isolation Levels – READ COMMITTED

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.

,

Understanding Transaction Isolation Levels – READ UNCOMMITTED

Sometimes referred to as a “dirty read”, the read uncommitted isolation level reads any data on a SQL Server data page regardless of whether or not the data has been committed. This means that you will see all committed and modified uncommitted data within the database.

There are a many pro’s and cons to consider when using the read uncommitted isolation level. One of the most noticeable pros is query speed. The read uncommitted isolation level will speed up queries as it does not have to wait for other blocking transactions to complete before it gets a turn to read from the table being modified by a different user. This pro, however, goes hand in hand with a con. On the flip side, you will see “dirty data” which may have been modified and may still be modified later in the transaction. Between these modifications, you may see data that does not make logically sense at that point in time. This especially holds true to financial systems where a lot of number crunching is being performed. If the system has been developed in such a way that a lot of changes occur before the final data is committed and you read that data, your figures may be skewed.

Real world case study

To demonstrate how this could potentially be a problem, let’s take an ordering system as an example. We have 100 laptops in stock. Sales person A is dealing with a client who wants to buy 20. Sales person B is dealing with a client who wants to buy 100. Sales person A adds 20 laptops to their cart which updates the stock level to 80. This data, however, has been updated but not yet committed. Sales person B checks the stock level using a query which reads uncommitted data and sees that only 80 laptops are in stock. The stock level of 80 is dirty data because sales person A has not yet confirmed and finalized the sale whereby committing the new stock level data to the database. In reality, the stock level is still 100 instead of the dirty read value which is 80. Sales person B now tells their client that they only have 80 laptops in stock and they lose the sale because the client is insisting on 100 laptops in one sale. Next, Sales person A’s client backs out of the sale because the price is too high. Sales person A cancels the order and the 80 stock level is never committed to the database. The stock level thus remains on 100 which could have filled the need of salesperson B’s client. Because sales person B was doing an uncommitted read on the same data sales person A was working with, they had the wrong impression of the stock level and lost the sale of 100 laptops.

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

You can set the read uncommitted transaction isolation level in any transaction or script by adding the following at the beginning of any transaction:

SET TRANSACTION ISOLATION READ UNCOMMITTED

The isolation level will be applied to the entire transaction in which it is set. Setting the transaction isolation level to read uncommitted is similar to specifying NOLOCK on every table which data is read from within your transaction.

Demonstration

To demonstrate the read uncommitted transaction isolation level, open SQL Server Management Studio and run the following create script on a test database:

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

The result set should show after the script has completed and the table has been created.

Open a new tab. This tab will represent the first session, or user, who will be updating the data. Run the following code in the first tab:

BEGIN TRANSACTION

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

	WAITFOR DELAY '00:00:15'

ROLLBACK

Open one more tab which will represent the second session, or user, who will be reading the uncommitted (dirty) data. Run the following code in the second tab:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

If you run the session 2 script while the session 1 script is running, you will see the following result set:

jj prinsloo sql expert read uncommitted result2

As you can see, the payment method is EFT, the same payment method as it was updated to in the session 1 script.

When you run the session 2 script again, after the session 1 script has completed, you will see the following result set:

jj prinsloo sql expert read uncommitted result3

Wait a minute!? The Payment method is Credit Card now? We updated the value in the session 1 script to EFT? And confirmed the update with a query? WHAT’S GOING ON? Steady now, if you check the session 1 script, you will notice that the transaction is never committed so the updated data is never committed.

Conclusion

As you can see, reading data using an uncommitted transaction isolation level can give you unexpected results. In most cases, this should not be an issue as the default setting of SQL Server nowadays is to commit data to the database automatically whereas with earlier versions you had to manually commit your changes to the database. That had to be a pain! The default of SQL Server is also to use that read committed transaction isolation level so you have to deliberately specify the usage of the read uncommitted transaction isolation level. In the end. When you use the read uncommitted isolation level and you see funny or unexpected results, keep all of the characteristics of this isolation level in mind. You may just end up saving yourself a lot of frustration.