-2

Following error occurred when 2 sessions accessing the same stored procedure(deadlocked occurred by accessing same stored procedure at same time by 2 users/sessions). ERROR : Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Stored procedure written to delete data based on date filter in where clause. how to use SET DEADLOCK_PRIORITY in this scenario or any alternative.

Pravin
  • 101
  • 4
  • how to use SET DEADLOCK_PRIORITY in this scenario or any alternative. – Pravin Feb 28 '18 at 12:08
  • Redesign your procedure to: 1) use updlock hints 2) handle deadlock cases with try...catch, retry when needed 3) in extreme cases use applocks – Arvo Feb 28 '18 at 12:47

1 Answers1

0

When you have threading with shared resource, you can expect such behavior. Please provide more information if you want more precise answer.

Deadlock occurs when one process needs to access data that is locked by another process. The other process wants to access the data from the first one, so neither can proceed. SQL Server is smart enough to recognize this messup and choose a favorite or stop both processes.

To resolve the issue, you better not set DEADLOCK priority if possible. The users with the same priority may also cause DEADLOCK. The best approach is to synchronize access when deleting. When one of the delete procedures is active on the table, the other should wait.

This is a good example of such synchronization: SQL Server - How to lock a table until a stored procedure finishes

GeorgiG
  • 1,018
  • 1
  • 13
  • 29
  • Hi Georgi, waiting of process is default or we have to set any parameter. because here same stored procedure locked with different victimProcess ID. – Pravin Feb 28 '18 at 12:17
  • This is a better example: https://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes/3662820. – GeorgiG Feb 28 '18 at 12:22
  • Your goal is to lock the entire table during deletion. When table is released, then the other Stored procedure will do its work. The point is that this way you will never get a DEADLOCK (because only 1 procedure can lock table rows at a time), albeit forfeiting some efficiency. If you set priorities, there will always be possibility for another DEADLOCK. – GeorgiG Feb 28 '18 at 12:24