SQL error 1205 when accessing a table "XXXXXX" - deadlock

[Former Member]

Hi Experts,

I came across a dead lock situation on my Solution Manager System and ST22 has lot of ABAP dumps "DBIF_RSQL_SQL_ERROR" and still continuing.

Application : SAP Solution Manager 7.1

OS : Windows

DB : SQL Server 2008 R2

Short text

    SQL error 1205 when accessing table "MES_DB_AGGREGATE".

What happened?

    Error Text of the Database: "Transaction (Process ID 87) was deadlocked on lock

     resources with another process and has been chosen as the deadlock victim.

     Rerun the transaction."

I have gone through many sap notes on this dead lock, but still not clear on what is the exact solution for clearing this locks.

Can someone please throw some light on this?

Regards,

Leela.

[luisdarui · Accepted solution]

Hi Leela,

This wiki page may help you find the root cause of this deadlock:

Information about deadlocks in DBACockpit - SAP on SQL Server - SCN Wiki

Kind Regards,

Luis Darui

[former_member294862 · Accepted solution]

Hello Leela,

Please go through the below SNOTE, and let us know if still you are facing the issue.

2021463 - Composite SAP Note for the necessary corrections to the "Event Calculation Engine" in Support Package 12.

Regards

Eknath

[luisdarui · Accepted solution]

Hi Eknath,

Bull's-eye!

We maintain known deadlocks under BC-DB-MSS component even if they belongs to another components, to ease customers finding this.

I asked the owner of this note to add BC-DB-MSS as sub-component, that will definitely help finding this note.

Thanks and kind regards,

Luis Darui

[Former Member · Accepted solution]

Eknath,

     I implemented the note and applied the manual corrections as per the note and the dumps stopped now.

Thanks a lot for the SAP Note, this was a direct fix for my issue.

Regards,

Leela.

[former_member182657]

Hi Leela,

SQL error 1205 when accessing table "MES_DB_AGGREGATE".

Suggest you to follow SAP Notes

32129 - Deadlock analysis for the SQL server

1870847 - How to judge if a SQL Server deadlock is caused by parallel processes using the deadlock ...

Also try to implement the relevant support package to the system as per release to check again.

Regards,

[Former Member]

Gaurav,

thanks for your response, I am looking for a solution like, is there a way we can find which SPID's were causing the lock on that particular table.

Also if someone can explain a way for clearing the lock(like killing the SPID causing the lock etc... )

pros and cons using such procedure(killing the SPID).

Regards,

Leela.

[Sriram2009]

Hi Leela

1. Could you share the full dump as text format?

2. Could you refer the SAP Note follow the solution part to identify the deadlock in MS Sql DB.

1774085 - Deadlock analysis for SQL Server (Advanced)

Regards

SS