Non Clustered Indexes may prevent dead locks

If a table is very tiny, so that all table’s data fit into one single data page e.g., will any index be helpful when accessing/modifying the table’s data? You may think no, since for reading the data, the one and only data page has to be touched anyway, so a table (or clustered index) scan will get by with a single page read, and that’s the most efficient method for reading the data. So, there’s no need for an index, right?

Well, as the guru always says: It depends.

Have a look at the following table:

create table t1  (    c1 int not null primary key clustered   ,c2 int not null  )

Now, lets say we modify some data simultaneously from two different connections. Try the following scripts from two separate connections:

-- Connection 1 begin tran   insert t1(c1, c2) values (1, 1111)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 1111 commit

 

-- Connection 2 begin tran   insert t1(c1, c2) values (2, 2222)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 2222 commit

After a few seconds you will see an abortion one of the two transactions with a deadlock error like this:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, let’s create a nonclustered index on column c2.

create nonclustered index ix1 on t1(c2)

You may think, this index isn’t any useful, since our table only contains two rows and the optimizer will never decide for an index seek therefore. But if you repeat the above experiment, no deadlock shows up this time. So the index is good, helpful and may be even necessary.

Ok, let’s see what happened here. We start with the first case, without the nonclustered index. Our focus should be on the DELETE statement, since this is the cause for the problem. The execution plan (without the nonclustered index) looks like this:

In order to find the row that has to be deleted, a clustered index scan must be performed.

A call of sp_lock during the execution of the two statements reveals a situation like this:

spid	IndId	Type	Resource	Mode	Status
52	1	KEY	(8194443284a0)	U	WAIT
52	1	PAG	1:115	IX	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	0	TAB		IX	GRANT
54	1	PAG	1:115	IX	GRANT
54	1	KEY	(61a06abd401c)	U	WAIT

We see two connections (SPIDs) holding or waiting for locks. I’ve highlighted the rows of interest, where I used a green background for granted locks and a red background for waits. The resource column of the highlighted rows is related to a table (clustered index) row. You may investigate this on your own by using the undocumented %%lockres%% column. (See this blog post by James Rowland-Jones)  It is very obvious that the two connections are waiting on each other to release a lock on a row which is locked by the other connection, while – at the same time - holding a lock on the row the other connection is waiting for. Classical deadlock situation.

Now, let’s see how this changes with the nonclustered index in place. Here’s the execution plan for the delete:

Ok, the index is used for looking up the row that has to be deleted. Very good. The optimizer is aware of the fact that an index seek will be more appropriate for determining the row, since this will lead to fewer resource locks.

Here’s what sp_lock reveals if the two scripts are executed with an existing nonclustered index:

spid	IndId	Type	Resource	Mode	Status
52	3	PAG	1:126	IX	GRANT
52	1	PAG	1:120	IX	GRANT
52	3	KEY	(0fcd4e06ed44)	X	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	1	PAG	1:120	IX	GRANT
54	3	PAG	1:126	IX	GRANT
54	0	TAB		IX	GRANT
54	3	KEY	(917735e52a83)	X	GRANT

Very good! Only granted locks this time, no waits.