question

ashok2012 avatar image
ashok2012 asked

To resolve Deadlock other than Nolock hints

Hi, We have SQL 2008 R2 We recently faced Deadlock on SQL Server. When Analyzed there is update and Create proc Statement and select Statement had involved in Deadlock So we have some SP's used nolock to prevent deadlock Is any other option there to resolve deadlock like RowLock/Pagelock?
deadlocknolock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
Please do not use NOLOCKs blindly. NOLOCKS results in dirty reads which can become a problem for your application. NOLOCKS are suggested when the tables thay are used against are seldom changed. If your tables have frequent transactions on them, NOLOCKS are not recommended. To help minimize deadlocks, you can use any of the below suggesstion- 1. Access objects in the same order. 2. Avoid user interaction in transactions. 3. Keep transactions short and in one batch. 4. Use a lower isolation level. 5. Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. OR. Use snapshot isolation. 6. Use bound connections. Check [this link][1] for further details in achieveing this. [1]: https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SET DEADLOCK_PRIORITY only specifies the relative importance between two queries when they are involved in a deadlock. What session to chose as the deadlock victim depends on each session's deadlock priority. If you know in advance which ones are ok to be set to HIGH, you can chose to, but if two queries, both HIGH are participating in deadlock, then it will come back to the optimizer to decide which one to roll back, based on its cost factor. "Can we reduce Deadlock at Database level?" - I formatted the list I gave in the answer before and yes you can go for Point#5 as a start. Details are there in the link I provided.
4 Likes 4 ·
Can we reduce Deadlock in Database level? To reduce deadlock in database level Any specific concept available in SQL?
0 Likes 0 ·
if we use SET DEADLOCK_PRIORITY HIGH can help to reduce deadlock victim?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.