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

Question

Hi,

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.

Answer 1

Hi,

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.

Thanks

---

Tejas Shah http://tejasnshah.wordpress.com/

Answer 2

Use try catch in the stored procedure

BEGIN TRY

BEGIN TRANSACTION

stmts...

END TRAN

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRAN

END CATCH

---

Thanks, A.m.a.L

Answer 3

Use try catch in the stored procedure

BEGIN TRY

BEGIN TRANSACTION

stmts...

END TRAN

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRAN

END CATCH

---

Thanks, A.m.a.L

I have not used Store procedure.i have written sql query inline in code.

So how can i apply this thing in that?

pls reply.

Thanks.

 

Answer 4

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.)

FWIW,
RLF

Answer 5

Hi,

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.

Fix/Workaround/Solution:
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.

http://blog.sqlauthority.com/2007/05/16/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/

Hope this helps.

Regards,
Thiyagu

---

Regards, Thiyagu.