Hana Transaction Rolled Back by an Internal Error
When blocking events such as frozen SQL queries or frozen internal sap hana system tasks and services occur, you have to react quickly in order to release locks. Locks may disappear naturally after a while but you need to know how long the application system can wait until you are able to kill blocking transastions
how to check for lock events in sap hana?
In Hana Studio
When the Alert 49 "Check blocked transactions" or the Alert 59 "Percentage of transactions blocked" is showing in sap hana studio, you need to start investigating.
How to analyse sap hana Alert 49 "Check blocked transactions"?
This alert is reporting that transactions are waiting for locks to be released in order to continue. Too many of those alerts indicate possible serious problems.
How to analyse sap hana Alert 59 "Percentage of transactions blocked"?
This alert is reporting than more transactions are continuing to be blocked. If nothing is done to release the locks, your database may come to a stand still, and it is likely you will have to restart it.
In indexserver trace files
Look into the indexserver trace file for the following messages.
"Lock timeout occurs while waiting TABLE_LOCK/RECORD_LOCK of mode EXCLUSIVE"
"long running uncommitted write transaction detected"
"There are too many un-collected versions. The transaction blocks the garbage collection of HANA database."
"Deadlock detected while executing transaction".
To search messages in trace directories
For SYSTEMDB : /usr/sap/< SID >/HDB< instance nb >/< hostname >/trace for SYSTEMDB
For Tenant database : /usr/sap/< SID >/HDB< instance nb >/< hostname >/trace/DB_< tenant name >
For ex : "$>sudo patadm drwxr-x--x 2 patadm sapsys 4096 Jun 30 11:53 DB_TEST/ cd DB_BBD/ -rw-r----- 1 patadm sapsys 6950751 Aug 3 12:53 indexserver_alert_linux7.trc patadm@linux7:/usr/sap/PAT/HDB02/linux7/trace/DB_BBD $> egrep 'deadlock|long|running' indexserver_alert_linux7.trc
patadm@linux7:/usr/sap/PAT/HDB02/linux7/trace $> ls -lrt -d */
drwxr-x--x 2 patadm sapsys 4096 Jul 16 23:35 DB_BBD/
drwxr-x--x 2 patadm sapsys 4096 Jul 18 13:45 DB_DAT/
drwxr-x--x 2 patadm sapsys 4096 Jul 18 13:46 DB_FBW/
drwxr-x--x 2 patadm sapsys 4096 Jul 18 14:06 DB_MAI/
drwxr-x--x 2 patadm sapsys 4096 Jul 26 12:49 DB_GRD/
patadm@linux7:/usr/sap/PAT/HDB02/linux7/trace/DB_BBD $> ls -lrt
-rw-r----- 1 patadm sapsys 10000271 Jun 20 2016 indexserver_linux7.30255.002.trc
-rw-r----- 1 patadm sapsys 10000534 Jun 21 2016 indexserver_linux7.30255.003.trc
-rw-r----- 1 patadm sapsys 10000609 Jun 21 2016 indexserver_linux7.30255.004.trc
-rw-r----- 1 patadm sapsys 10752737 Mar 3 12:24 indexserver_linux7.30255.executed_statements.000.trc
What are the main lock events in sap hana?
Current lock event and historical lock event are 2 categories of waiting events. Occuring locks can be monitored and analysed on the spot with hana studio. Periodical locks can also be analysed with hana studio when it is entering the known transaction time frame. Current or occuring locks are happening now where as periodical or historical locks happened in the past during a time frame.
For example, You may face performance issues during the day because of locking query transactions. You will be starting analyses immediately to resolve the probleme. On the other hand, locking events may have happened during the night for a short period of time. Analyses on old locking issues will be possible after the event during working hours.
How to check with hana studio for blocked transactions?
How to identify with hana studio which sessions are blocking other transactions?
To show number of blocked transactions
Performance -> Sessions
Performance -> Blocked Transactions
How to resolve sap hana transactions locked issues?
Here are some steps to follow :
· check whether the application has been tested or not for the current situation? ·Is it possible to resolve the locking issues by setting sap hana parameters such as "idle_cursor_lifetime" and "uncommitted_write_transaction_lifetime"
See Also : How to set query timescale grace period in sap hana?
·Check if you can kill the transaction or the session immediately?
See Also : How to kill sessions and transactions in sap hana?
Here are system information views to obtain more lock event details
"Open Transactions" : M_TRANSACTIONS
"Blocked Transactions" : M_BLOCKED_TRANSACTIONS
"Table locks"
"Record Locks"
"Sessions".
How to find out sap hana lock event details?
How to analyse wait and lock events when hana database has been restarted?
Your hana tenant database was retarted because it was completely at a stand still. You have to find out what was the source of the locking issues.
You can inquire the following views from the "_SYS_STATISTICS" schema in order to get more details on locking events.
"HOST_LONG_RUNNING_STATEMENTS"
"HOST_LONG_RUNNING_SERIALIZABLE_TRANSACTION"
"HOST_LONG_IDLE_CURSOR"
"HOST_LONG_RUNNING_UNCOMMITTED_WRITE_TRANSACTION"
"HOST_MEMORY_STATISTICS"
"HOST_RESOURCE_UTILIZATION_STATISTICS"
"HOST_SAVEPOINTS"
"HOST_VOLUME_IO_PERFORMANCE_STATISTICS"
How to check sap hana save point is occuring during locking events?
When hana database transactions starts to slowdown, everything within sap hana can be affected. The save point event is an important part of the sap hana system. It garantees data integrity. If the database freeze for whatever reasons the saving point may not be happening. It is therefore important to check whether the internal sap hana mecanism is working when locking alerts is occuring.
The following views can be used to find out about save point activities
"SYS"."M_SAVEPOINTS"
"_SYS_STATISTICS"."HOST_SAVEPOINTS"
"SYS"."M_CS_UNLOADS"
How to check how long a sap hana transaction has been waiting?
How to deal with a dead lock in sap hana?
What are the different type of locks in sap hana ?
There is a number of different types of lock in sap hana. Locks are important and protect data integrity within the sap hana system. However when too many locks are in place at once, it could deteriorate performance drastically.
Here are the various lock descriptions :
At the transaction level.
The record lock is an exclusive lock at record level which is happening for a transaction such as an update.
The object lock is an exclusive or shared lock at object level. The lock will occur on DDL operations.
The metadata lock is a lock on the object structure. ALTER TABLE for instance cannot be done while the table is in use
At the internal hana system level
The read and write lock is a shared or exclusive lock. It is happening during sap hana savepoint operation.
.
How to check for lock events in sap hana?
Lock events show up in various ways within log and trace files or at run time within hdbsql or studio SQL console.
SQL error may come up with one of the following messages:
· SQL error 131: transaction rolled back by lock wait timeout
· SQL error 133: transaction rolled back by detected deadlock
Lock messages will be written to the trace file :
· There are too many lock items on this system.
· Deadlock detected: Deadlock detected while executing transaction (TRANSACTION_ID=< tid >, UPDATE_TRANSACTION_ID=< utid >):
SAP HANA alerts will point out problematic waiting situations. Those messages will appear in Hana studio
· The alert :16 is a lock wait timeout. This is a waiting event indication and waiting time can be configured.
· The alert :49 indicate a blocking event situations on a long query.
· The alert :59 show the percentage of transactions blocked.
Is there a sap hana deadlock holding several transactions?
How to analyse sap hana waiting events?
How to check whether a sap hana table or a row of data is locked or not?
How to check the different locks occuring in sap hana?
How to analyse sap hana internal locks?
Other related links to hana systemdb and tenant databases?
Back to top
Hana Transaction Rolled Back by an Internal Error
Source: http://www.bestsaphanatraining.com/how-to-analyse-waiting-events-in-sap-hana-t3.html
0 Response to "Hana Transaction Rolled Back by an Internal Error"
Post a Comment