February 2, 2014 at 11:43 pm
I am facing this error frequently.. during SP running under SQL Jobs at every day 3 times
SP_name: exec.dbo.usp_ShiftdataReporting
Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
Should I implement as below setting as below options at job steps for avoid the deadlock issues also avoid the failure job. i am looking for for your suggestion Pls.
option 1 - Procedure Level.
Use <DBName>
GO
SET DEADLOCK_PRIORITY LOW
GO
exec.dbo.usp_ShiftdataReporting
GO
Option 2 - DATABASE level
Transaction level to avoid the locking and blocking issues, to setting at database level
ALTER DATABASE <db name> SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT;
Thanks
ananda
February 3, 2014 at 12:14 am
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2014 at 5:50 am
Thank you for posting Dead lock article, it is very useful for me
READ_WRITE dead lock frequently happaned in my production system, this is 3 rd party application and they are not used any SP in database level all are prepared SQL Statement via application. (For DML operations). in this cause i can not set deadlock priority application coding level. so i will ask them to tune that query as well
I have created one SP for shift email reporting purpose that is also happend READ_WRITE deadlock, so i have set at execution level
use dbname
go
SET DEADLOCK_PRIORITY HIGH
go
exec dbo.usp_shiftdataReporting
go
Thanks
ananda
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply