11g Concepts中摘錄的鎖的信息 Table Locks (TM) A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. 當事務通過INSERT、UPDATE、DELETE、MERGE和FOR UPDATE對表進行修改時，就會獲得一個表鎖，也稱為TM鎖子句，或鎖表語句。DML操作需要表鎖來為事務保留對表的DML訪問權限，并防止DDL與事務沖突的操作。
A table lock can be held in any of the following modes: Row Share (RS) This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table. 這個鎖，也稱為子共享表鎖(SS)，表示持有表上鎖的事務已鎖定表中的行并打算鎖定更新它們。行共享鎖是表鎖中限制最少的一種模式，它為表提供最高程度的并發性。 Row Exclusive Table Lock (RX) This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table. 這個鎖，也稱為subexclusive table lock (SX)，通常表示持有鎖的事務已經更新了表行或發出了SELECT…FOR UPDATE。SX鎖允許其他事務在同一表中同時查詢、插入、更新、刪除或鎖定行。因此,SX鎖允許多個事務為同一個表獲取同步的SX和子共享表鎖。 Share Table Lock (S) A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table. 事務持有的共享表鎖允許其他事務查詢表(除了SELECT…FOR UPDATE)，但只允許更新如果一個事務持有共享表鎖。由于多個事務可能同時持有一個共享表鎖，因此持有此鎖不足以確保事務可以修改表。 Share Row Exclusive Table Lock (SRX) This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table. 這個鎖，也稱為共享-subexclusive table鎖(SSX)，比共享表鎖有更多的限制。一次只能獲得一個事務SSX鎖定給定的表。事務持有的SSX鎖允許其他事務查詢表(除了SELECT…FOR UPDATE)，但不更新表。 Exclusive Table Lock (X) This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table. 此鎖是最嚴格的，禁止其他事務執行任何類型的DML語句或將任何類型的鎖放在表上。
購買 柜子狀態是打開：6號模式 第2類人，試用期（試用期間不能讓別人用） 第3類人，立即購買（相當于我們的update、delete、select for update、LOCK TABLE table IN ROW EXCLUSIVE MODE語句） 總結：update、delete、select for update在行上都是產生排他鎖
共享鎖將允許別的共享鎖存在，也就是共享跟共享是不沖突的。 比如用戶A在表T上執行了UPDATE第1行，那么表t上有個表級的共享鎖，那用戶B在表T上執行了UPDATE第2行，那么也會在表t上有個表級的共享鎖，雖然行上都是排它鎖，但不是同一行，所以他們在行上沒有沖突，在表上也沒有沖突 比如用戶A執行LOCK TABLE T IN ROW EXCLUSIVE MODE，用戶B可以同時執行LOCK TABLE T IN ROW EXCLUSIVE MODE或LOCK TABLE T IN ROW SHARE MODE
查詢兩個會話的鎖信息 SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (sid1,sid2) order by sid;
查詢鎖類型的具體含義 SQL> select * from V$LOCK_TYPE where type in ('TX','AE','TM','TO','OD'); TYPE NAME ID1_TAG ID2_TAG IS_USE DESCRIPTION ----- -------------- ----------------- ----------------- ------ ---------------------------------------------------------------------- TM DML object # table/partition YES Synchronizes accesses to an object TX Transaction usn<<16 | slot sequence YES Lock held by a transaction to allow other transactions to wait for it AE Edition Lock edition obj# 0 NO Prevent Dropping an edition in use OD Online DDLs object # 0 NO Lock to prevent concurrent online DDLs TO Temp Object object # 1 NO Synchronizes DDL and DML operations on a temp object
案例1 會話1的sid是161,會話2的sid是189
sid1 不commit SQL> update test set id=11; 1 row updated sid2一直創建不成功 SQL> alter table test add hid3 number; sid3查詢結果，發現sid1和和sid2的表級鎖都是3 SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid; SID ID1 ID2 TY LMODE REQUEST ---------- ---------- ---------- -- ---------- ---------- 161 65547 1930 TX 6 0 161 88539 0 TM 3 0 --sid1的表級鎖為3 161 100 0 AE 4 0 161 79833 1 TO 3 0 189 196612 2185 TX 6 0 189 88539 0 TM 3 0 --sid2的表級鎖為3 189 100 0 AE 4 0 189 88539 0 OD 6 0 189 65547 1930 TX 0 4 189 79833 1 TO 3 0 SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'; SID FINAL_BLOCKING_SESSION EVENT ----------------- ---------------------- ----------- 189 161 enq: TX - row lock contention
案例2 會話1的sid是161,會話2的sid是189
sid1不commit SQL> update test set id=11; 1 row updated sid2，直接報錯 SQL> drop table test; drop table test * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired sid3修改ddl后，sid2再執行一次，sid查詢結果 SQL> alter system set ddl_lock_timeout=60 SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid; SID ID1 ID2 TY LMODE REQUEST ---------- ---------- ---------- -- ---------- ---------- 161 88539 0 TM 3 0 --sid1的表級鎖為3 161 100 0 AE 4 0 161 79833 1 TO 3 0 161 458768 1934 TX 6 0 189 88539 0 TM 0 6 --sid2當前表級鎖為0，但是請求表級鎖6 189 100 0 AE 4 0 189 0 1 AE 4 0 189 79833 1 TO 3 0 SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'; SID FINAL_BLOCKING_SESSION EVENT --- ---------------------- ------------- 189 161 enq: TM - contention
CREATE INDEX ONLINE create index online會堵塞update嗎？ 不會 先執行update后不提交，后執行create index online不會報錯，但是create index online一直處于堵塞狀態 先執行create index online后，后執行update正常update，但是如果update不提交，則create index online一直處于堵塞狀態
理解到：create index online在一行行創建索引過程中，并不是說這一行創建好索引了，再對這一行執行update時必須等到所有行都create index online完成后才會正常udpate，也就是說不管update在create index online前還是后，create index online都不影響update，倒是update如果沒有提交會影響create index online。
如下兩個實驗會話1的sid是161,會話2的sid是189 實驗1，先執行create index online,創建到一半后，update最小rowid的一行，按理說create index online應該已經過了這一行，應該會堵塞update會話，實際上并沒有堵塞，update一樣很快，到時最后查詢下來發現update倒是把create index online堵塞了
sid1執行 SQL> select object_id from test1 where rowid in (select min(rowid) from test1); OBJECT_ID ---------- 4559 sid2執行，創建正常耗時6秒 SQL> create index ind_obd on test1 (OBJECT_ID) online; Index created. Elapsed: 00:00:06.06 SQL> drop index ind_obd; Index dropped. Elapsed: 00:00:00.14 SQL> create index ind_obd on test1 (OBJECT_ID) online; 在sid2執行的6秒期間，馬上在sid1執行，發現sid1執行很快，并不堵塞 SQL> update test1 set object_id=1 where OBJECT_ID=4559; 32 rows updated. sid3執行如下，發現sid1 161堵塞了sid2 189 SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'; SID FINAL_BLOCKING_SESSION EVENT ---- ---------------------- ------------------------- 189 161 enq: TX - row lock contention SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid; SID ID1 ID2 TY LMODE REQUEST ---------- ---------- ---------- -- ---------- ---------- 161 79833 1 TO 3 0 161 262151 1938 TX 6 0 161 88544 0 TM 3 0 161 100 0 AE 4 0 189 100 0 AE 4 0 189 79833 1 TO 3 0 189 131075 2139 TX 6 0 189 88544 0 DL 3 0 189 262151 1938 TX 0 4 189 88552 0 TM 4 0 189 88544 0 DL 3 0 189 88544 0 OD 4 0 189 88544 0 TM 2 0 13 rows selected.
實驗2，先執行create index online,創建到一半后，update最大rowid的一行，按理說create index online應該還沒到這一行，不會堵塞update會話，實驗也發現確實是這樣，update很快，到時最后查詢下來是update把create index online堵塞了
sid1執行 SQL> select object_id from test1 where rowid in (select max(rowid) from test1); OBJECT_ID ---------- 85998 sid2執行，創建正常耗時6秒 SQL> create index ind_obd on test1 (OBJECT_ID) online; Index created. Elapsed: 00:00:06.06 SQL> drop index ind_obd; Index dropped. Elapsed: 00:00:00.14 SQL> create index ind_obd on test1 (OBJECT_ID) online; 在sid2執行的6秒期間，馬上在sid1執行，發現sid1執行很快，并不堵塞 SQL> update test1 set object_id=1 where OBJECT_ID=85998; 32 rows updated. sid3執行如下，發現sid1 161堵塞了sid2 189 SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID'; SID FINAL_BLOCKING_SESSION EVENT ---- ---------------------- ------------------------- 189 161 enq: TX - row lock contention SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid; SID ID1 ID2 TY LMODE REQUEST ---------- ---------- ---------- -- ---------- ---------- 161 79833 1 TO 3 0 161 88544 0 TM 3 0 161 393242 2315 TX 6 0 161 100 0 AE 4 0 189 79833 1 TO 3 0 189 88544 0 TM 2 0 189 88546 0 TM 4 0 189 458777 1936 TX 6 0 189 100 0 AE 4 0 189 88544 0 DL 3 0 189 88544 0 DL 3 0 189 393242 2315 TX 0 4 189 88544 0 OD 4 0 13 rows selected.
查詢鎖對象是哪張表，哪一行的SQL 先查出堵塞的會話的SID，再如下查詢堵塞的是哪張表，行是哪行 select a.sid, a.row_wait_obj#, a.row_wait_file#, a.row_wait_block#, a.row_wait_row#,b.owner,b.object_name from v$session a,dba_objects b where a.row_wait_obj#=b.object_id and sid in (XX);
select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid in (XX);--此次查詢到row_wait_obj#=-1表示是持有鎖的會話
