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
相關文章
- Java 種15種鎖的介紹:公平鎖,可重入鎖,獨享鎖,互斥鎖等等Java
- Java 種15種鎖的介紹:公平鎖,可重入鎖,獨享鎖,互斥鎖等等...Java
- 【go】golang中鎖的用法-互斥鎖Golang
- Python中的互斥鎖Python
- 互斥鎖mutex的簡單實現Mutex
- Java 中15種鎖的介紹:公平鎖,可重入鎖,獨享鎖,互斥鎖,樂觀鎖,分段鎖,自旋鎖等等Java
- C++中的各種鎖C++
- Java 各種鎖的小結Java
- 讀寫鎖 ReentrantReadWriteLock 與 互斥鎖 的效率
- 執行緒的互斥鎖執行緒
- C 語言的 互斥鎖、自旋鎖、原子操作
- 執行緒安全: 互斥鎖和自旋鎖(10種)執行緒
- PostgreSQL的四種程式間鎖SQL
- Go語言的互斥鎖MutexGoMutex
- sqlserver鎖表的六種語法及with nolock的說明SQLServer
- 透過互斥鎖+條件量的方式實現同步與互斥
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用ENQ索引
- iOS 中常見的幾種鎖-程式碼示例iOS
- 8分鐘搞懂Java中的各種鎖Java
- MySQL 當中的各種鎖(中級篇)MySql
- Go語言中的互斥鎖和讀寫鎖(Mutex和RWMutex)GoMutex
- 詳解 MySql InnoDB 中的三種行鎖(記錄鎖、間隙鎖與臨鍵鎖)MySql
- Mysql各種鎖機制MySql
- liteos互斥鎖(七)
- 悲觀的併發策略——synchronized互斥鎖synchronized
- 一文弄懂分散式場景中各種鎖的原理及使用分散式
- InnoDB常用鎖總結(行鎖、間隙鎖、臨鍵鎖、表鎖)
- 面試官:你說說互斥鎖、自旋鎖、讀寫鎖、悲觀鎖、樂觀鎖的應用場景面試
- Gil全域性解釋鎖和執行緒互斥鎖的關係執行緒
- 執行緒同步與互斥:互斥鎖執行緒
- MySQL 共享鎖 (lock in share mode),排他鎖 (for update)MySql
- Linux多執行緒的使用一:互斥鎖Linux執行緒
- Go 語言的原子操作和互斥鎖的區別Go
- 實戰:併發轉賬業務中避免死鎖的各種方法
- 檢視oracle被鎖的表是誰鎖的Oracle
- java中的鎖及實現原理Java
- 分散式鎖的實現及原理分散式
- MySQL行鎖、表鎖、間隙鎖,你都瞭解嗎MySql
- Mysql中的鎖機制——MyISAM表鎖MySql