## 3.13.1.1  Using Scripts to Analyze Blocked Transactions
You can use scripts to analyze the issues underlying blocked transactions. Modify the time range analyzed by the script, then use it to identify the lock owner, get information on the lock wait hierarchy, or analyze thread activity on the database at the time of the block.
## Identify the Lock Owner: Transactional Lock Waits Hierarchy Script
The HANA_Locks_Transactional_LockWaits_Hierarchy_* script can help you to gather information on current and historic lock wait situations, including the lock owner and the lock wait hierarchy. The asterisk (*) in the script title is a placeholder for the SAP HANA release and revision.
## Modify a Script
Where relevant, the SQL support scripts contain sections which can be customized to enter your own parameter values, these are marked with the comment ' /* Modification section */ '. For example:
```
( SELECT               /* Modification section */ '%' HOST, '%' PORT, '%' OBJECT_TYPE, ... )
```
If you want to analyze a specific time range, you can modify the time parameters using the codes described in the comment block of the script, in the section INPUT_PARAMETERS . Y ou can use 'C-H12' BEGIN_TIME , or you can enter a specific date and time.
To do this, in place of 'C-H12' BEGIN_TIME and 'C' END_TIME , enter the date and time you want to analyze, in the format 'YYY/MM/DD HH24:MI:SS' .
Example: To check for transaction lock waits that happened between 19/08/2022 at 2pm and 20/08/2022 at 2pm, the modification part of the script would look like this:
```
Modification section 2022/08/19 BEGIN_TIME , 2022/08/20 14;00:00 SERVER, UTC 1 SITE_Id, Port
```
You can then execute the script and check for transaction lock waits in your system for the relevant date and time.
The output parameters are explained in the comment block of the script. Key columns include:
| Output Columns   | Explanation                                                                      |
|------------------|----------------------------------------------------------------------------------|
| WAIT_S           | Seconds since the lock wait started                                              |
| LT               | Lock type ('R'=RECORD, 'T'=TABLE, 'M'=METADATA)                                  |
| LM               | Lock mode ('E'=EXCLUSIVE, 'IE'=INTENTIONAL EXCLUSIVE, 'S'=SHARED)                |
| A                | Lock holder ('X'=lock holder on SAP HANAside, ' '= lock holder outside SAP HANA) |
| OBJECT_NAME      | Object name related to the lock wait                                             |
| STATEMENT_HASH   | Statement hash related to the lock wait                                          |
| CONN_ID          | Connection ID (Indent indicates hierarchy)                                       |
As a starting point for your analysis, refer to the value for the Column 'A' to identify the lock owner. The steps you take to troubleshoot the block depend on whether the issue is within the SAP HANA Database or caused by factors external to the database.
## If the issue is outside of the SAP HANA Database
If the value for Column 'A' in the script output is blank (' '), the lock owner is outside the SAP HANA Database. Consider checking the application and the network.
- Check the application: The application associated with the lock is named in the column 'APPLICATION SOURCE'. You can search for known application lock issues in KBA 2000002 , FAQ: SAP HANA SQL Optimization, in particular Question 13, "Are there standard recommendations for specific SQL statements available?" Search the section using the application name, the OBJECT_NAME (this is usually a table name), and the STATEMENT_HASH related to the lock.
- If you don't find any issues on the application side, the next step would be to check the network between the application client and the SAP HANA Database. See KBA 2081065 , T roubleshooting SAP HANA Network, especially Section 1, "Analyze Round Trip Time (RTT) Between Server and Client". You can also use SAP's NIPING program to analyze the network connection between any two machines running SAP software: see SAP Note 500235 , Network Diagnosis with NIPING.
## If the lock owner is active on the SAP HANA Database side
If the value in Column 'A' in the script output is 'X', the lock owner is active on the SAP HANA Database side and further analysis is required on the database side. You can check the HANA thread samples to see what the HANA threads are doing at the time of the block.
## Analyze Thread Activity: Threads Filter and Aggregation Script
You can use the script HANA_Threads_ThreadSamples_FilterAndAggregation_* to see what the HANA threads are doing on the database during a block.
First you need to modify the time range of your analysis, as described above.
Next you can define the level of detail you want in the script output. Use the parameter AGGREGATE_BY in the 'Modification section' of the script. For example, the 'AGGREGATE_BY' value 'THREAD_METHOD, THREAD_DETAIL' specifies that the script will be aggregated by thread method and thread detail. If you want to see more detailed information in the script output you can remove this aggregation detail by replacing 'THREAD_METHOD, THREAD_DETAIL' with 'NONE'.
In the script output, you get important information from the values in the columns 'THREAD_STATE' and 'LOCK_NAME' for the time that you have the lock wait issue.
## Further Information
- The collection of support scripts is provided as an attachment to 1969700, SQL Statement Collection for SAP HANA. See the "Solution" section for further information on how support scripts can be modified and for known issues.
- See also KBA 1999998 , FAQ:SAP HANA Lock Analysis, in particular Question 5, "How can transactional lock waits be analyzed and optimized?"
- For more information on HANA thread samples, and to assess whether thread samples are involved in the lock situation, follow the steps outlined in see KBA 2114710 , FAQ: SAP HANA Threads and Thread Samples.
- If you notice a high number of transactional (record and object) lock waits at the same time as many semaphore and mutex waits, then it's likely that the semaphore and mutex waits are causing the transactional locks. See KBA 1999998 , FAQ:SAP HANA Lock Analysis, in particular Question 6, "How can internal lock waits be analyzed?"
- For known application lock contention issues, see KBA 2000002 , FAQ: SAP HANA SQL Optimization, especially Question 13, "Are there standard recommendations for specific SQL statements available?"