Error Message:
Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Why This Error?
Deadlock occurs when any two SQL server process IDs are locking a seperate resource and each one of them is trying to access the resource locked by the other process.
Fix/Resolution:
Check the below link for real time scenario and resolution to deadlock:
Deadlock Internals with Real Time Scenario – Explanation and Resolution
1) Try to use SQL server profiler to Analyze Deadlocks. http://msdn.microsoft.com/en-us/library/ms188246.aspx
2) In each script that has a transaction(locking) invoived, Begin the transaction as late as possible and commit it as soon as possible.
3) Try to set the optimal lock time value depending up on your script. Link: Using @@LOCK_TIMEOUT
4) SET the DEADLOCK_PRIORITY value in each script so that the priority processes wont be deadlocked. Link: SET DEADLOCK_PRIORITY
5) Use query hints for each TSQL Query.
Use WITH NOLOCK in select statements (when the waiting/locking is not needed)
ProcessID’s: Process ID 1 State 1 Process ID 2 State 2 Process ID 3 State 3 Process ID 4 State 4 Process ID 5 State 5 Process ID 6 State 6 Process ID 7 State 7 Process ID 8 State 8 Process ID 9 State 9 Process ID 10 State 10 Process ID 11 State 11 Process ID 12 State 12 Process ID 13 State 13 Process ID 14 State 14 Process ID 15 State 15 Process ID 16 State 16 Process ID 17 State 17 Process ID 18 State 18 Process ID 19 State 19 Process ID 20 State 20 Process ID 21 State 21 Process ID 22 State 22 Process ID 23 State 23 Process ID 24 State 24 Process ID 25 State 25 Process ID 26 State 26 Process ID 27 State 27 Process ID 28 State 28 Process ID 29 State 29 Process ID 30 State 30 Process ID 31 State 31 Process ID 32 State 32 Process ID 33 State 33 Process ID 34 State 34 Process ID 35 State 35 Process ID 36 State 36 Process ID 37 State 37 Process ID 38 State 38 Process ID 39 State 39 Process ID 40 State 40 Process ID 41 State 41 Process ID 42 State 42 Process ID 43 State 43 Process ID 44 State 44 Process ID 45 State 45 Process ID 46 State 46 Process ID 47 State 47 Process ID 48 State 48 Process ID 49 State 49 Process ID 50 State 50 Process ID 51 State 51 Process ID 52 State 52 Process ID 53 State 53 Process ID 54 State 54 Process ID 55 State 55 Process ID 56 State 56 Process ID 57 State 57 Process ID 58 State 58 Process ID 59 State 59 Process ID 60 State 60 Process ID 61 State 61 Process ID 62 State 62 Process ID 63 State 63 Process ID 64 State 64 Process ID 65 State 65 Process ID 66 State 66 Process ID 67 State 67 Process ID 68 State 68 Process ID 69 State 69 Process ID 70 State 70 Process ID 71 State 71 Process ID 72 State 72 Process ID 73 State 73 Process ID 74 State 74 Process ID 75 State 75 Process ID 76 State 76 Process ID 77 State 77 Process ID 78 State 78 Process ID 79 State 79 Process ID 80 State 80 Process ID 81 State 81 Process ID 82 State 82 Process ID 83 State 83 Process ID 84 State 84 Process ID 85 State 85 Process ID 86 State 86 Process ID 87 State 87 Process ID 88 State 88 Process ID 89 State 89 Process ID 90 State 90 Process ID 91 State 91 Process ID 92 State 92 Process ID 93 State 93 Process ID 94 State 94 Process ID 95 State 95 Process ID 96 State 96 Process ID 97 State 97 Process ID 98 State 98 Process ID 99 State 99 Process ID 100 State 100 Process ID 101 State 101 Process ID 102 State 102 Process ID 103 State 103 Process ID 104 State 104 Process ID 105 State 105 Process ID 106 State 106 Process ID 107 State 107 Process ID 108 State 108 Process ID 109 State 109 Process ID 110 State 110 Process ID 111 State 111 Process ID 112 State 112 Process ID 113 State 113 Process ID 114 State 114 Process ID 115 State 115 Process ID 116 State 116 Process ID 117 State 117 Process ID 118 State 118 Process ID 119 State 119 Process ID 120 State 120 Process ID 121 State 121 Process ID 122 State 122 Process ID 123 State 123 Process ID 124 State 124 Process ID 125 State 125 Process ID 126 State 126 Process ID 127 State 127 Process ID 128 State 128 Process ID 129 State 129 Process ID 130 State 130 Process ID 131 State 131 Process ID 132 State 132 Process ID 133 State 133 Process ID 134 State 134 Process ID 135 State 135 Process ID 136 State 136 Process ID 137 State 137 Process ID 138 State 138 Process ID 139 State 139 Process ID 140 State 140 Process ID 141 State 141 Process ID 142 State 142 Process ID 143 State 143 Process ID 144 State 144 Process ID 145 State 145 Process ID 146 State 146 Process ID 147 State 147 Process ID 148 State 148 Process ID 149 State 149 Process ID 150 State 150
Applied to SQL Server 2005,SQL Server 2008,SQL Server 2008 r2,SQL Server 2012