,

Understanding Transaction Isolation Levels – READ UNCOMMITTED

jj prinsloo sql expert transaction isolation level read uncommitted header

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.

1 reply

Trackbacks & Pingbacks

  1. […] 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. […]

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 *