SELECT FOR UPDATE Dump DBSQL_SQL_ERROR

[alejiandro_sensejl]

Dear Experts,

we are using SELECT FOR UPDATE für database lock. In case of conflict we expect second SELECT to lead to deadlock and are displayed in DB01. In general this locks only last for 1 or 2 seconds and the second SELECT continues.

In some systems this does not lead to HOLD status in DB01 but rather there is just a short dump Category Installation Errors, Runtime Error DBSQL_SQL_ERROR, Exception CX_SY_OPEN_SQL_DB.

Database error text:
SQL message: ORA-00060: deadlock detected while waiting for resource
SQL dbsl rc: 99

When trying to check via debugging we always see DB HOLD, so we cannot directly test the issue but only see dumps in ST22.

- What are the reasons for dump instead of hold status?
- I asked our basis guys, but they have no clue. In google I found some references to issues in ABAP Kernel (we use 749pl222). Is this maybe a known kernel issue?

Thank you for your help!

Best regards,
Alej

[retired_member]

The error comes from the DB. How should it be an ABAP kernel error?

And by the way, the documentation for FOR UPDATE says: "If set incorrectly, the lock can produce a deadlock". So I'd say, the lock is set incorrectly.

[alejiandro_sensejl]

Thank you for your very fast feedback - much appreciated. I am well aware that the issue itself is an DB error. I am still searching for the reason why there is a dump some sometimes instead of hold. That behaviour could be an ABAP kernel error, doesn't it?

Currently I am researching help documentation....

This documentation clearly states that the second process will wait for the first one that holds the lock:

[...] If a transaction cannot lock an object because it is already locked by another transaction, it waits until the other transaction has released the lock. This can result in a deadlock. A deadlock occurs, for example, when two transactions are waiting for a lock held by the other. https://help.sap.com/doc/abapdocu_731_index_htm/7.31/en-US/abendb_lock.htm

On the other hand this documentation mentions that there IS always an exception on a deadlock:

If setting the lock produces a deadlock, an exception is raised.

https://help.sap.com/doc/abapdocu_731_index_htm/7.31/en-US/abapselect_clause_lines.htm#!ABAP_ONE_ADD...

So I am still wondering what is the expected behaviour at all for this...

[retired_member]

In fact, I don't see a contradiction here.

If a deadlock is detected, there is an exception. If a situation can be resolved by waiting, there is no exception. That's my understanding.

[alejiandro_sensejl]

Nevermind... Learnt from Wiki https://wiki.scn.sap.com/wiki/pages/viewpage.action?pageId=414089456 and KBA 84348 - Oracle deadlocks, ORA-00060 how to properly trace what is going on. Issue is caused by some custom implementation in implicit enhancement (yikers..) we were not aware of. We will talk to developer tomorrow how to resolve this.

Therefore, of course, documentation is very correct and no issue in kernel but rather an customer application error..