Contention On The Heap

I was reading a very interesting blog post from Paul Randal, called Lock Logging and Fast Recovery. The theme was log internals, but it set my train of though to how much concurrency you can achieve on a heap, with no non-clustered indexes, and the results may (or may not surprise you).

I created a table called ConcurrencyTest, and populated it, with the following script...

USE Adventureworks2008
GO

CREATE TABLE ConcurrencyTest
 (
 PKCol int NOT NULL IDENTITY (1, 1),
 TextCol varchar(50) NOT NULL
 )  ON [PRIMARY]

GO
ALTER TABLE ConcurrencyTest ADD  CONSTRAINT DF_ConcurrencyTest_TextCol  DEFAULT ('Test Data') FOR [TextCol]
GO

CREATE UNIQUE CLUSTERED INDEX Clust ON ConcurrencyTest(PKCOL)
GO

INSERT INTO ConcurrencyTest DEFAULT VALUES;
GO 10000

...I then picked 2 rows at random, and used %%physloc%% to see what pages the rows were stored on. (See my post What Files and Pages Is My Table Stored On? for more info).

The results below, show that the 2 rows are on separate pages...

...I then opened 2 new query windows, and SQL assigned them the session IDs 53 and 57 respectively.

Next, in each of the query windows, I began an explicit transaction (in order to make SQL hold the locks) and ran an update statement against the 2 rows I choose a moment ago...

(Session 53)
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 1

(Session 57)
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 5000

...I set both queries running, and a quick glance at sys.dm_exec_requests...

SELECT session_id, blocking_session_id, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0

...showed that there were no blocks (as you would expect). The same would hold true if I dropped the Clustered index and created a non-clustered index on the TextCol column (Although I will let you prove that for yourself)

Looking at sys.dm_tran_locks, shows that exclusive locks have been taken on the keys, and Intent exclusive locks have been taken on the pages and also the table...

SELECT * FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
GO

...Again, this is exactly as you would expect. The interesting behavior occurs when all indexes are removed from the table. I dropped the clustered index, rolled back the 2 transactions, and then ran queries again. This time, when I looked at sys.dm_exec_requests, I saw the following results...

...As you can see, this time there is a resource wait on the RID of the first row, even though the where clauses would prevent the same row being updated by both queries.

A look at sys.dm_tran_locks, shows slightly different locks and holds the reason for this contention...

...Instead of locking an index key, SQL has had to take out locks on the RIDs, which are an internal unique identifier found on heaps.

Basically, if there is no clustered index on a table, and the column being updated is not included in the key of a non-clustered index, then SQL has no key to lock and instead locks the RIDs. However, if there is no index, then the data can be stored in any order, which means that a row can move between slots on a page, or if the page is full, could even move to a different page. Therefore, SQL can not allow concurrency across the 2 updates, because although unlikely, there is no guarantee that the 2 updates will not conflict with each other, and SQL Server does not support lost updates.

The moral of the story? Always use indexes....!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom