deadlock on nonclustered index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

deadlock on nonclustered index

Author

Topic

adamquestion
Starting Member

2 Posts

Posted - 2009-07-11 : 08:20:23

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-11 : 08:54:58

Deadlocks are resource (row) contentions not related to database locking as such, more about what you are actually trying to do. If you are transactionally updating A, B, C while someone else is updating C, B, A you can get to the point where you are waiting for each other to finish before they can continue.
The solution here is to make sure that you update each table in the same order every time.
Adding more aggressive locking will make things slower, not better.

adamquestion
Starting Member

2 Posts

Posted - 2009-07-11 : 11:38:52

I don't agree, my deadlock problem seems to be solved by putting begin tran and commit tran around delete&insert statements, and locking table before them.

I have tested this several times, thus it seems to be working. It makes delete and insert to wait and there is no deadlock on index.

----

begin tran

lock table test_table in exclusive mode

delete ....

insert ....

commit tran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources