Purpose
Helps administrators identify root cause of a blocked session. User needs to run the following command with the blocked session id:
localhost> localhost> session blocking <session id>
The command not only lists the sessions blocking the current session but also provide an insightful explanation for each - including what locks were acquired which led given session to be blocked.
Syntax
SESSION BLOCKING <sessionid> ;Example
Queries Executed
Session 1000: bt; locking table p1 for write; Session 1001: bt; locking table p2 for read; Session 1002: bt; insert into p2 select * from p1; et; Session 1003: ins p1(1);
Session Listing
localhost>
localhost> session list;
---------------------------------------------
Session Number : 1000
Client TDP Session Number : 1006
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4160 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:54:49
Idle Time (in seconds) : 47
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (in transactions)
Session in transaction : 46 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11633 (Active)
[2] pe-tdvm-smp-0397 - 9242 (Standby)
Locking details:
Lock granted txn scope table write lock on 'kiran.p1' [1001]
---------------------------------------------
Session Number : 1001
Client TDP Session Number : 1007
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4193 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:54:59
Idle Time (in seconds) : 38
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (in transactions)
Session in transaction : 37 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11634 (Active)
[2] pe-tdvm-smp-0397 - 9243 (Standby)
Locking details:
Lock granted txn scope table read lock on 'kiran.p2' [1002]
---------------------------------------------
Session Number : 1002
Client TDP Session Number : 1008
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4665 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:55:06
Idle Time (in seconds) : 27
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (waiting)
Session in transaction : 26 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11635 (Active)
[2] pe-tdvm-smp-0397 - 9244 (Standby)
Locking details:
Lock pending txn scope table read lock on 'kiran.p1' [1001]
Lock pending txn scope table write lock on 'kiran.p2' [1002]
---------------------------------------------
Session Number : 1003
Client TDP Session Number : 1009
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4696 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:55:17
Idle Time (in seconds) : 16
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (waiting)
Session in transaction : 15 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11636 (Active)
[2] pe-tdvm-smp-0397 - 9245 (Standby)
Locking details:
Lock pending txn scope table write lock on 'kiran.p1' [1001]
Blocking SessionIdentification
This is an example of BCM responding with two sessions that were blocking session 1003. It also provides an explain to show how locks were cascaded leading to this blockage.
localhost>
localhost> session blocking 1003;
---------------------------------------------
Session Number : 1000
Client TDP Session Number : 1006
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4160 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:54:49
Idle Time (in seconds) : 54
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (in transactions)
Session in transaction : 54 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11633 (Active)
[2] pe-tdvm-smp-0397 - 9242 (Standby)
Explain:
1003 [kiran.p1] W -> 1000
---------------------------------------------
Session Number : 1001
Client TDP Session Number : 1007
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 4193 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 04/06 23:54:59
Idle Time (in seconds) : 45
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (in transactions)
Session in transaction : 45 sec
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398, [2] pe-tdvm-smp-0397
System Sessions:
[1] bcmn-tdvm-smp-0398 - 11634 (Active)
[2] pe-tdvm-smp-0397 - 9243 (Standby)
Explain:
1003 [kiran.p1] W -> 1002 [kiran.p2] W -> 1001
---------------------------------------------
localhost>
Understanding Explain
Following is the structure of explain output:
SESSION_X [database.tablename] LOCK_TYPE -> SESSION_Y -> ... Where, SESSION_X -> Blocked session [database.tablename] -> Name of database and tablename LOCK_TYPE -> Type of lock requested SESSION_Y -> Blocking session with locks or called dibs via txn id
Termination of a Session
Admins can terminate the blocking sessions by using the following existing command:
localhost> localhost> session kill 1000; localhost> localhost> session kill 1001;