16 November 2006 22:20 tonyrogerson

Timebomb - Consistency problem with READ COMMITTED; why you still multi-count or no count rows

I’ve shown you the NOLOCK (READ UNCOMMITTED) timebomb, now let’s look at the READ COMMITTED timebomb and how we get back inconsistent results and why; this entry came as a result of some confusion feedback from my original NOLOCK article but also from some good conversations I had with Gianluca Hotz and Kresimir Radosevic on the ATE stand at IT Forum 2006 in Barcelona.

 

To recap, the READ UNCOMMITTED isolation level allows you to read uncommitted data without you blocking, when a row moves because of a page split, column update etc… then you may well end up counting the same row multiple times or not at all.

 

READ COMMITTED gives you a consistent view of the data, specifically the committed (or last committed in case of READ_COMMITTED_SNAPSHOT) but it isn’t a point in time view – if you use READ COMMITTED and a page you are trying to read is locked then you’ll block, if you use READ_COMMITTED_SNAPSHOT then you won’t (you get the last committed value).

 

Here’s the crunch and here’s where I didn’t make myself clear in the last post because of the perspective I was talking at, even with READ COMMITTED there is a possibility of getting missing or additional counts (or aggregations) purely because of the nature of the isolation level.

 

Let’s create our test stuff…

 

create table bigtable (

    id  int not null,

    somedata char(2048) not null

)

go

set nocount on

declare @i int

set @i = 1

 

while @i <= 100000

begin

    insert bigtable( id, somedata ) values( @i, 'tony' )

    set @i = @i + 1

end

 

insert bigtable( id, somedata ) select id + 100000, somedata from bigtable

go

 

alter table bigtable add constraint pk_bigtable primary key clustered( id )

go

 

Let’s go through a number of tests, usual stuff – two connections required…

 

Run this in connection 1, I am using dropcleanbuffers to slow the query down so it gives me time to run the second update query before this one finishes.

 

dbcc dropcleanbuffers

 

select count(*) from bigtable

 

Run this in connection 2 – be quick, it needs to run and complete while the count(*) query is running.

 

update bigtable

    set id = 500000

where id = 1

 

The COUNT(*) gives the result 200,001 even though a) there are only 200,000 rows in the table and b) we are using READ COMMITTED.

 

Let’s do the same but reverse the update.

 

Run this in connection 1, I am using dropcleanbuffers to slow the query down so it gives me time to run the second update query before this one finishes.

 

dbcc dropcleanbuffers

 

select count(*) from bigtable

 

Run this in connection 2 – be quick, it needs to run and complete while the count(*) query is running.

 

update bigtable

    set id = 1

where id = 500000

 

The COUNT(*) gives the result 199,999 even though a) there are 200,000 rows in the table and b) we are using READ COMMITTED.

 

What’s going on and how does this differ from the READ UNCOMMITTED I talked about in my other blog entry?

 

The UPDATE is doing an Index Seek as part of the Clustered Index Update so the number of pages read to satisfy the query is only 6 logical reads in my own test.

 

The COUNT(*) query is doing a Clustered Index Scan and causes 66,846 logical page reads.

 

Imagine the table is on pages 1 through to page 66,846 and here is what happens with READ COMMITTED (and READ_COMMITTED_SNAPSHOT).

 

COUNT(*) query starts reading from page 1, then incrementally page by page through to 66,846; as it traverses through the table it acquires a Shared Intent lock on the table itself but for each page it acquires a shared lock but the locks are acquired and released as we traverse through the table - they are not held for the entire duration of the statement.

 

Say we have read pages 1 to 5000 and you now run the update statement on id=1, that is on page 1 which no longer has a shared lock on it, so, we can and do update that row – again, because the update is using an index seek and only using 6 pages and the COUNT(*) is doing its lock acquire and release in the middle of the table we are allowed and do essentially move the row from page 1 to page 66,846. This is why we count the row again.

 

There are many ways to see the locks being acquired and released, sp_lock or if you are running SQL Server 2005 use the query below:-

 

select *

from sys.dm_tran_locks

where resource_database_id = db_id()

 

Another method is to using SQL Server profiler, you’ll need the @@SPID for the connections you want to track otherwise too much info comes out, you’ll also want to limit the COUNT(*) to say 5000 rows (shown below). The events to capture are Lock:Acquired and Lock:Released; just capture all the columns and don’t forget to limit it for the SPIDS!!!

 

select count(*) from ( select top 5000 * from bigtable ) as c

 

READ_COMMITTED_SNAPSHOT does nothing for you here for this particular problem, READ_COMMITTED_SNAPSHOT prevents you being blocked by writers that have pages locked so the COUNT(*) won’t be effected by the UPDATE if for instance you where actually doing more in the UPDATE transaction instead of milliseconds of work and one row updated that our test does.

 

If you are after an absolute accurate answer we need to consider other isolation modes, READ COMMITTED just gives you a consistent view in terms of committed data.

 

Our problem here is the lock acquire release behaviour as we traverse the table, what we really need is to keep locks acquired so to prevent data we have read from being updated, we can do that using HOLDLOCK or using the REPEATABLE READ isolation level. Again, you can witness this behaviour through SQL Server Profiler as mentioned earlier.

 

The big (big) problem with this approaches is that quickly gives rise to deadlocks and very quickly if your queries are reporting type, basically anything that’s going to cause a lot of page reads because you are keeping the locks as you go. Let’s take our example, let’s see what effect two updates in a transaction has on our process

 

In connection 1

 

begin tran

 

update bigtable

    set id = 500001

where id = 200000

 

In connection 2

 

set transaction isolation level read committed

 

dbcc dropcleanbuffers

 

select count(*) from bigtable with ( HOLDLOCK )

 

We have 266,680 locks acquired on our bigtable table and the connection is blocked, back in connection 1

 

update bigtable

    set id = 500000

where id = 1

 

rollback

 

Connection 2 deadlocked…

 

Msg 1205, Level 13, State 51, Line 3

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

When modifying our test to use the SERIALISATION isolation mode we get a shed load of locks, well, 100,389 in my test.

 

In connection 1

 

begin tran

 

update bigtable

    set id = 500001

where id = 200000

 

At this point there are only a few things locked

 

DB                     S        GRANT

PAG    1:100266        IX       GRANT

TAB                    IX       GRANT

KEY    (21002849ce51)  X        GRANT

KEY    (40004eb85a66)  X        GRANT

 

In connection 2

 

set transaction isolation level serializable

 

dbcc dropcleanbuffers

 

select count(*) from bigtable with ( HOLDLOCK )

 

We now have 100,389 locks being held and connection 2 is blocking, back in connection 1

 

update bigtable

    set id = 500000

where id = 1

 

rollback

 

Connection 2 deadlocked…

 

Msg 1205, Level 13, State 51, Line 3

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

It gets worse, each connection running the query with the SERIALIZABLE or HODLOCK will generate that number of locks – so if you have lots of connections querying using those techniques the locking resource will become noticeable – no lock escalation occurs.

 

I suppose that leads us to the new SNAPSHOT isolation level in SQL Server 2005, we must enable the database to use this mode:-

 

alter database testlocking set allow_snapshot_isolation on

 

Let’s run up our test, first the update in connection one…

 

begin tran

 

update bigtable

    set id = 500001

where id = 200000

 

Again we have the same locks in place because of the update; now, run the query so it uses the snapshot isolation level, but capture the locks using Profiler…

 

set transaction isolation level snapshot

 

dbcc dropcleanbuffers

 

select count(*) from bigtable

 

In profiler we get a single lock for the query – Sch-S which is a system schema lock to give schema stability rather than the normal data lock; the query does not block and we get the correct result of 200,000 rows; even running it so the COUNT(*) kicks off first and the update that moves id from 1 to 500,000 (start of table to end) still gives us the correct row count and does not block.

 

Basically when using snapshot isolation we get the state of the data when we start our transaction, not the transaction in terms of BEGIN TRAN but the transaction in terms of SELECT COUNT(*) statement transaction – remember each statement is done in a transaction. The data is snapshotted (mmm – perhaps that isn’t a word lol) when we execute our query and if we are inside a BEGIN TRAN then from that point forward is the ‘version’ we see.

 

Anyway, going to summarise now; perhaps I’ll post some stuff on the snapshot isolation when I get time; in a nutshell though – SQL Server copies any change into the version store in tempdb so it keeps a consistent point in time (of the transaction) snapshot of your data until you commit or rollback the transaction.

 

To summarise, READ UNCOMMITTED does not give a consistent view of the database – its trash, you can get double counts, missing rows – the works; you do have blocking so you can never read an uncommitted value, READ COMMITTED and READ COMMITTED SNAPSHOT again does not give a point in time view of your data, it uses a sliding window as your data is read so your counts can again be wrong, rows missing, counted more than once, HOLDLOCK and REPEATABLE READ will give you a point in time consistent view however you will be very prone to deadlocks, the only way to get a real point in time consistent view without all the deadlocking is to use the SNAPSHOT isolation.

 

In a real system blocking may be enough to give you correct results – I cannot tell you for sure because each schema and access patterns are different, but to absolutely make sure of a point in time correct result then use SNAPSHOT isolation which only really works in SQL Server 2005 and above.

Filed under:

Comments

# re: Timebomb - Consistency problem with READ COMMITTED; why you still multi-count or no count rows

17 November 2006 08:09 by Colin Leversuch-Roberts

Didn't Jim Gray say that the next real way forward was to add a time dimension ( so to speak ) to the database "model" . That would sort of fix this problem. I think I'm right in saying that the snapshot isolation ( from Oracle ) is not ANSI standard ?

interesting information.

# Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

17 November 2006 10:06 by Tony Rogerson's ramblings on SQL Server

Using NOLOCK or READ UNCOMMITTED isolation (that’s what NOLOCK is) gives us a consistency problem. This...

# T-SQL: READ COMMITTED and multi-counted rows

24 July 2007 21:04 by Little Tidbits of Random Knowledge

# With NOLOCK &laquo; Martinus Foe

22 January 2008 05:13 by With NOLOCK « Martinus Foe

# (SQL) Blocking Giving You the Blues?

06 October 2008 04:05 by Technical Musings

Problem: You have a long-running query in SQL Server that is causing failures all over the place. Activity Monitor shows you the cause is blocking. Causes: Blocking essentially means &quot;you’ve locked a table, and now someone else is stuck waiting.

# Is a select statement a transaction? | keyongtech

Pingback from  Is a select statement a transaction? | keyongtech

# Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

11 April 2009 14:12 by Alexander Kuznetsov

Selects under READ COMMITTED may return incorrect results if the data they select is being modified at

# Transaction Isolation / Dirty Reads / Deadlocks Demo

31 July 2009 00:32 by Merrill Aldrich

I have just given a talk with my group at work on the basics of transaction isolation, dirty reads (nolock)

# Transaction Isolation / Dirty Reads / Deadlocks Demo | ButtonForums

Pingback from  Transaction Isolation / Dirty Reads / Deadlocks Demo | ButtonForums

# Application Performance: Open Session In View Design Pattern | Cirrus ABS

Pingback from  Application Performance: Open Session In View Design Pattern | Cirrus ABS