Skip to Content
Author's profile photo Klaus Liu

How to realize deadlock in HANA

In this blog, we will have a small test about how to realize a deadlock in HANA.


Multi version concurrency control ensures consistent read operations. However, it does not prevent concurrent write operations on the same data and the associated inconsistencies (dirty write, lost updates). To prevent concurrent write operations on the same data record, the SAP HANA database uses exclusive write locks at row level. For each write access, a row-level write lock is obtained. Concurrent transactions which need to write the same record have to wait until the lock is released. Write locks are implicitly requested before an update or delete operation is executed.

A deadlock situation occurs, if two transactions both need to update two records R1 and R2, and one transaction is given the lock for R1 and the other one the lock for R2. The transaction manager detects the deadlock and aborts one of the transactions.


In the following test, I created two stored procedures P1 and P2. Both P1 and P2 need to update all entries in one table(10000 entries). P1 updates the table from the last entry to the first entry. While P2 updates the table from the first entry to the last entry. Both P1 and P2 will execute at the same time. And one of the procedure will get aborted with deadlock error.






Execute the Transaction P1

Then execute the Transaction P2 in another SQL console session immediately

Here the transaction P2 aborted with the error deadlock detected.

Could not execute 'CALL "TRANSACTION_P2"' in 29.059 seconds . 
SAP DBTech JDBC: [133]: transaction rolled back by detected deadlock: "TRANSACTION_P2": line 8 col 3 (at pos 189): [133] (range 3): transaction rolled back by detected deadlock: TrexUpdate failed on table 'TEST1' with error: transaction rolled back by detected deadlock: Deadlock detected while executing transaction (TRANSACTION_ID=21, UPDATE_TRANSACTION_ID=60740880), rc=4616


Deadlocks are situations where two or more transactions lock each other cross-wise so that no transaction will ever be able to proceed. Usually deadlocks are caused by the application design.

If SAP HANA recognizes a deadlock based on a transactional lock, one of the transaction will be terminated and the following error is issued:

SQL error 133: transaction rolled back by detected deadlock

This behavior is different from other databases like Oracle where a deadlock will only terminate the current DML operation and not the whole transaction.


For more details, please refer to SAP noteĀ 1999998 – FAQ: SAP HANA Lock Analysis.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kjeld Sigtermans
      Kjeld Sigtermans

      Currently we are using CAP Java. In the 'before' section of our CDS event handlers, we are trying to run a UPSERT on the database, so it will be timely up-to-date to provide real-time data.

      However two (or more) calls simultaneously access the before handle, which causes the transaction rollback described in this article (it says the transaction is rolled back because a deadlock is detected).

      1. Can you (or anyone else) confirm that a transaction has already begun when the before handle is accessed?
      2. Can you confirm that no two transactions are allowed to write to two different tables in the same database model? (Because that is what seems to be happening).
      3. Finally how can we solve this, given that we really need to update/insert into tables in that before handle? We are thinking: is there some sort of way to manipulate the transaction behaviour? Maybe use Update and Insert instead of Upsert?



      Author's profile photo Syncher Pylon Peng
      Syncher Pylon Peng

      Thanks Klaus Liu. I have a question when the lock is released? At the end of transaction or end of each write operation?