TM表鎖各種mode的實驗及2-6 的TM鎖相互間的互斥示例
TM 表鎖，發生在insert,update,delete以及select for update操作時，目的是保證操作能夠正常進行，並且阻止其它人對錶執行DDL操作。
TM鎖幾種模式的互斥關係圖:
在巡檢期間，檢查如果發現有相關的session_wait鎖，可按以下步驟查詢相應的鎖
1. 使用V$LOCK找出session持有的鎖。
2. 使用V$SESSION找出持有鎖或等待鎖的session執行的sql語句。
3. 使用V$SESSION_WAIT找出什麼原因導致session持有鎖堵塞。
4. 使用V$SESSION獲取關於持有鎖的程式和使用者的更多資訊。
V$LOCK中的常用列
SID：表示持有鎖的會話資訊。
TYPE：表示鎖的型別。值包括TM和TX等。
LMODE：表示會話等待的鎖模式的資訊。用數字0－6表示，和表1相對應。
REQUEST：表示session請求的鎖模式的資訊。
ID1,ID2：表示鎖的物件標識。
例如：首先查出當前TM','TX等待事件的等待會話
BYS@ bys3>select a.sid,a.kaddr,a.addr,a.type,a.id1,a.id2,a.lmode,a.request,a.block from v$lock a where a.type in('TM','TX') order by 1,2;
SID KADDR ADDR TY ID1 ID2 LMODE REQUEST BLOCK
---------- -------- -------- -- ---------- ---------- ---------- ---------- ----------
1 B693D590 B693D560 TM 22326 0 0 3 0
44 B693D590 B693D560 TM 22326 0 5 0
BYS@ bys3>select sql_id from v$session where sid=1; 通過上一步會話SID，查出1號會話當前在等待的語句
SQL_ID
-------------
01mv5v746cfbq
BYS@ bys3>select * from v$sqltext where sql_id='01mv5v746cfbq'; --通過上一步查出SQL_SID，查出SQL_TEXT
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ------------------------------
25DDD5FC 3362142582 01mv5v746cfbq 6 0
update test set status=8899
用一條語句來查詢是：select a.*,b.sid,b.username from v$sqltext a,v$session b,v$lock c where a.sql_id=b.sql_id and b.sid=c.sid and c.type in('TM','TX');
詳見：v$session/v$process檢視涉及的相關會話資訊的查詢
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>insert into test values(1);
1 row created.
BYS@ bys001>commit;
Commit complete.
BYS@ bys001>select * from test;
A
----------
1
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
no rows selected
TM鎖1號鎖實驗：
create table order_info(customerid number);
CREATE OR REPLACE PROCEDURE kzcz214001 (cust_id in NUMBER)
AS
BEGIN
insert into order_info values(cust_id);
commit;
END;
/
col owner for a10
col name for a10
col type for a10
select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
這一句查詢dba_ddl_locks表，如果手工建庫，dba_ddl_locks表可能不存在，需要SYS使用者執行指令碼去建立：?/rdbms/admin/catblock.sql
exec kzcz214001(1);
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ---------- ---------- --------- ---------
1 BYS KZCZ214001 Table/Proc Null None
edure/Type
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
VALID
BYS@ bys3>alter table order_info add(name varchar2(10));
Table altered.
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
INVALID
索引失效後，1號鎖還能查到。。。
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ---------- ---------- --------- ---------
1 BYS KZCZ214001 Table/Proc Null None
edure/Type
等級共享鎖 2 row share----rs
產生2號TM鎖方法1：--手動鎖定
把在19會話將test表設定為等級共享鎖模式
BYS@ bys001>lock table test in row share mode;Table(s) Locked.
可以查詢到是在test表加了個模式2的鎖
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
BYS@ bys001>select object_name from dba_objects where object_id=77389;
OBJECT_NAME
-------------------------------
TEST
在另一個會話147上做DML操作：
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>insert into test values(2);
1 row created.
BYS@ bys001>delete from test where a=1;
1 row deleted.
BYS@ bys001>select * from test;
A
----------
2
BYS@ bys001>update test set a=22 where a=2;
1 row updated.
BYS@ bys001>select * from test;
A
----------
22
在會話19上查詢：
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
147 TM 77389 0 3 0 0
147 TX 196621 28393 6 0 0
如果使用DML語句，會報錯：ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
產生2號TM鎖方法2：--大表線上建索引
在會話1：--在大表上線上建立索引，要用ONLINE，不用ONLINE不會有2號鎖。
BYS@ bys3>create index idx_test1 on test1(id) online;
Index created.
會話2：--有4號鎖和2號鎖
BYS@ bys3>select * from v$lock where sid=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2AD66694 2AD666C0 1 AE 100 0 4 0 2154 0
2AD6697C 2AD669A8 1 DL 22325 0 3 0 4 0
2AD66AF0 2AD66B1C 1 OD 22325 0 4 0 4 0
2AD66C64 2AD66C90 1 DL 22325 0 3 0 4 0
B6A0DB98 B6A0DBC8 1 TM 22325 0 2 0 4 0
B6A0DB98 B6A0DBC8 1 TM 22354 0 4 0 4 0
2926D618 2926D690 1 TS 4 16777362 6 0 2 0
28471AE4 28471B24 1 TX 327696 2557 6 0
BYS@ bys3>col object_name for a20
BYS@ bys3>select a.sid,a.type,a.lmode,a.request,b.object_name from v$lock a,dba_objects b where a.id1=b.object_id and sid=1;
SID TY LMODE REQUEST OBJECT_NAME
---------- -- ---------- ---------- --------------------
1 TM 2 0 TEST1
1 TM 4 0 SYS_JOURNAL_22360
1 AE 4 0 ORA$BASE
1 OD 4 0 TEST1
1 DL 3 0 TEST1
1 DL 3 0 TEST1
################################################################################
等級排他鎖 row exclusive table lock---RX 3
BYS@ bys001>select distinct sid from v$mystat;SID
----------
19
BYS@ bys001>lock table test in row exclusive mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
3級鎖是一個靈活性比較大的鎖，insert delete update，for update 都可以產生一個3級鎖，也允許其他事務來修改鎖定的表
BYS@ bys001>select * from test;
A
----------
22
BYS@ bys001>update test set a=99 where a=22;
1 row updated.
BYS@ bys001>select * from test;
A
----------
99
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>insert into test values(55);
1 row created.
BYS@ bys001>select * from test;
A
----------
22
55
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
147 TM 77389 0 3 0 0
147 TX 589824 28423 6 0 0
block列全部是0,沒有阻塞。此時，如執行DDL語句，報錯：ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired
小結：行級排他鎖，通常已經有事務在修改行或者select…for update 修改結果集。允許其他事務對鎖定的表進行select insert update delete 或 lock table 同時鎖定一張表。
#####################################################################################
共享鎖 share table lock 4
BYS@ bys001>select distinct sid from v$mystat;SID
----------
19
BYS@ bys001>select * from test;
A
----------
99
55
BYS@ bys001>lock table test in row share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
模式標識：4
4級鎖禁止其他會話對鎖定的表進行DML操作但可以select查詢，還允許多個事物一起持有
在會話147上進行刪除操作，
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>DELETE TEST ;
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 1
147 TM 77389 0 3 0 0
147 TX 655390 28214 0 6 0
BYS@ bys001>lock table test in share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 0
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 1
147 TM 77389 0 0 3 0
共享鎖，其它事務可以查詢鎖定的表但不能修改，只允許當前事務修改，但可以多個事務持有它。
另一個產生4號鎖的，見： --2號鎖線上建索引實驗。
##########################################################TM鎖5號鎖實驗：
BYS@ bys001>lock table test in share row exclusive mode;
Table(s) Locked.BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
BYS@ bys001>insert into test values(88);
1 row created.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
19 TX 524320 28676 6 0 0
在147會話執行DML操作會hang住
BYS@ bys001>insert into test values(88);
此時查詢
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 1
19 TX 524320 28676 6 0 0
147 TM 77389 0 0 3 0
小結：共享行級排他鎖，同一時間只允許一個事務持有和修改鎖定的表，其他事務可以查詢但不能修改。
5號鎖--在儲存過程執行中會出現：A B兩個會話同時執行同一個儲存過程時
session B exec p
#############################################
TM鎖6號鎖實驗：
BYS@ bys001>lock table test in exclusive mode;
Table(s) Locked.BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 6 0 0
在會話147進行hang住
BYS@ bys001>lock table test in row share mode; ---在此HANG住
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 6 0 1
147 TM 77389 0 0 2 0
相關文章
- Oracle LOCK內部機制及最佳實踐系列(四)深入分析mode 2-6 的TM鎖相互間的互斥關係Oracle
- oracle的TM鎖、TX鎖Oracle
- oracle的TM鎖、TX鎖知識完全普及Oracle
- 對TM鎖和TX鎖的簡單理解
- 並行dml操作所需的TM鎖並行
- TM LOCK MODE
- APPEND時的TM鎖定級別APP
- enq: TM - contention TM 等待事件的原因及模擬ENQ事件
- Java 種15種鎖的介紹：公平鎖，可重入鎖，獨享鎖，互斥鎖等等Java
- Java 種15種鎖的介紹：公平鎖，可重入鎖，獨享鎖，互斥鎖等等...Java
- oracle deadlock with TM lock in SX/SSX modeOracle
- iOS 中的各種鎖iOS
- 【go】golang中鎖的用法-互斥鎖Golang
- Java 中15種鎖的介紹：公平鎖，可重入鎖，獨享鎖，互斥鎖，樂觀鎖，分段鎖，自旋鎖等等Java
- 互斥鎖mutex的簡單實現Mutex
- Java 各種鎖的小結Java
- Python中的互斥鎖Python
- C 語言的 互斥鎖、自旋鎖、原子操作
- 執行緒安全: 互斥鎖和自旋鎖(10種)執行緒
- Go語言的互斥鎖MutexGoMutex
- PostgreSQL的四種程式間鎖SQL
- sqlserver鎖表的六種語法及with nolock的說明SQLServer
- Linux下關於互斥鎖及同步的移植（一）Linux
- Linux下關於互斥鎖及同步的移植（二）Linux
- 讀寫鎖 ReentrantReadWriteLock 與 互斥鎖 的效率
- iOS 中常見的幾種鎖-程式碼示例iOS
- 舉例講解 Python 中的死鎖、可重入鎖和互斥鎖Python
- 特定的閂鎖和互斥場景
- MySQL 當中的各種鎖（中級篇）MySql
- 8分鐘搞懂Java中的各種鎖Java
- Go語言中的互斥鎖和讀寫鎖（Mutex和RWMutex）GoMutex
- Linux下關於互斥鎖及同步的移植（一）薦Linux
- mysql innodb間隙鎖示例MySql
- liteos互斥鎖（七）
- Mysql各種鎖機制MySql
- 詳解 MySql InnoDB 中的三種行鎖（記錄鎖、間隙鎖與臨鍵鎖）MySql
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用ENQ索引
- 面試官：你說說互斥鎖、自旋鎖、讀寫鎖、悲觀鎖、樂觀鎖的應用場景面試