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.
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:
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.
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=4616Deadlocks 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 deadlockThis 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.
- SAP Managed Tags:
2 Comments
