模擬insert,update和delete造成阻塞的示例
會話1SID
BYS@dg2>select distinct sid from v$mystat;
SID
----------
17
會話2SID
BYS@dg2>select distinct sid from v$mystat;
SID
----------
49
insert造成阻塞的示例
在會話1向表T插入一條資料值1,因為插入資料所在欄位有主鍵約束。
此時會話1不提交或回滾插入操作時,在會話2進行同樣的向表T插入一條資料值1時被髮生阻塞,語句無法執行。
此時再開啟一個會話,可以從v$lock檢視中查詢出有鎖的表及所在會話ID。
會話1:
BYS@dg2>create table t(x number primary key);---新建表,設定主鍵
Table created.
BYS@dg2>insert into t values(1); --插入資料不提交
1 row created.
BYS@dg2>
會話2:此時執行插入相同數值時被阻塞----hang住。
BYS@dg2>insert into t values(1);
如果會話1提交,阻塞中止,此時會話2語句執行並報錯,提示違反了主鍵的一致性約束
BYS@dg2>insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (BYS.SYS_C0011300) violated
會話3:
SID:會話id號
TYPE:鎖的型別
ID1:會話操作物件的id號
ID2:ID1+ID2 定位回滾段上的一個地址(即修改之前資料映象地址),由於138和156會話是一樣的說明指向的是同一個地址,換句話說操作的是同一行資料
LMODE:鎖模式,不同的數字代表不同的鎖模式 例如 0 現在沒有申請到鎖 3 共享鎖模式(段級共享鎖) 6 排他鎖模式 鎖的級別越高限制越多
REQUEST:目前會話沒有鎖,正在申請的鎖模式 例如 0 沒有正在申請的鎖,說明已經有鎖了 6 現在正在申請6號鎖,目前因為沒有才申請
BLOCK:當前正在阻塞幾個會話 例如 1 當前正在阻塞一個會話 2 當前正在阻塞兩個會話
鎖的實質:是維護一個事務完整性的,鎖的資訊是資料塊的一個屬性,是物理的,並不是邏輯上屬於某個表或者某幾行的。
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 65562 824 0 4 0 --插入的修改值相同才被阻塞,鎖級別是4
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
49 TX 327706 1041 6 0 0 --49號會話插入第二條記錄未被阻塞
17 TX 65562 824 6 0 1 -----正在阻塞一個會話
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
insert時v$lock檢視裡面多了一個TX鎖(就是最後一行)。
insert和update delete操作的不同,後兩者都是對同一條記錄的修改權爭用產生阻塞(這裡不涉及修改值的問題),
而insert操作實際上插入了2條不同的記錄,由於這2條不同的記錄的修改值一樣違反了主鍵約束從而產生阻塞,實際是對修改值的相同產生了阻塞。鎖的級別為4,這種鎖比update的鎖級別要低,鎖的級別越低限制越少。
#####################################################
update造成阻塞的示例
會話1:
BYS@dg2>update t set x=2 where x=1;
1 row updated.
BYS@dg2>
會話二:
BYS@dg2>update t set x=3 where x=1;
會話3
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 196624 1041 0 6 0
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 196624 1041 6 0 1
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id=75052;
OBJECT_NAME
--------------------
T
說明 :17會話在T表加了TM TX鎖。TM鎖模式為3--共享鎖 TX鎖模式為6--排它鎖。
目前TX鎖正在阻塞一個會話-49 可以在49的行看到它在請求一個模式為6的鎖。
49會話是當前被阻塞的會話,它操作的物件也是T表(從ID1看)。
TM鎖模式也為3(共享鎖是有幾個會話就可以建立幾個共享鎖,同時存在).
此時它正在申請17會話所持有的模式為6的鎖(操作同一行資料)。
delete造成阻塞的示例
會話1:
BYS@dg2>delete from t; 17會話在刪除表內記錄-只有一條。未提交,加了TM TX鎖
1 row deleted.
會話2:
BYS@dg2>delete from t; 此時在49會話也進行刪除表內記錄,hang住
會話3:
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 262145 912 0 6 0 --被阻塞了正在申請一個模式6的鎖
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 262145 912 6 0 1 持有模式6鎖,阻塞一個會話
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
49會話因TX鎖爭用導致hang,enq=enqueues佇列鎖
#####################
select...for update 鎖阻塞 這是一種對結果集修改的保護機制
場景:一次性修改多條記錄的時候會用到這個命令,起到鎖定結果集的效果,這也是結果集修改引起的阻塞
會話1:
BYS@dg2>select * from t where x=2 for update; 對查詢出的結果集進行獨佔,此時不允許其他會話進行修改
X
----------
2
會話2:
BYS@dg2>select * from t where x=2 for update;
會話3:
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 589852 1023 0 6 0
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 589852 1023 6 0 1
17號會話阻塞49號會話。這種方法可以一次鎖定多行記錄。一個表上只能有一個6號鎖。
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
BYS@dg2>select distinct sid from v$mystat;
SID
----------
17
會話2SID
BYS@dg2>select distinct sid from v$mystat;
SID
----------
49
insert造成阻塞的示例
在會話1向表T插入一條資料值1,因為插入資料所在欄位有主鍵約束。
此時會話1不提交或回滾插入操作時,在會話2進行同樣的向表T插入一條資料值1時被髮生阻塞,語句無法執行。
此時再開啟一個會話,可以從v$lock檢視中查詢出有鎖的表及所在會話ID。
會話1:
BYS@dg2>create table t(x number primary key);---新建表,設定主鍵
Table created.
BYS@dg2>insert into t values(1); --插入資料不提交
1 row created.
BYS@dg2>
會話2:此時執行插入相同數值時被阻塞----hang住。
BYS@dg2>insert into t values(1);
如果會話1提交,阻塞中止,此時會話2語句執行並報錯,提示違反了主鍵的一致性約束
BYS@dg2>insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (BYS.SYS_C0011300) violated
會話3:
SID:會話id號
TYPE:鎖的型別
ID1:會話操作物件的id號
ID2:ID1+ID2 定位回滾段上的一個地址(即修改之前資料映象地址),由於138和156會話是一樣的說明指向的是同一個地址,換句話說操作的是同一行資料
LMODE:鎖模式,不同的數字代表不同的鎖模式 例如 0 現在沒有申請到鎖 3 共享鎖模式(段級共享鎖) 6 排他鎖模式 鎖的級別越高限制越多
REQUEST:目前會話沒有鎖,正在申請的鎖模式 例如 0 沒有正在申請的鎖,說明已經有鎖了 6 現在正在申請6號鎖,目前因為沒有才申請
BLOCK:當前正在阻塞幾個會話 例如 1 當前正在阻塞一個會話 2 當前正在阻塞兩個會話
鎖的實質:是維護一個事務完整性的,鎖的資訊是資料塊的一個屬性,是物理的,並不是邏輯上屬於某個表或者某幾行的。
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 65562 824 0 4 0 --插入的修改值相同才被阻塞,鎖級別是4
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
49 TX 327706 1041 6 0 0 --49號會話插入第二條記錄未被阻塞
17 TX 65562 824 6 0 1 -----正在阻塞一個會話
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
insert時v$lock檢視裡面多了一個TX鎖(就是最後一行)。
insert和update delete操作的不同,後兩者都是對同一條記錄的修改權爭用產生阻塞(這裡不涉及修改值的問題),
而insert操作實際上插入了2條不同的記錄,由於這2條不同的記錄的修改值一樣違反了主鍵約束從而產生阻塞,實際是對修改值的相同產生了阻塞。鎖的級別為4,這種鎖比update的鎖級別要低,鎖的級別越低限制越少。
#####################################################
update造成阻塞的示例
會話1:
BYS@dg2>update t set x=2 where x=1;
1 row updated.
BYS@dg2>
會話二:
BYS@dg2>update t set x=3 where x=1;
會話3
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 196624 1041 0 6 0
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 196624 1041 6 0 1
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id=75052;
OBJECT_NAME
--------------------
T
說明 :17會話在T表加了TM TX鎖。TM鎖模式為3--共享鎖 TX鎖模式為6--排它鎖。
目前TX鎖正在阻塞一個會話-49 可以在49的行看到它在請求一個模式為6的鎖。
49會話是當前被阻塞的會話,它操作的物件也是T表(從ID1看)。
TM鎖模式也為3(共享鎖是有幾個會話就可以建立幾個共享鎖,同時存在).
此時它正在申請17會話所持有的模式為6的鎖(操作同一行資料)。
delete造成阻塞的示例
會話1:
BYS@dg2>delete from t; 17會話在刪除表內記錄-只有一條。未提交,加了TM TX鎖
1 row deleted.
會話2:
BYS@dg2>delete from t; 此時在49會話也進行刪除表內記錄,hang住
會話3:
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 262145 912 0 6 0 --被阻塞了正在申請一個模式6的鎖
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 262145 912 6 0 1 持有模式6鎖,阻塞一個會話
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
49會話因TX鎖爭用導致hang,enq=enqueues佇列鎖
#####################
select...for update 鎖阻塞 這是一種對結果集修改的保護機制
場景:一次性修改多條記錄的時候會用到這個命令,起到鎖定結果集的效果,這也是結果集修改引起的阻塞
會話1:
BYS@dg2>select * from t where x=2 for update; 對查詢出的結果集進行獨佔,此時不允許其他會話進行修改
X
----------
2
會話2:
BYS@dg2>select * from t where x=2 for update;
會話3:
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 589852 1023 0 6 0
49 TM 75052 0 3 0 0
17 TM 75052 0 3 0 0
17 TX 589852 1023 6 0 1
17號會話阻塞49號會話。這種方法可以一次鎖定多行記錄。一個表上只能有一個6號鎖。
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
49 enq: TX - row lock contention
相關文章
- Oracle LOCK內部機制及最佳實踐系列(一)分別模擬insert|update|delete造成阻塞及說明Oracledelete
- 利用insert,update和delete注入獲取資料delete
- 34、VIEW可以insert,delete,update.Viewdelete
- sql server 帶有OUTPUT的INSERT,DELETE,UPDATESQLServerdelete
- Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)delete
- mysql innodb新建索引堵塞update ,insert,deleteMySql索引delete
- mysql 在delete、insert、update 時,page的變化MySqldelete
- MySQL之資料的insert-delete-update操作MySqldelete
- SQLite語句(二):INSERT DELETE UPDATE SELECTSQLitedelete
- MongoDB入門系列(二):Insert、Update、Delete、DropMongoDBdelete
- java-Mybatis XML 對映器(select,insert, update 和 delete)JavaMyBatisXMLdelete
- MERGE新特性(UPDATE WHERE,DELETE WHERE,INSERT WHERE)delete
- LINQ to SQL語句之Insert/Update/Delete操作SQLdelete
- Oracle資料庫中Insert、Update、Delete操作速度Oracle資料庫delete
- Delete,insert,update與undo的關係[轉載TOM文章]delete
- KunlunDB功能之insert/update/delete...returning語句delete
- 索引是否也能提高UPDATE,DELETE,INSERT速度 解釋索引delete
- SQL Server的Merge —— 一步實現 insert,update,deleteSQLServerdelete
- 輕量ORM-SqlRepoEx (四)INSERT、UPDATE、DELETE 語句ORMSQLdelete
- myisam對於update,insert,delete關於auto_incremant的影響deleteREM
- innodb對於update,insert,delete關於auto_incremant的影響deleteREM
- 檢視insert,delete,update對基表的影響(檢視初識)delete
- 模擬阻塞會話例項會話
- 【MyBatis原始碼分析】insert方法、update方法、delete方法處理流程(上篇)MyBatis原始碼delete
- 【MyBatis原始碼分析】insert方法、update方法、delete方法處理流程(下篇)MyBatis原始碼delete
- 阻塞(block)過程模擬與分析!BloC
- MyBatis(五) insert、update、delete 、主鍵回填、返回matched行數和affected行數、引數配置#{},${}MyBatisdelete
- ASP.NET動態網站開發培訓-20.INSERT、UPDATE和DELETE語句ASP.NET網站delete
- 如何插入insert_update,delete_select特殊字元&到oracle表中delete字元Oracle
- sql server merge 做insert和updateSQLServer
- 管理工具造成的阻塞
- 模擬RI鎖定導致阻塞的場景
- drop物化檢視log表導致insert、delete、update報ORA-00942delete
- 用merge 語句代替 insert 和deletedelete
- Oracle中 Update和insert結合語法Oracle
- mysql INSERT ... ON DUPLICATE KEY UPDATEMySql
- MySQL 4.1.0 中文參考手冊 --- 6.4 資料操縱:SELECT, INSERT, UPDATE, DELETE (轉)MySqldelete
- update引起資料庫阻塞資料庫