鎖讀書筆記
一、基本概念
1、丟失更新
在沒有鎖的情況下，兩個人在基本同一時間對資料庫的某行做修改。後來修改的會話會覆蓋掉之前修改的會話的結果。
2、阻塞
一個會話保持另一個會話正在請求的資源上的鎖定時，就發生阻塞。阻塞分為插入阻塞、更新和刪除阻塞。
避免發生阻塞的方法是使用序列或者DBMS_LOCK包提供的方法手工鎖定避免此情況。
更新阻塞可採用select from upadate nowait來驗證沒有更新的行。
3、死鎖
A會話：Update test Set test.col1 = 36 不提交
B會話：Update test1 set test1.col1=48 不提交
B會話：update test set test.col1=48 不提交
A會話：Update test1 Set test1.col1 = 96
然後在B會話會提示：
ORA-00060：等待資源時檢測到死鎖
檢查沒有索引的外來鍵：
column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/
在plsql中執行該指令碼報錯。提示ORA-00979：不是group by表示式，儘管報錯了，但是測試繼續。
二、鎖定型別
1 DML鎖定
1.1 TX鎖定
TX用於保證在一段時間只有一個使用者進行修改，並且別人不可一刪除正在使用的表。
測試如下：
在會話A執行
Update test Set col1 = 118 Where col1 = 18
不提交
在當前會話執行
Update test Set col1 = 128 Where col1 = 18
提示0行被更新
在B會話執行
Update test Set col1 = 128 Where col1 = 18
該執行掛起，等待A會話的執行確認或者回滾之後才能正常執行。
在A會話執行：
alter table TEST drop column COL1;
提示：ORA-00054: 資源正忙，要求指定 NOWAIT
執行：
select username,v$lock.TYPE,
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
/
v$session展示登陸的會話
v$lock包含所有被持有的鎖的入口，也包含正在等待鎖定的會話的入口
結果如下：
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST TX 13 7 31 250 0 6
TEST TX 14 7 31 250 6 0
執行指令碼：
select XIDUSN, XIDSLOT, XIDSQN
from v$transaction
/
v$transaction展示了每個活動事物的入口
結果如下：
select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
7 31 250
繼續測試，回滾A會話的修改，回滾B會話的修改，關閉B會話
執行：
select username,v$lock.TYPE,
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
/
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
SQL>
此時鎖定消失。
在會話A執行
Update test Set col1 = 118 Where col1 = 18
不提交
執行：
select username,v$lock.TYPE,
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
/
結果：
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST TX 14 3 17 329 6 0
結論：oracle給每個會話開啟一個鎖定
1.2 TM鎖定
這些鎖定用來保證在更改表的內容時，表的結構不會被更改。
分別執行下面的語句，
Insert Into test Values(1,2)
Insert Into test1 Values (2,3)
不提交
執行：
select username,
v$lock.sid,
id1, id2,
lmode,
request, block, v$lock.type
from v$lock, v$session
where v$lock.sid = v$session.sid
and v$session.username = USER
/
結果如下：
SQL> select username,
2 v$lock.sid,
3 id1, id2,
4 lmode,
5 request, block, v$lock.type
6 from v$lock, v$session
7 where v$lock.sid = v$session.sid
8 and v$session.username = USER
9 /
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TYPE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----
TEST 14 30914 0 3 0 0 TM
TEST 14 65558 265 6 0 0 TX
TEST 14 31267 0 3 0 0 TM
SQL>
結論：每個事務中只獲取一個TX鎖，但是可以獲取與所修改數量一樣多的TM鎖定。此時ID1是DML鎖定物件的物件ID。
TM縮的總數是可配置的DML_LOCKS，如果該引數配置為0，意味著不允許DDL。
2、DDL鎖定
三種DDL鎖定：
2.1 獨佔的DDL鎖定，防止其他會話獲得DDL鎖定或TM鎖定，意味著在此期間，查詢可以進行，任何方式的修改不允許。
2.2 共享的DDL鎖定，保護引用物件的結構，防止其他會話修改，但是允許資料修改
2.3 可以打破的分析鎖定，不能防止DDL發生
多數的DDL使用獨佔的DDL鎖定
下面的語句不使用DDL獨佔鎖
Create Index index_test On test(col1) Online
執行該語句之前執行：
Create Table ddl_lock1 As Select * From dba_ddl_locks
SQL> desc ddl_lock1;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
SESSION_ID NUMBER Y
OWNER VARCHAR2(60) Y
NAME VARCHAR2(60) Y
TYPE VARCHAR2(40) Y
MODE_HELD VARCHAR2(9) Y
MODE_REQUESTED VARCHAR2(9) Y
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30;
SQL> select * from ddl_lock1
2 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
5 XDB DBMS_XDBZ0 Body Null None
5 XDB DBMS_XDBZ Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Body Null None
5 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
5 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
5 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
5 SYS DBMS_STANDARD Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
5 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ0 Body Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DATABASE 18 Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Body Null None
17 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
17 SYS DATABASE 18 Null None
17 TEST TEST 18 Null None
17 SYS DBMS_APPLICATION_INFO Body Null None
28 rows selected
執行該語句之後執行：
Create Table ddl_lock2 As Select * From dba_ddl_locks
利用PL/SQL DEVELOPER工具進行DDL操作，刪除col1欄位，不提交
同時執行語句：
Create Table ddl_lock3 As Select * From dba_ddl_locks
在另外一個會話利用PL/SQL DEVELOPER工具進行DDL操作，修改col1欄位為col3欄位，不提交
執行：
Create Table ddl_lock4 As Select * From dba_ddl_locks
執行：
Select * From ddl_lock2
Minus
Select * From ddl_lock1
結果：
SQL> Select * From ddl_lock2
2 Minus
3 Select * From ddl_lock1
4 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 SYS DATABASE 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_OUTPUT Body Null None
13 SYS DBMS_OUTPUT Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 SYS DBMS_TRANSACTION Body Null None
13 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
13 XDB DBMS_XDBZ0 Body Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
17 TEST DDL_LOCK2 Table/Procedure/Type Exclusive None
執行：
Select * From ddl_lock3
Minus
Select * From ddl_lock2
結果：
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
17 TEST DDL_LOCK3 Table/Procedure/Type Exclusive None
執行：
Select * From ddl_lock4
Minus
Select * From ddl_lock3
結果：
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
17 TEST DDL_LOCK4 Table/Procedure/Type Exclusive None
最終的dba_ddl_locks內容如下：
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30
SQL> Select * From ddl_lock4
2 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
5 XDB DBMS_XDBZ0 Body Null None
5 XDB DBMS_XDBZ Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Body Null None
5 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
5 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
5 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
5 SYS DBMS_STANDARD Table/Procedure/Type Null None
5 XDB DBMS_XDBZ Body Null None
7 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
7 SYS DATABASE 18 Null None
7 TEST TEST 18 Null None
7 SYS DBMS_APPLICATION_INFO Body Null None
12 XDB DBMS_XDBZ0 Body Null None
12 SYS DATABASE 18 Null None
12 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
12 SYS DBMS_STANDARD Table/Procedure/Type Null None
12 TEST TEST 18 Null None
12 SYS DBMS_APPLICATION_INFO Body Null None
12 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
12 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
12 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
12 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
12 SYS DBMS_CDC_PUBLISH Body Null None
12 XDB DBMS_XDBZ Body Null None
12 XDB DBMS_XDBZ Table/Procedure/Type Null None
13 XDB DBMS_XDBZ0 Body Null None
13 SYS DATABASE 18 Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DBMS_OUTPUT Body Null None
13 SYS DBMS_OUTPUT Table/Procedure/Type Null None
13 SYS DBMS_TRANSACTION Body Null None
13 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
17 TEST DDL_LOCK4 Table/Procedure/Type Exclusive None
17 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
17 SYS DBMS_STANDARD Table/Procedure/Type Null None
17 TEST TEST 18 Null None
17 SYS DBMS_APPLICATION_INFO Body Null None
17 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
17 SYS DBMS_OUTPUT Body Null None
17 SYS DBMS_OUTPUT Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Body Null None
17 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
17 SYS DATABASE 18 Null None
17 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
17 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
17 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
17 SYS DBMS_CDC_PUBLISH Body Null None
17 XDB DBMS_XDBZ Body Null None
17 XDB DBMS_XDBZ0 Body Null None
17 XDB DBMS_XDBZ Table/Procedure/Type Null None
61 rows selected
SQL>
其中的含義不是很明白，只瞭解，在每次進行過程中，就加了一個DDL鎖定。
在模擬DDL鎖之前dba_ddl_locks的內容都是什麼含義？
模擬DDL鎖定之後，TEST使用者中的每個鎖定意義是什麼？
DDL鎖釋放之後，什麼時候更新dba_ddl_locks表中的內容，還是說不會更新？
3 鎖存器和內部鎖定(入隊)(latches and internal locks(enqueues))
鎖存器是在極短時間內，例如用於修改記憶體裡資料結構的時間內保持的鎖定，他們用來保護某些記憶體結構。
入隊是另外一個複雜的序列化裝置，例如在更新資料庫表中的行時使用，它們與鎖存器的區別在於他們允許請求者排隊等待資源，使用鎖存器請求，請求者立刻被告知他們是否得到了鎖存器，使用入隊，請求者將被阻塞，直到實際地獲得它為止。它速度不如鎖存器，但是提供的功能在鎖存器功能之上。入隊可以在不同的等級獲得。
上面是概念，但是還是不能完全理解latch 和enquenue，不知道有沒有實踐的例子可以展示。或者論壇上已經有的很細的帖子，給個連線也可以。
1、丟失更新
在沒有鎖的情況下，兩個人在基本同一時間對資料庫的某行做修改。後來修改的會話會覆蓋掉之前修改的會話的結果。
2、阻塞
一個會話保持另一個會話正在請求的資源上的鎖定時，就發生阻塞。阻塞分為插入阻塞、更新和刪除阻塞。
避免發生阻塞的方法是使用序列或者DBMS_LOCK包提供的方法手工鎖定避免此情況。
更新阻塞可採用select from upadate nowait來驗證沒有更新的行。
3、死鎖
A會話：Update test Set test.col1 = 36 不提交
B會話：Update test1 set test1.col1=48 不提交
B會話：update test set test.col1=48 不提交
A會話：Update test1 Set test1.col1 = 96
然後在B會話會提示：
ORA-00060：等待資源時檢測到死鎖
檢查沒有索引的外來鍵：
column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/
在plsql中執行該指令碼報錯。提示ORA-00979：不是group by表示式，儘管報錯了，但是測試繼續。
二、鎖定型別
1 DML鎖定
1.1 TX鎖定
TX用於保證在一段時間只有一個使用者進行修改，並且別人不可一刪除正在使用的表。
測試如下：
在會話A執行
Update test Set col1 = 118 Where col1 = 18
不提交
在當前會話執行
Update test Set col1 = 128 Where col1 = 18
提示0行被更新
在B會話執行
Update test Set col1 = 128 Where col1 = 18
該執行掛起，等待A會話的執行確認或者回滾之後才能正常執行。
在A會話執行：
alter table TEST drop column COL1;
提示：ORA-00054: 資源正忙，要求指定 NOWAIT
執行：
select username,v$lock.TYPE,
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
/
v$session展示登陸的會話
v$lock包含所有被持有的鎖的入口，也包含正在等待鎖定的會話的入口
結果如下：
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST TX 13 7 31 250 0 6
TEST TX 14 7 31 250 6 0
執行指令碼：
select XIDUSN, XIDSLOT, XIDSQN
from v$transaction
/
v$transaction展示了每個活動事物的入口
結果如下：
select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
7 31 250
繼續測試，回滾A會話的修改，回滾B會話的修改，關閉B會話
執行：
select username,v$lock.TYPE,
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
/
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
SQL>
此時鎖定消失。
在會話A執行
Update test Set col1 = 118 Where col1 = 18
不提交
執行：
select username,v$lock.TYPE,
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
/
結果：
SQL> select username,v$lock.TYPE,
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
12 /
USERNAME TYPE SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST TX 14 3 17 329 6 0
結論：oracle給每個會話開啟一個鎖定
1.2 TM鎖定
這些鎖定用來保證在更改表的內容時，表的結構不會被更改。
分別執行下面的語句，
Insert Into test Values(1,2)
Insert Into test1 Values (2,3)
不提交
執行：
select username,
v$lock.sid,
id1, id2,
lmode,
request, block, v$lock.type
from v$lock, v$session
where v$lock.sid = v$session.sid
and v$session.username = USER
/
結果如下：
SQL> select username,
2 v$lock.sid,
3 id1, id2,
4 lmode,
5 request, block, v$lock.type
6 from v$lock, v$session
7 where v$lock.sid = v$session.sid
8 and v$session.username = USER
9 /
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TYPE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----
TEST 14 30914 0 3 0 0 TM
TEST 14 65558 265 6 0 0 TX
TEST 14 31267 0 3 0 0 TM
SQL>
結論：每個事務中只獲取一個TX鎖，但是可以獲取與所修改數量一樣多的TM鎖定。此時ID1是DML鎖定物件的物件ID。
TM縮的總數是可配置的DML_LOCKS，如果該引數配置為0，意味著不允許DDL。
2、DDL鎖定
三種DDL鎖定：
2.1 獨佔的DDL鎖定，防止其他會話獲得DDL鎖定或TM鎖定，意味著在此期間，查詢可以進行，任何方式的修改不允許。
2.2 共享的DDL鎖定，保護引用物件的結構，防止其他會話修改，但是允許資料修改
2.3 可以打破的分析鎖定，不能防止DDL發生
多數的DDL使用獨佔的DDL鎖定
下面的語句不使用DDL獨佔鎖
Create Index index_test On test(col1) Online
執行該語句之前執行：
Create Table ddl_lock1 As Select * From dba_ddl_locks
SQL> desc ddl_lock1;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
SESSION_ID NUMBER Y
OWNER VARCHAR2(60) Y
NAME VARCHAR2(60) Y
TYPE VARCHAR2(40) Y
MODE_HELD VARCHAR2(9) Y
MODE_REQUESTED VARCHAR2(9) Y
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30;
SQL> select * from ddl_lock1
2 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
5 XDB DBMS_XDBZ0 Body Null None
5 XDB DBMS_XDBZ Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Body Null None
5 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
5 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
5 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
5 SYS DBMS_STANDARD Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
5 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ0 Body Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DATABASE 18 Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Body Null None
17 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
17 SYS DATABASE 18 Null None
17 TEST TEST 18 Null None
17 SYS DBMS_APPLICATION_INFO Body Null None
28 rows selected
執行該語句之後執行：
Create Table ddl_lock2 As Select * From dba_ddl_locks
利用PL/SQL DEVELOPER工具進行DDL操作，刪除col1欄位，不提交
同時執行語句：
Create Table ddl_lock3 As Select * From dba_ddl_locks
在另外一個會話利用PL/SQL DEVELOPER工具進行DDL操作，修改col1欄位為col3欄位，不提交
執行：
Create Table ddl_lock4 As Select * From dba_ddl_locks
執行：
Select * From ddl_lock2
Minus
Select * From ddl_lock1
結果：
SQL> Select * From ddl_lock2
2 Minus
3 Select * From ddl_lock1
4 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 SYS DATABASE 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_OUTPUT Body Null None
13 SYS DBMS_OUTPUT Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 SYS DBMS_TRANSACTION Body Null None
13 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
13 XDB DBMS_XDBZ0 Body Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
17 TEST DDL_LOCK2 Table/Procedure/Type Exclusive None
執行：
Select * From ddl_lock3
Minus
Select * From ddl_lock2
結果：
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
17 TEST DDL_LOCK3 Table/Procedure/Type Exclusive None
執行：
Select * From ddl_lock4
Minus
Select * From ddl_lock3
結果：
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
17 TEST DDL_LOCK4 Table/Procedure/Type Exclusive None
最終的dba_ddl_locks內容如下：
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30
SQL> Select * From ddl_lock4
2 /
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
5 XDB DBMS_XDBZ0 Body Null None
5 XDB DBMS_XDBZ Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Body Null None
5 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
5 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
5 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
5 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
5 SYS DBMS_STANDARD Table/Procedure/Type Null None
5 XDB DBMS_XDBZ Body Null None
7 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
7 SYS DATABASE 18 Null None
7 TEST TEST 18 Null None
7 SYS DBMS_APPLICATION_INFO Body Null None
12 XDB DBMS_XDBZ0 Body Null None
12 SYS DATABASE 18 Null None
12 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
12 SYS DBMS_STANDARD Table/Procedure/Type Null None
12 TEST TEST 18 Null None
12 SYS DBMS_APPLICATION_INFO Body Null None
12 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
12 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
12 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
12 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
12 SYS DBMS_CDC_PUBLISH Body Null None
12 XDB DBMS_XDBZ Body Null None
12 XDB DBMS_XDBZ Table/Procedure/Type Null None
13 XDB DBMS_XDBZ0 Body Null None
13 SYS DATABASE 18 Null None
13 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
13 SYS DBMS_STANDARD Table/Procedure/Type Null None
13 TEST TEST 18 Null None
13 SYS DBMS_APPLICATION_INFO Body Null None
13 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
13 SYS DBMS_OUTPUT Body Null None
13 SYS DBMS_OUTPUT Table/Procedure/Type Null None
13 SYS DBMS_TRANSACTION Body Null None
13 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
13 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
13 SYS DBMS_CDC_PUBLISH Body Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
13 XDB DBMS_XDBZ Body Null None
13 XDB DBMS_XDBZ Table/Procedure/Type Null None
17 TEST DDL_LOCK4 Table/Procedure/Type Exclusive None
17 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
17 SYS DBMS_STANDARD Table/Procedure/Type Null None
17 TEST TEST 18 Null None
17 SYS DBMS_APPLICATION_INFO Body Null None
17 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
17 SYS DBMS_OUTPUT Body Null None
17 SYS DBMS_OUTPUT Table/Procedure/Type Null None
17 SYS DBMS_TRANSACTION Body Null None
17 SYS DBMS_TRANSACTION Table/Procedure/Type Null None
17 SYS DATABASE 18 Null None
17 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
17 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
17 SYS DBMS_CDC_PUBLISH Table/Procedure/Type Null None
17 SYS DBMS_CDC_PUBLISH Body Null None
17 XDB DBMS_XDBZ Body Null None
17 XDB DBMS_XDBZ0 Body Null None
17 XDB DBMS_XDBZ Table/Procedure/Type Null None
61 rows selected
SQL>
其中的含義不是很明白，只瞭解，在每次進行過程中，就加了一個DDL鎖定。
在模擬DDL鎖之前dba_ddl_locks的內容都是什麼含義？
模擬DDL鎖定之後，TEST使用者中的每個鎖定意義是什麼？
DDL鎖釋放之後，什麼時候更新dba_ddl_locks表中的內容，還是說不會更新？
3 鎖存器和內部鎖定(入隊)(latches and internal locks(enqueues))
鎖存器是在極短時間內，例如用於修改記憶體裡資料結構的時間內保持的鎖定，他們用來保護某些記憶體結構。
入隊是另外一個複雜的序列化裝置，例如在更新資料庫表中的行時使用，它們與鎖存器的區別在於他們允許請求者排隊等待資源，使用鎖存器請求，請求者立刻被告知他們是否得到了鎖存器，使用入隊，請求者將被阻塞，直到實際地獲得它為止。它速度不如鎖存器，但是提供的功能在鎖存器功能之上。入隊可以在不同的等級獲得。
上面是概念，但是還是不能完全理解latch 和enquenue，不知道有沒有實踐的例子可以展示。或者論壇上已經有的很細的帖子，給個連線也可以。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/79499/viewspace-149750/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql鎖機制 讀書筆記MySql筆記
- 讀書筆記筆記
- 讀書筆記...筆記
- Cucumber讀書筆記筆記
- 散文讀書筆記筆記
- HTTP 讀書筆記HTTP筆記
- CoreJava讀書筆記-------Java筆記
- flask讀書筆記Flask筆記
- Vue讀書筆記Vue筆記
- MONGODB 讀書筆記MongoDB筆記
- Qt讀書筆記QT筆記
- Node讀書筆記筆記
- SAP讀書筆記筆記
- YII讀書筆記筆記
- iptables 讀書筆記筆記
- Makefile 讀書筆記筆記
- mysql讀書筆記MySql筆記
- dataguard讀書筆記筆記
- 《重構》讀書筆記筆記
- webpackDemo讀書筆記Web筆記
- PMBook讀書筆記（一）筆記
- Effective Java 讀書筆記Java筆記
- js高程讀書筆記JS筆記
- “Docker Practice”讀書筆記Docker筆記
- FPGA讀書筆記5FPGA筆記
- FPGA讀書筆記3FPGA筆記
- FPGA讀書筆記4FPGA筆記
- FPGA讀書筆記2FPGA筆記
- FPGA讀書筆記1FPGA筆記
- 《精通JavaScript》讀書筆記JavaScript筆記
- 讀書筆記摘錄：筆記
- 《矽谷之父》讀書筆記筆記
- 重構讀書筆記筆記
- 讀書筆記之一筆記
- Spam Or Ham讀書筆記筆記
- 讀書筆記之五筆記
- 讀書筆記：RUP (轉)筆記
- ajax安全讀書筆記筆記