0

I am reposting my question from SAP SCN, where it was not blessed with an answer.

I am trying to write a query or script in HANA that shall provice detailed information about the current locks in the system, including the SQL statement that has acquired the lock. Here is what I have so far:

SELECT
    BT.BLOCKED_CONNECTION_ID                AS "Blocked session",
    TR.CONNECTION_ID                        AS "Blocking session",
    BT.BLOCKED_TIME                         AS "Start",
    SECONDS_BETWEEN(BT.BLOCKED_TIME, NOW()) AS "Length",
    CN.CURRENT_SCHEMA_NAME                  AS "Schema",
    CN.CLIENT_HOST || ' ' ||
      CAST(CN.CLIENT_PID AS VARCHAR(5))     AS "Host/PID",    
    SC_AP.VALUE                             AS "Program",
    SC_AS.VALUE                             AS "Program source",
    BT.TABLE_NAME                           AS "Lock object",
    BT.LOCK_OWNER_UPDATE_TRANSACTION_ID,
    PS.STATEMENT_STRING                     AS "Locking statement"
FROM SYS.M_BLOCKED_TRANSACTIONS_ BT
JOIN SYS.M_TRANSACTIONS_ TR ON
    TR.HOST                  = BT.HOST AND
    TR.PORT                  = BT.PORT AND
    TR.UPDATE_TRANSACTION_ID = BT.LOCK_OWNER_UPDATE_TRANSACTION_ID    
JOIN SYS.M_CONNECTIONS CN ON
    CN.HOST          = TR.HOST AND
    CN.PORT          = TR.PORT AND
    CN.CONNECTION_ID = TR.CONNECTION_ID
JOIN M_SESSION_CONTEXT SC_AP ON
    SC_AP.HOST          = TR.HOST          AND
    SC_AP.PORT          = TR.PORT          AND
    SC_AP.CONNECTION_ID = TR.CONNECTION_ID AND
    SC_AP.KEY           = 'APPLICATION'    
JOIN M_SESSION_CONTEXT SC_AS ON
    SC_AS.HOST          = TR.HOST          AND
    SC_AS.PORT          = TR.PORT          AND
    SC_AS.CONNECTION_ID = TR.CONNECTION_ID AND
    SC_AS.KEY           = 'APPLICATIONSOURCE'
LEFT JOIN M_PREPARED_STATEMENTS PS ON
    PS.CONNECTION_ID = TR.CONNECTION_ID AND
    -- Doesn't work, because TR.CURRENT_STATEMENT_ID IS NULL:
    PS.STATEMENT_ID  = TR.CURRENT_STATEMENT_ID

This query displays everyting correctly except the most important field—the locking statement, which I cannot retrieve because

M_TRANSACTIONS_.CURRENT_STATEMENT_ID IS NULL

for the transaction holding the lock. How can I extract the text of the statement that has acquired the lock? The version of our HANA is 1.00.122.05.

In connexion with this question, I have another, about HANA documentation. The only documentation for these system views and tables that I have found is SAP HANA SQL and System Views Reference, but it only lists the fields of views and tables with nary an attempt to describe them and their interrelations. Where is the actual documentation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.