SQL SERVER - Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Tuesday, March 10, 2009 6:34 AMHi,
I am getting deadlock exception"SQL SERVER - Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction". I am using just a single application there is no problem but when two or more clients are inserting records concurrently, I get the deadlock excpetion.
Can anyone give me the solution of this problem.deadlock occurs in Stored procedure.I am using sql server 2005.
Tuesday, March 10, 2009 7:26 AMHi,
To fix this issue, you need to make sure all the transactions should commit/rollback. No Transactions should be in between state. If your transaction did not commit or rollback, you can not query to that table.
Give me more details about your tables. and Procedures that you are using.
Tejas Shah http://tejasnshah.wordpress.com/
Tuesday, March 10, 2009 11:53 AMUse try catch in the stored procedure
IF @@TRANCOUNT > 0
Thursday, March 31, 2011 11:58 AM
Use try catch in the stored procedure
IF @@TRANCOUNT > 0
I have not used Store procedure.i have written sql query inline in code.
So how can i apply this thing in that?
Thursday, March 31, 2011 5:36 PM
The answer is quite similar. As I understand it, the SQL command is 'inline' in the code of your application. Is that correct? You do not have to have a stored procedure, but the TRY/CATCH code has to be in the same batch of code that raises the error. That means that all of this would be in the 'inline' code. See: http://www.codeproject.com/KB/database/SQLServer_deadlock.aspx
Also, there are good blogs by Bart Duncan: http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspx
If you cannot use the SQL TRY/CATCH, you have to trap the 1205 deadlock error in your application and then resubmit the command that failed. (This is the same thing that T-SQL's TRY/CATCH is enabling you to do, but outside of SQL Server.)
Thursday, August 09, 2012 10:01 AM
Use SQL Profiler and create a trace and proceed with the operation, where we can capture all the occurings happening.
To overcome this issue, we can set the DEADLOCK_PRIORITY. Making this configuration, we can overcome this issue.
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.
Please find the below reference lick for more details on this issue.
Hope this helps.