HANA DB Locks

Home


1. Present lock situations

Check the Alerts Monitor in HANA Studio for alerts indicating lock situations, which may serve as a starting point for the following investigations:
Check for blocked transactions ("Performance -> Load", "Performance -> Blocked Transactions", "Performance -> Threads") to determine the number of blocked transactions.
                       When transactions are blocked, identify the session that blocks other transactions from ("Performance -> Sessions")or ("Performance -> Blocked Transactions").


                       To resolve the blocking situation, the following actions can be taken.

                       1. Check the application logic of the currently blocking session to see if the application logic can be changed to avoid the blocking situation.

                       2. Consider changing the schedule or parameterization of the application modifying those database objects and thereby blocking those transactions.

                       3. You can terminate the blocking session manually if you need to resolve the situation immediately.

                       4. When further analysis is needed to find out the root cause of the blocking transaction, follow the instructions in later part of this SAP Note and collect diagnosis information and send the information to SAP Support.

The following "System Information" views provide additional information on blocked transactions, locks and sessions: "Open Transactions", "Blocked Transactions", "Table locks", "Record Locks", "Sessions".
A possible root cause for a temporary increase of internal locks are contentions on system resources. background processes.
A possible root cause for a temporary increase of internal locks are contentions on those system resources.
                       To determine temporal coincidences of deteriorated performance with increasing lock contention caused by background jobs, use the HANA Studio monitors "Performance -> Load", and "Performance -> Job Progress" and the "System Information" views to check the status of "(Delta) Merge Statistics", "Failed Backups", "Backup catalog" "MVCC blocker transaction", "MVCC blocker connection".

                       To find the times when savepoints have been written, submit a query on the system view "SYS"."M_SAVEPOINTS". The view SYS"."M_CS_UNLOADS" logs phases of intensive column data unloads from memory.

To find wait situations at a thread level, open the HANA Studio monitor "Performance -> Threads", select "Create call stacks", and repeatedly refresh the current page every few minutes. Check which threads do not change their call stacks even after many repetitions.
Unchanged call stacks can have many other root causes than lock contention, e.g., long running queries, open sessions waiting for a user action, or background processes.
                       To find out the root cause of a long running query, the following checks can be performed.

Number of row store version is over 1,000,000:
                       1. Check number of row store versions using the following query:

                       select * from m_mvcc_tables

                       If the number of versions is over 1 million, it might affect overall system performance. Therefore, we need to find out which transaction blocks garbage collection, possibly by long-running or unclosed cursors, long-running serializable/repeatable read isolation mode transactions, or hanging threads.

                       2. From "System Information" views, identify the transaction and connection information that is blocking the garbage collection by checking "MVCC blocker transaction" and "MVCC blocker connection".

                       3. Identify Query String of the problemaic cusror or query using the following query:

                       select * from m_prepared_statements where statement_id = (select current_statement_id from m_connections where connection_id = (select connection_id from m_transactions where min_mvcc_snapshot_timestamp = (select min(Value) from m_mvcc_tables where name = 'MIN_SNAPSHOT_TS') and connection_id > 0))

                       4. Get more information on the session context:

                       select * from m_session_context where connection_id = (select connection_id from m_transactions where min_mvcc_snapshot_timestamp = (select min(Value) from m_mvcc_tables where name = 'MIN_SNAPSHOT_TS') and connection_id > 0)

                       You can get more detailed information on the session such as application program, application user from this query.

                       5. Analyze the query why it takes long

                       Check application and solve the problem. For example, application program has to be changed if there is any unclosed cursor or uncommitted transaction.

                       6. If a persistent sequence is used with "NO CACHE" option, then please check SAP Note 1977214 SAP HANA: Growing number of rowstore table versions when a persistent sequence is used.

                       7. If the problem is not resolved, then follow the instructions in later part of this SAP Note and collect diagnosis information and send the information to SAP Support.

Number of row store version is less than 1000000, but still a performance is not good and need further investigation, then follow the instructions in later part of this SAP Note and collect diagnosis information and send the information to SAP Support.
When further analysis is needed to find out the root cause of the wait situation at a thread level, follow the instructions in later part of this SAP Note and collect diagnosis information and send the information to SAP Support.
2. Past lock situations
Identify the exact time frame when the performance temporarily decreased.
For example, by using the "Performance -> Load" monitor, status of resource consumption such as CPU or memory usage or SQL workloads such as number of active connections, statements, blocked transactions, versions, active read / write requests, column unload status can be checked. HANA trace files also can be used to identify the time frame of problematic situation by checking alerts or warning / error messages.
                       The time frame is a valuable filter for further analysis.

                       When HANA database is not restarted after the performance downgrade, the "System Information" views can be checked further. If the performance issue is resolved without a database restart, then it indicates that the database was not in a hang situation, but the database performance was temporarily decreased.

                       If HANA database has been restarted after a hang situation, the history of database status can be found in "_SYS_STATISTICS" schema:

                       "HOST_LONG_RUNNING_STATEMENTS","HOST_LONG_RUNNING_SERIALIZABLE_TRANSACTION","HOST_LONG_IDLE_CURSOR","HOST_LONG_RUNNING_UNCOM MITTED_WRITE_TRANSACTION","HOST_MEMORY_STATISTICS","HOST_RESOURCE_UTILIZATION_STATISTICS","HOST_SAVEPOINTS", "HOST_VOLUME_IO_PERFORMANCE_STATISTICS"

To check if a savepoint was written during a pre-defined time period, submit a query on the view "SYS"."M_SAVEPOINTS" and specify a suitable time window for the corresponding attribute(s). A longer history can be found in "_SYS_STATISTICS"."HOST_SAVEPOINTS". Similarly, the view "SYS"."M_CS_UNLOADS" saves time information when column data is unloaded from memory.
To search for lock messages in trace files, open the HANA DB Studio "Diagnosis Files" view and primarily examine the indexserver alert trace files. In a scale-out environment, there is one such file for each host. Search for the strings "lock", "wait", and "transaction". Check if the messages refer to lock situations and the timestamps match the period of performance deterioration.
3. Collect Diagnosis Information for SAP Support
                       To collect Diagnosis Information ("runtimedump") including configurations and trace files, follow the SAP Notes 1837439 "Activating the Emergency Support Package for DB support" and 1732157 "SAP HANA: Howto get system dump". Send the system dump archive to SAP Customer Support who will provide you an SAPMATS container link for the upload. Small dump archives can be directly attached to OSS messages.

                       This collection of Diagnosis information is helpful for both present and past lock situations.

                       If the HANA DB System currently shows a serious hang situation, repeat the collection of Diagnosis files again after a few minutes and additionally send the second dump archive to SAP Customer Support.



2. SAP HANA locking issues?

You suffer from terminations and short dumps due to SQL errors 131 or 133:

SQL error 131: transaction rolled back by lock wait timeout
SQL error 133: transaction rolled back by detected deadlock
SQL error 146: resource busy and NOWAIT specified
The mvcc_anti_ager reports in a trace file:

There are too many lock items on this system.


3. Types of locks exist?

We can distinguish the following SAP HANA lock types:

Lock type	Lock wait thread state	Scope	Views	Details
Record lock	ConditionalVariable Wait	transactional	M_RECORD_LOCKS	Exclusive locks on record level, typically caused by concurrent changes of the same records by different transactions
	(RecordLockWaitCondStat / TransactionLockWaitCondStat)		M_CONDITIONAL_VARIABLES
			M_BLOCKED_TRANSACTIONS
			M_OBJECT_LOCK_STATISTICS
Object lock	ConditionalVariable Wait	transactional	M_OBJECT_LOCKS	Locks on object level, typically caused by DDL operations requiring an object lock:
	(TableLockWaitCondStat / TransactionLockWaitCondStat)		M_CONDITIONAL_VARIABLES
			M_BLOCKED_TRANSACTIONS	Shared locks (INTENTIONAL EXCLUSIVE): Set in case of DELETE, INSERT, MERGE, SELECT FOR UPDATE, UPDATE and UPSERT operations on table
			M_OBJECT_LOCK_STATISTICS	Exclusive locks (EXCLUSIVE): Set in case of DDL operations on table and in case of an explicit LOCK TABLE operation
Read / write lock	SharedLock Enter	internal	M_READWRITELOCKS	Read / write lock waits, e.g. waits during critical savepoint phase
	ExclusiveLock Enter
Semaphore	Semaphore Wait	internal	M_SEMAPHORES	Low level locks based on semaphores, e.g. waits for a critical delta merge phase
Mutex	Mutex Wait	internal	M_MUTEXES	Low level locks based on mutexes
Barrier	Barrier Wait	internal		Low level locks similar to mutexes
Speculative locks	Speculative Lock Retry backoff	internal		Low level transactional memory locks (SAP HANA >= Rev. 122)
	Speculative Lock Wait for fallback
liveCache lock		internal	M_LIVECACHE_LOCKS	Locks related to integrated liveCache (if used)
Nameserver topology		special		The nameserver topology is locked based on a file (/tmp/.hdb_<sid>_<inst_id>_lock)

Record and object locks are mainly linked to the application transactions while read / write locks,
semaphores, mutexes and barriers are managed by SAP HANA internally.

Be aware that the table above concludes from a lock type to a thread state.
The opposite way is not generally valid. For example, there can be "ConditionalVariable Wait"
situation that are not linked to a record lock or object lock

4. To check if my SAP HANA database suffers from lock waits?

On a very elementary level you can identify lock waits based on the thread states.

Current threads can be displayed via:

Transaction DBACOCKPIT: Performance --> Threads
SAP HANA Studio: Performance --> Threads
SQL: "
HANA
_Threads_CurrentThreads" (SAP Note 1969700)
Historic thread activities can be determined via:

SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" and "HANA_Threads_ThreadSamples_AggregationPerTimeSlice" (SAP Note 1969700)
The following thread states indicate lock wait situations:

Barrier Wait
ConditionalVariable Wait
ExclusiveLock Enter
Mutex Wait
Semaphore Wait
SharedLock Enter
Sleeping

Not every occurrence of these states is critical. It happens frequently that a thread submits a request to another thread and waits for a
related semaphore until the called thread has returned the result.

5. Do timeouts for lock waits exist?

Transactional lock waits are terminated when the time limit defined with the following parameter is exceeded:

indexserver.ini -> [transaction] -> lock_wait_timeout
Its default value 1800000 which represents 1800000 ms / 1800 s / 30 minutes. This means that a lock wait is terminated after 30 minutes and the following error message is issued:

SQL error 131: transaction rolled back by lock wait timeout
This behavior is different from other databases like Oracle where no timeout for exclusive lock waits exist and transactions will wait for the lock until the lock is available or the transaction is manually terminated.

For internal lock waits no timeout is implemented.


6. How long certain types of locks are held?

Normally it is most important to analyze lock wait situations, i.e. the concurrent access to the same resources. For transactional locks you can additionally see how long locks are held (even if no transaction is waiting) by using the following approaches:

M_OBJECT_LOCKS, M_RECORD_LOCKS, M_OBJECT_LOCK_STATISTICS
SQL: "HANA_Locks_Transactional_Current" / SQL: "HANA_Locks_Transactional_Total" (SAP Note 1969700)