oracle學習----行級鎖的理解

標籤：style   blog   http   io   ar   color   使用   sp   for   

通過實驗來理解行級鎖的發生

1.建立需要的表

SQL> conn / as sysdba
已串連。
SQL> create table dept as select * from scott.dept;

表已建立。

SQL> create table emp as select * from scott.emp;

表已建立。

SQL> alter table dept add constraint dept_pk primary key(deptno);

表已更改。

SQL> alter table emp add constraint emp_pk primary key(empno);

表已更改。

SQL> alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno);

表已更改。

SQL> create index emp_deptno_idx on emp(deptno);

索引已建立。

2.執行更新操作

SQL> update dept set dname=initcap(dname);

已更新4行。

3.查看此時的系統狀態

SQL> set linesize 200 pagesize 1000
SQL> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff‘,‘xxxx‘))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock,v$session
9 where v$lock.type=‘TX‘
10 and v$lock.sid=v$session.sid
11 and v$session.username=USER;

　　　　　　　　　USERNAME 　　   SID 　　　RBS 　　  SLOT 　　 SEQ     LMODE  REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
　　　　　　　　　　　　SYS 　　　　68 　　　　 3 　　　　 23 　　　 898 　　 　　6 　　　　 0  

通過列表可以看出lmode=6是排它鎖，request=0代表沒有鎖請求，就是說更新了表的資料沒有另一個會話更新同一條資料，就沒有鎖請求

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;查出來的資訊是事務ID可以和查詢出的鎖資訊相對應。

XIDUSN     XIDSLOT  XIDSQN
---------- ---------- ----------
3                        23         898

4.在一個新的會話中登入相同的使用者，執行更新操作

SQL> update emp set ename=upper(ename);

已更新14行。

SQL> update dept set deptno=deptno-10;

5.查看系統資訊

SQL> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff‘,‘xxxx‘))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock,v$session
9 where v$lock.type=‘TX‘
10 and v$lock.sid=v$session.sid
11 and v$session.username=USER;

USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS 130 3 23 898 0 6
SYS 68 3 23 898 6 0 
SYS 130 5 33 872 6 0

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 23 898---第一次
5 33 872---第二次

在事務資訊中多了一條，在鎖的資訊中多了兩條，其中一條請求為獨佔鎖定6，他的rbs/slot/seq與第一次的相同，說明給他加鎖的就是第一次的更新操作，他在請求這個鎖，來獲得資源，在第二個會話中第一條語句由於也沒有提交所以他也出現了排它鎖。

通過鎖的自連接就可以更清楚的看出他們之間誰鎖了誰
SQL> select
2 (select username from v$session where sid=a.sid) blocker,
3 a.sid,
4 ‘is blocking‘,
5 (select username from v$session where sid=b.sid) blockee,
6 b.sid
7 from v$lock a,v$lock b
8 where a.block=1
9 and b.request >0
10 and a.id1=b.id1
11 and a.id2=b.id2;

BLOCKER SID ‘ISBLOCKING BLOCKEE SID
------------------------------ ---------- ----------- ------------------------------ ----------
SYS 68 is blocking SYS 130

6.提交事務，查詢鎖資訊

SQL> commit;

提交完成。

SQL> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff‘,‘xxxx‘))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock,v$session
9 where v$lock.type=‘TX‘
10 and v$lock.sid=v$session.sid
11 and v$session.username=USER;

USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS 130 5 33 872 6 0

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
5 33 872

SID為68的鎖不見了，130的鎖只剩下了一個，有鎖請求的資訊也消失了，這就解釋了oracle的鎖機制，首先oracle的行鎖不會使用鎖隊列管理，在第二個會話中有兩個更新操作沒有提交，如果是隊列管理應該會有兩個鎖資訊，現在只有一個，那在第一個會話沒有提交的時侯出現兩個鎖資訊，是因為第一個會話更新的時候對行加了排它鎖，第二個會話想更新相同的行，就要向會話一發出鎖請求，所以在鎖資訊裡才有它的出現，但是它的出現，他的模式是0這樣就可以理解了。

 

相關 指令碼總結

查詢鎖得相關資訊

查詢當前會話的操作的鎖狀態

select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number(‘ffff‘,‘xxxx‘))+0 slot,
id2 seq,
lmode,
request
from v$lock,v$session
where v$lock.type=‘TX‘
and v$lock.sid=v$session.sid
and v$session.username=USER;

在事務視圖中找到相應的對照資訊

select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

查看鎖的自連接資訊，可以查看誰把誰鎖定了

select
(select username from v$session where sid=a.sid) blocker,
a.sid,
‘is blocking‘,
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a,v$lock b
where a.block=1
and b.request >0
and a.id1=b.id1
and a.id2=b.id2;

 

oracle學習----行級鎖的理解