Compartilhar via

Deadlock because of Indexed Views

  • Artigo

Indexed views can cause deadlock when two or more of the participating tables (in the indexed view) is updated/inserted/deleted from two or more sessions in parallel such that they block each other causing a deadlock scenario.

The deadlock scenario in this case cannot be completely eliminated, however, the impact/cause can be minimized. I have explained with a complete sample scenario

Create a new database and execute the script from the attached file "deadlockschemafiles.sql". This file create two tables, one indexed view and populates data into the tables.

Step A) Create a new session (Session 1) and execute

                  begin tran

                  update invoice set amount = amount + 1 where invid = 1

Step B) Create another session (Session 2) and execute

begin

tran

update

customer set custname = 'Customer Change1' where custid = 2

This statement in Step B will will be blocked and if you look at the execution plan of Update Customer statement (from the attached document QueryPlan.doc Section Query Plan A ) you will see a clustered index scan on the invoice table.

This may mean that for any customer to be updated, no other session should hold exclusive lock on any of the invoice rows at that point of time since a clustered index scan is performed on invoice table.

Now we have just seen a blocking scenario, so when does it result in a deadlock.

Execute this statement from Session 1 (created with Step A)

update invoice set amount = amount + 1 where invid = 2

This statement will require a shared lock on the customer with CustID = 2 in the customer table. As a result both the sessions block out each other resulting in a deadlock situation with a deadlock error in one of sessions.

So how do we minimize such that the customer update does not have to block when any of the sessions hold an exclusive lock or anyother lock incompatible with shared lock.

The answer is to create an Index on custid on so that Clustered Index scan becomes an Index Seek.

Now execute this statement to create an index on the table Invoice(custid)

CREATE

NONCLUSTERED INDEX [IX_Invoice_CustID] ON [dbo].[Invoice] (

[Custid]

ASC

)

ON [PRIMARY]

GO

If you now look at the query plan for update customer statement the query plan will use Index Seek (Refer the attached document "QueryPlan.doc" Section Query Plan B) . Now atleast you will not see a deadlock when different customers are updated from one session and not related invoices are updated in another session

Now try all the steps mentioned above. It should work fine as long as you don't update the same customer (from one session) use the invoice (from another session)

The attached zip file has two files in them

 

Deadlock.zip

Comments