adam99
July 12th, 2009, 10:44 AM
I have a table which causes a deadlock on its nonclustered index. We use page level locking.
The problem seems to be that during deletion and insertion nonclustered index is used. Even though 2 processes are deleting and inserting the different data, they try to keep a lock on the same part of the index pages. Then a deadlock occurs.
In order to avoid this problem, I started putting an exclusive lock to the tables. However I would like to learn a little bit more about the cause of the deadlock
1. During a deletion/insertion of multiple rows do the index pages of data other than what has been changed need to be updated? That seems to be the case in my problem. Why is that?
2. Can a single row delete/insert cause the same problem. In my case it is a multiple row delete/insert, but if I could get a deadlock on single row update, then I will need to modify other parts of my code.
3. My clustered index is only using dates (which is one column in my table), nonclustered index has other date and two other columns which are in the table. Delete and insert statements use date and column1 to reference data. If I did not have nonclustered index, is it possible to get a deadlock on the clustered index while updating different rows. I am not very familiar with deadlocks on indices, if you have any reference that I could look up, it will be great.
---
Deadlock Id 1: detected. 1 deadlock chain(s) involved.
Deadlock Id 1: Process (Familyid 0, Spid 434, Suid 143) was executing a DELETE command in the procedure 'fill_universe' at line 1065 at nested level 4.
Deadlock Id 1: Process (Familyid 0, Spid 415, Suid 143) was executing a INSERT command in the procedure 'fill_universe' at line 1076 at nested level 4.
Deadlock Id 1: Process (Familyid 0, Spid 415) was waiting for a 'shared page' lock on page 5574695 of the 'test_table' table , indid 2 in database 'testx' but process (Familyid 0, Spid 434) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 434) was waiting for a 'exclusive page' lock on page 5550470 of the 'test_table' table , indid 2 in database 'testx' but process (Familyid 0, Spid 415) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 415) was chosen as the victim. End of deadlock information.
---
CREATE TABLE dbo.test_table (
update_date date NOT NULL,
model_data_id smallint NOT NULL,
column1 char(6) NOT NULL,
column2 varchar(8) NOT NULL
)
LOCK ALLPAGES
WITH max_rows_per_page = 0,
reservepagegap = 0,
identity_gap = 0
ON 'default'
GO
ALTER TABLE dbo.test_table
ADD CONSTRAINT FK_model_data_
FOREIGN KEY(model_data_id)
REFERENCES testx.dbo.model_data(model_data_id)
GO
CREATE INDEX I1_test_table
ON dbo.test_table(column1)
GO
CREATE UNIQUE INDEX IU_test_table
ON dbo.test_table(model_data_id, update_date, column1)
GO
CREATE CLUSTERED INDEX IC_test_table
ON dbo.test_table(update_date)
WITH allow_dup_row
GO
The problem seems to be that during deletion and insertion nonclustered index is used. Even though 2 processes are deleting and inserting the different data, they try to keep a lock on the same part of the index pages. Then a deadlock occurs.
In order to avoid this problem, I started putting an exclusive lock to the tables. However I would like to learn a little bit more about the cause of the deadlock
1. During a deletion/insertion of multiple rows do the index pages of data other than what has been changed need to be updated? That seems to be the case in my problem. Why is that?
2. Can a single row delete/insert cause the same problem. In my case it is a multiple row delete/insert, but if I could get a deadlock on single row update, then I will need to modify other parts of my code.
3. My clustered index is only using dates (which is one column in my table), nonclustered index has other date and two other columns which are in the table. Delete and insert statements use date and column1 to reference data. If I did not have nonclustered index, is it possible to get a deadlock on the clustered index while updating different rows. I am not very familiar with deadlocks on indices, if you have any reference that I could look up, it will be great.
---
Deadlock Id 1: detected. 1 deadlock chain(s) involved.
Deadlock Id 1: Process (Familyid 0, Spid 434, Suid 143) was executing a DELETE command in the procedure 'fill_universe' at line 1065 at nested level 4.
Deadlock Id 1: Process (Familyid 0, Spid 415, Suid 143) was executing a INSERT command in the procedure 'fill_universe' at line 1076 at nested level 4.
Deadlock Id 1: Process (Familyid 0, Spid 415) was waiting for a 'shared page' lock on page 5574695 of the 'test_table' table , indid 2 in database 'testx' but process (Familyid 0, Spid 434) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 434) was waiting for a 'exclusive page' lock on page 5550470 of the 'test_table' table , indid 2 in database 'testx' but process (Familyid 0, Spid 415) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 415) was chosen as the victim. End of deadlock information.
---
CREATE TABLE dbo.test_table (
update_date date NOT NULL,
model_data_id smallint NOT NULL,
column1 char(6) NOT NULL,
column2 varchar(8) NOT NULL
)
LOCK ALLPAGES
WITH max_rows_per_page = 0,
reservepagegap = 0,
identity_gap = 0
ON 'default'
GO
ALTER TABLE dbo.test_table
ADD CONSTRAINT FK_model_data_
FOREIGN KEY(model_data_id)
REFERENCES testx.dbo.model_data(model_data_id)
GO
CREATE INDEX I1_test_table
ON dbo.test_table(column1)
GO
CREATE UNIQUE INDEX IU_test_table
ON dbo.test_table(model_data_id, update_date, column1)
GO
CREATE CLUSTERED INDEX IC_test_table
ON dbo.test_table(update_date)
WITH allow_dup_row
GO