How can transactional lock waits be analyzed and optimized? Alert 59: Percentage of transactions blockedīelow you can find more specific information how to analyze certain lock wait situations.ĥ.Alert 49: Long-running blocking situations.The following SAP HANA alerts are another indication for locking issues: SQL error 133: transaction rolled back by detected deadlock.SQL error 131: transaction rolled back by lock wait timeout.The following SQL errors also indicate lock related problems: An 'X' in column C indicates potentially critical situations. The comprehensive check command SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700) contains also lock related checks. ![]() It happensįrequently that a thread submits a request to another thread and waitsįor a related semaphore until the called thread has returned the result. Not every occurrence of these states is critical. The following thread states indicate lock wait situations: SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" and "HANA_Threads_ThreadSamples_AggregationPerTimeSlice" (SAP Note 1969700).Historic thread activities can be determined via: SQL: " HANA_Threads_CurrentThreads" (SAP Note 1969700).SAP HANA Studio: Performance -> Threads.Transaction DBACOCKPIT: Performance -> Threads.On a very elementary level you can identify lock waits based on the thread states. How can I check if my SAP HANA database suffers from lock waits? For example, there canīe "ConditionalVariable Wait" situation that are not linked to a recordĤ. Transactions while read / write locks, semaphores, mutexes andīarriers are managed by SAP HANA internally.īe aware that the table above concludes from a lock type to a thread Record and object locks are mainly linked to the application The nameserver topology is locked based on a file (/tmp/.hdb_lock) Locks related to integrated liveCache (if used) Low level locks based on semaphores, e.g. Exclusive locks (EXCLUSIVE): Set in case of DDL operations on table and in case of an explicit LOCK TABLE operation.MERGE, SELECT FOR UPDATE, UPDATE and UPSERT operations on table Shared locks (INTENTIONAL EXCLUSIVE): Set in case of DELETE, INSERT,.Locks on object level, typically caused by DDL operations requiring an object lock: (TableLockWaitCondStat / TransactionLockWaitCondStat) (RecordLockWaitCondStat / TransactionLockWaitCondStat)Įxclusive locks on record level, typically caused by concurrent changes of the same records by different transactions ![]() ![]() We can distinguish the following SAP HANA lock types: Maximum threads waiting for locks (last day)Ĭoncurrently blocked transactions (last hour)Ĭoncurrently blocked transactions (last day) SelfWatchDog activity time (%, last hour)Īge of oldest active trans. SQL: "HANA_Configuration_MiniChecks" (SAP Notes 1969700, 1999993) returns a potentially critical issue (C = 'X') for one of the following individual checks: Identifies long-running blocking situations.ĭetermines the percentage of transactions that are blocked. 'transaction' section of the indexserver.ini file is between 100,000 and ![]() The following SAP HANA alerts indicate problems in the locking area:ĭetermines whether the 'lock_wait_timeout' parameter in the SQL error 146: resource busy and NOWAIT specified SQL error 133: transaction rolled back by detected deadlock SQL error 131: transaction rolled back by lock wait timeout You suffer from terminations and short dumps due to SQL errors 131 or 133: Which indications exist for SAP HANA locking issues? The SAP HANA Troubleshooting and Performance Analysis Guide at SAP HANA Troubleshooting and Performance Analysis Guide contains further information related to lock analysis.Ģ. SAP Note 1858357 provides an overview how to analyze lock situations. Where do I find information about SAP HANA lock analysis?
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |