Help With Deadlock

Question

Help With Deadlock

sree-226516

SSCrazy

Points: 2590
More actions
September 13, 2005 at 7:54 pm

#165480

Hi,
Could anybody help me with deadlock.I have captured the deadlock procs info in error log.I can see both spid's are on same Stored proc and same statement.The staement is something like this

insert into (select from.........)

I can see in the error log that lock is at index level(key) i.e row lock occurs with in an index(cluster index).How can I resolve index level locks to avoid simialr deadlock in feature?

Any help is appreciated.

Thanks,
Sree
Thanks,
SR

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Deni Kusdeni Ten Centuries Points: 1129 More actions · Answer 1

If you just want to query the data in the table, try to add "NOLOCK" option in your query statement, because with this statement if someone lock that record the query will display the result.

For example : insert into TableA (Field1,FieldB) select FieldA,FieldB from TableB NOLOCK

dbuchan SSCrazy Points: 2278 More actions · Answer 2

Yes the Nolock will allow the select to proceed, therefore completing the Insert

Can it be that the select performance could be improved ?

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

Deni Kusdeni Ten Centuries Points: 1129 More actions · Answer 3

Yes, because the query statement doesn't need to wait until the user release the record from the locking process.

For example :

TableA has a 100 records, record number 40 has been lock by UserA and record number 70 has been lock by UserB. If we do the query without NOLOCK statement, the query will wait until userA and UserB releasing the record. But if we add the "NOLOCK" the query will run and give the query result.

SDM SSCommitted Points: 1580 More actions · Answer 4

Hmmmm... surely there's more to this?

1. For a deadlock there should be 4 statements - 2 to compete for locks, and 2 to have created the locks that the other 2 are fighting over?

2. Even if the NOLOCK hint seems to have the desired effect, that puts each statement using it at some integrity risk. Since they're INSERTs, is it really desirable to insert data based on a condition which may change during the INSERT?