阻塞(block)過程模擬與分析！

鎖的一點分析、總結和理解。[@more@]

session 1:
SQL> select * from tt;

ID NAME AGE
---------- ---------- ----------
1 c 30
2 b

SQL> select distinct sid from v$mystat;

SID
----------
159

SQL> update tt set name='a' where id=1;

已更新 1 行。

SQL>
--============================
session 2:
SQL> select distinct sid from v$mystat;

SID
----------
142

SQL> update tt set age=30 where id=1;
等待
--==================================
session 3:(監控session)
SQL> set linesize 200
--先確定阻塞的session和被阻塞的session，由欄位BLOCK可知session 159阻塞了session 142
SQL> select * from v$lock where block=1 or request<>0
2 ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C4343B4 6C4343C8 142 TX 1310753 18 0 6 132 0
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 147 1

SQL>
--再根據上面知道的sid確定update操作所需要在表上以及行上加的lock type(TM,TX),當然最終阻塞是發生在row上：
SQL> select * from v$lock where sid in (142,159) order by sid;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6B9C4D08 6B9C4D20 142 TM 10247 0 3 0 222 0
6C4343B4 6C4343C8 142 TX 1310753 18 0 6 222 0
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 237 1
6B9C4C44 6B9C4C5C 159 TM 10247 0 3 0 237 0

SQL>
SQL> select * from dba_locks where session_id in (142,159) order by session_id;

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------- --------------- ---------- ---------- ---------- ---------- ------------ ----------------------------------------
142 DML Row-X (SX) None 10247 0 4176 Not Blocking
142 Transaction None Exclusive 1310753 18 4176 Not Blocking
159 Transaction Exclusive None 1310753 18 4191 Blocking
159 DML Row-X (SX) None 10247 0 4191 Not Blocking

SQL>
--TM,TX的含義,TM鎖是由DML操作引起的加在表上的鎖，TX是由trasaction引起的夾在row上的鎖：
SQL> select * from v$lock_type where type in ('TM','TX');

TYP NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
--- --------------- --------------- --------------- --- ----------------------------------------------------------------------
TM DML object # table/partition YES Synchronizes accesses to an object
TX Transaction usn<<16 | slot sequence YES Lock held by a transaction to allow other transactions to wait for it

SQL>
--TM鎖對應的ID1表示被鎖定的物件object_id：
SQL> select object_id,object_name from dba_objects where object_id=10247;

OBJECT_ID OBJECT_NAM
---------- ----------
10247 TT

SQL>
--TX鎖對應的ID2=1310753的含義，ID2是由4個位元組組成的，高位2個位元組和低位2個位元組分別表示v$trasaction中的xidusn,xidslot：
SQL> select xidusn,xidslot from v$transaction;

XIDUSN XIDSLOT
---------- ----------
20 33

SQL> select to_char(1310753,'xxxxxxx') from dual;

TO_CHAR(
--------
140021

SQL>
--其中高位位元組(00)14轉為10進位制數是20，低位位元組0021轉為10進位制數是33；另外我們
也發現這2個session引起的鎖TX鎖的ID1和ID2是相同的,也就是說最終這2個session裡
執行的update操作產生的事務id是相同的，在db裡同一時刻我想xid必須是唯一的吧，所以
這個xid到底是阻塞的原因還是結果呢？
SQL> select id1,id2 from v$lock where sid in (142,159) and type='TX';

ID1 ID2
---------- ----------
1310753 18
1310753 18

SQL> select xid from v$transaction;

XID
----------------
1400210012000000

SQL>
--這裡的14對應的就是v$transaction中XIDUSN,0021對應的就是XIDSLOT,0012對應的就是XIDSQN
--================================
--從session中獲得更為詳細的和阻塞相關的session以及rowid資訊：
SQL> select sid,taddr,lockwait,status,sql_id,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,blocking_session_status,blocking_instance b_in
,blocking_session b_se from v$session where sid in (142,159);

SID TADDR LOCKWAIT STATUS SQL_ID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_SE B_IN B_SE
---------- -------- -------- -------- ------------- ------------- -------------- --------------- ------------- ----------- ---------- ----------
142 6BA36C5C 6C4343C8 ACTIVE 9zr2gjp8darff 10247 5 2064 0 VALID 1 159
159 6BA3C518 INACTIVE 10247 5 2060 0 NO HOLDER

SQL>
--從上面查詢更加清楚的知道阻塞session 159位於instance 1上，正在被阻塞的session是142，
同時我們也知道被阻塞session當前執行的sql_id是9zr2gjp8darff，從而知道對應的sql是：
SQL> select sql_text from v$sql where sql_id='9zr2gjp8darff';

SQL_TEXT
----------------------------------------
update tt set age=30 where id=1

SQL>
--當然根據欄位row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#我們也知道阻塞到底是發生在哪一行上(透過計算rowid)：
SQL> select dbms_rowid.rowid_create(1,10247,5,2064,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAACgHAAFAAAAgQAAA

SQL> select id,name,rowid from tt where rowid='AAACgHAAFAAAAgQAAA';

ID NAME ROWID
---------- --------------- ------------------
1 c AAACgHAAFAAAAgQAAA

SQL>
--================================
--事務資訊：
SQL> select addr,xidusn,xidslot,xidsqn,status,ses_addr,xid from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN STATUS SES_ADDR XID
-------- ---------- ---------- ---------- ---------------- -------- ----------------
6BA3C518 20 33 18 ACTIVE 6CF3F284 1400210012000000

SQL> select * from v$transaction_enqueue;

ADDR KADDR SID TYP ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- --- ---------- ---------- ---------- ---------- ---------- ----------
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 2859 1

SQL>