Pages

Thursday, June 3, 2010

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:

image

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:

image

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.

2 comments:

  1. We have similar execution plans taking many concurrent updates and all use non-clustered index seeks at the beginning. So, why do we still see deadlocks?

    ReplyDelete
  2. Dadlocks can occur because of a diversity of causes and on many ressources. I didn'd say that NCI will prevent any deadlock. It's just one approach that might be appropriate.

    ReplyDelete

Followers