SQL Server Performance Forum – Threads Archive

Dead lock between a delete and a select

We are using SQL 2K with sp4. Once in a while we have deadlock between a Select statement and a Delete statement on a table. I have some general understanding about deadlock and like to confirm what I believe is correct. The where clause in the Delete statement is based on a clustered index with only one field. The select statement invoves three fields but none of them belongs to the clustered index field. THe Delete statement was granted IX and requesting IX on a page lock and the Select statement was granted S and was requesting S key lock. The delete statement was chosen to be the deadlock victim. The reason the deadlock happened I believe was because the record(s) it tried to delete was in the same page as the records the Select statement was selecting. Since the delete statement triggered SQL server to use Page lock, am I right that I should force it to use row lock by using RowLock hint so pagelock won’t be used? Is there another way I should attack a deadlock between a Select(key lock) and Delete (page lock) situtation? Wingman

As far as i know, Locks cannot be confined to particular fields.
what is the expected rows that are deleted? if that is less, you can force a row hint on the delete statement and read committed hint for select statement. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Are you using SELECT .. with NOLOCK hint? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Is either operation wrapped in an explicit transaction ?
Have you tried selecting with NOLOCK hint ?

Are you using SELECT .. with NOLOCK hint?
—————————————-
MUST ADD begin tran and commit

hi
try to use the following tips 01. Keep the transactions one batch
02. Use the low isolation level
03. Keep the access obejcts in one order
04. Use select with no lock then u can minimise the dead lock situation.
SURYA

Also readhttp://sql-server-performance.com/deadlocks.asp Madhivanan Failing to plan is Planning to fail

]]>