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表示是持有锁的会话
