|
知道一般情况下alter index rebuild online不会堵塞DML操作，那又是为什么呢。正好闲着没事做，就试着从锁上去找原因。
在这过程中写了个文档，和大家一起分享下对oracle几种锁模式的理解。
一．几种锁模式的描述
在ORACLE动态性能视图v$lock里lmode有以下几种类型（http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1147.htm#i1407629 ）：
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
这里的描述比较模糊，但是在对lock table语句解释中可以找到相对应的详细解释（http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm#i2064405 ）
1.ROW SHARE
|
ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.
ROW SHARE模式允许同时访问被锁定的表，但是禁止用户以排他方式锁定整个表。ROW SHARE与SHARE UPDATE相同，只是为了兼容早期的ORACLE版本。对应lmode2，row-S (SS)。
实验：but prohibits users from locking the entire table for exclusive access.
SID:285
SQL> lock table test_objects in row share mode;
Table(s) Locked.
SID:267
SQL> lock table test_objects in exclusive mode;
此时操作被锁。 对象被 添加 排它锁 之前，不可以被添加任何锁！！！
SID:294(用来查看当前的锁信息)
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
42A87604 42A8761C 285 TM 72142 0 2 0 105 1
42A876B0 42A876C8 267 TM 72142 0 0 6 45 0
这里SID285的TM2阻塞了SID267的TM6
|
2.ROW EXCLUSIVE
|
ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
ROW EXCLUSIE类似于ROW SHARE模式，但是不能应用在SHARE模式中。当update,insert,delete发生时，ROW EXCLUSIVE会自动获得。对应lmode3，row-X (SX) 。
针对这里的描述做两个实验：
实验1：but it also prohibits locking in SHARE mode
SID:285
SQL> lock table test_objects in share mode;
Table(s) Locked.
SID:267
SQL> lock table test_objects in row exclusive mode;
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
42A87604 42A8761C 285 TM 72142 0 4 0 651 1
42A876B0 42A876C8 267 TM 72142 0 0 3 105 0
这里可以看到SID285的TM4阻塞了SID267的TM3
|
实验2：
|
ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
当执行 update/ insert/ delete 操作的时候，会自动获取 行级排它锁
SID:285
SQL> update test_objects set object_id=1231 where id=23423;
1 row updated.
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ----------
42A87604 42A8761C 285 TM 72142 0 3 0 6 0
42B58AFC 42B58C18 285 TX 458773 6086 6 0 6 0
当SID285做了修改而没有commit或者rollback时，这里有两个锁，其中一个就是TM3的。 其中 ID1=458773是数据库中 数据字典，不用理会
|
3.SHARE SHARE
|
permits concurrent queries but prohibits updates to the locked table.
SHARE允许同时查询，但是禁止更新被锁定的表。对应lmode4，share (S) 。
实验：but prohibits updates to the locked table
SID:285
SQL> lock table test_objects in share mode;
Table(s) Locked.
SID:267
SQL> update test_objects set object_id=1231 where id=23423;
此时操作被阻塞
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
42A87604 42A8761C 285 TM 72142 0 4 0 28 1
42A876B0 42A876C8 267 TM 72142 0 0 3 6 0
这里SID285的TM4阻塞了SID267的TM3
|
4.SHARE ROW EXCLUSIVE
|
SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.
SHARE ROW EXCLUSIVE模式用于查看整个表，它允许其他session查看表中的行，但是不允许添加SHARE模式的锁。对应lmode5，S/Row-X (SSX) 。
实验：but to prohibit others from locking the table in SHARE mode or from updating rows（根据上面ROW EXCLUSIVE中的实验，修改数据本来就会要对整个表添加一个TM3的锁）
SID:285
SQL> lock table test_objects in share row exclusive mode;
Table(s) Locked.
SID:267
SQL> lock table test_objects in share mode;
该操作被阻塞
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
42A87604 42A8761C 285 TM 72142 0 5 0 24 1
42A876B0 42A876C8 267 TM 72142 0 0 4 3 0
这里SID285的TM5阻塞了SID267的TM4
|
5.EXCLUSIVE
|
EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
EXCLUSIVE模式允许查询被锁表上的数据，但是禁止任何其他任何活动（这里我理解是禁止添加其他任何模式的锁）。对应lomde6，exclusive (X) 。
经过类似的实验也证实了EXCLUSIVE确实会阻塞任何一种模式的锁，当然这其中也包括EXCLUSIVE。
总结一下上面提到的堵塞的几种情况：
lmode2--->lmode6
lmode4--->lmode3
lmode5--->lmode4
lmode6--->所有的
|
二．几种实际情况的锁堵塞
1.DML操作
|
SID:285
SQL> update test_objects set object_id=1231 where id=23423;
1 row updated.
SID:267
SQL> delete from test_objects where id=23423;
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
43A53A94 43A53AA8 267 TX 327692 7824 0 6 3 0
42A87604 42A8761C 285 TM 72142 0 3 0 24 0
42A876B0 42A876C8 267 TM 72142 0 3 0 3 0
42B58AFC 42B58C18 285 TX 327692 7824 6 0 24 1
这里可以看到SID285的TM3并没堵塞SID267的TM3,这里真正发生堵塞的是TX6。
这里还有一个锁定对象的问题。上面两个TM3的锁针对的对象是object_id为72142的表，既然描述是类似行共享，自然是不会堵塞的。而两个TX6的锁针对的对象可以理解成表中的行，在这些行上添加EXCLUSIVE锁（lmode6，exclusive (X) )自然是会堵塞其他的EXCLUSIVE锁的。
解决这种类型的锁堵塞当然就是在代码中尽早commit结束事务。很多地方都写到尽早commit可以提高运行效率，这里所指的是释放锁（特别是lmode6的EXCLUSIVE锁）减少堵塞，以提高并发性。（不是以减少数据的量来提高效率的，事实上不管多大的数据量，一个commit的过程都是很"平"的，具体可以参考Thoms的那本ORACLE体系结构的书）
|
2.DDL操作
|
alter index ... rebuild堵塞DML操作
SID:285
SQL> alter index IDX_TEXT_OBJECT_ID rebuild;
SID:267
update test_objects set object_id=1231 where id=2342;
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- -------------------- ----------
43A53A94 43A53AA8 285 DL 72142 0 3 0 3 0
43A53B4C 43A53B60 285 DL 72142 0 3 0 3 0
42A87604 42A8761C 285 TM 72142 0 4 0 3 1
42A876B0 42A876C8 267 TM 72142 0 0 3 2 0
SID285的TM4(SHARE)堵塞了SID267的TM3(ROW EXCLUSIVE)，就如上面提到的ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode.
那alter index ... rebuild online为什么不会堵塞DML操作呢
SID:285
SQL> alter index IDX_TEXT_OBJECT_ID rebuild online;
SID:267
SQL> update test_objects set object_id=1231 where id=2342;
1 row updated.
SID:294
SQL> select * from v$lock where sid in (285,267);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
43A53A94 43A53AA8 285 DL 72142 0 3 0 9 0
43A53B4C 43A53B60 285 DL 72142 0 3 0 9 0
42A87604 42A8761C 285 TM 72142 0 2 0 6 0
42A876B0 42A876C8 285 TM 72325 0 4 0 6 0
42A8775C 42A87774 267 TM 72142 0 3 0 6 0
42B157D4 42B158F0 267 TX 458789 6090 6 0 6 0
6 rows selected.
这里可以看到SID285添加了两个DL3的锁(lmode3,ROW EXCLUSIVE),一个TM2(lmode2,ROW SHARE)，还有一个TM4的锁(lmode4,SHARE)。
SID267就和一般的update语句一样添加了一个TM3的锁和一个TX6的锁。
根据前面总结的：
lmode2--->lmode6
lmode4--->lmode3
lmode5--->lmode4
lmode6--->所有的
lmode3没问题。
lmode2堵塞lmode6，但是他们锁的对象不一样也没问题。
lmode4堵塞lmode3，虽然这里都是TM类型的，但是SID285的TM4的对象是72325，而SID的TM3的对象是72142，所以也不会有问题的。（这里的72325其实是ORACLE创建的一个临时对象，对它上个lmode4的锁自然不会影响对目标表的操作）。
因为DDL的操作和DML不同，它没有显式的提交。一条语句完了就自动commit了，也不显示rollback的情况，而且执行过程中强行中断还有可能会有些意想不到的情况。所以在平时情况可以多去看看，什么样的DDL语句会加什么样的锁，当执行的时候也就知道当前情况下执行的这条DDL语句在持有锁的时候会有什么影响。
|
3.一个特别的情况
|
这里是一个在ITPUB上看到的帖子，标题是:alter index rebuild online引发的血案
http://www.itpub.net/thread-1445427-1-1.html
根据上面的实验，alter index rebuild online是不会阻塞DML操作的，那又为什么会引起血案呢：
他的情况是这样：一个大的事务一直在执行，没有提交，以后操作alter index rebuild online，再之后所有应用就都hang住了。
模拟的情况如下：
SID:285
SQL> update test_objects set object_id=1231 where id=2342;
1 row updated.
SID:267
SQL> alter index IDX_TEXT_OBJECT_ID rebuild online;
SID:154
SQL> update test_objects set object_id=1231 where id=234245;
查看锁的情况：
SQL> select * from v$lock where sid in (285,267,154) order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ----------
42A87808 42A87820 154 TM 72142 0 0 3 21 0
43A53B4C 43A53B60 267 DL 72142 0 3 0 216 0
43A53A94 43A53AA8 267 DL 72142 0 3 0 216 0
42A876B0 42A876C8 267 TM 72142 0 2 4 216 0
42A8775C 42A87774 267 TM 72328 0 4 0 214 0
42A87604 42A8761C 285 TM 72142 0 3 0 228 1
42B4D60C 42B4D728 285 TX 589868 7867 6 0 228 0
最先开始的SID285,持有两个锁，TM3和TX6没有问题
然后SID267,两个DL3锁没问题，一个其他对象的TM4锁，也没有问题，但是还有另一个已经持有了TM2的锁的同时，还要求请求lmode4的锁
再往后的SID154的TM3锁也没有得到
一位网友在他的BLOG上讲解原因：
"虽然rebuild index online在执行期间只持有模式2的TM锁，不会阻塞DML操作，但在操作的开始和结束阶段，是需要短暂的持有模式为4的TM锁的，这段会阻塞表上的所有DML操作。 "
源地址是：http://www.ningoo.net/html/2008/lock_mechanism_of_rebuild_index_online.html
根据前面的实验：lmode4--->lmode3，这样也就可以理解了
而且根据网友的实验，11g开始后面的那个DML（就是SID154）将不会再被阻塞。
|
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1606090>
rebuild index online的锁机制浅析
2008年09月28日 by NinGoo 2 Comments
一般都说，rebuild index online不阻塞DML操作，这是相对于rebuild index来说的，加上了online，只是在rebuild的期间不阻塞DML，但是在开始和结束阶段还是可能阻塞其他进程的DML的，要弄清楚到底是 阻塞还是不阻塞，何处阻塞，为什么阻塞，还是要从锁的角度来分析。本文实验环境为Oracle 10.2.0.4。
Oracle中的锁，一共有6两种模式：
- 2：Row-S 行共享(RS)：共享表锁，sub share
- 3：Row-X 行独占(RX)：用于行的修改，sub exclusive
- 4：Share 共享锁(S)：阻止其他DML操作，share
- 5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
- 6：exclusive 独占(X)：独立访问使用，exclusive
我们知道，DML操作一般要加两个锁，一个是对表加模式为3的TM锁，一个是对数据行的模式为6的TX锁。只要操作的不是同一行数据，是互不阻塞的。但是rebuild index online在开始和结束的时候是需要对表加一个模式为4的TM锁的， 这个可以很容易通过实验观察到，实验中的测试表t是通过create table t as select * from all_objects生成，并且多次执行insert into t select * from t产生较多的数据，以便延迟rebuild的时间来观察系统中锁的情况：
session 1：
SQL> delete from t where object_id=28;
1 row deleted.
session 2：
SQL> alter index ix_t rebuild online;
Session 2被阻塞，会话挂起，这时查询v$lock，可以得到如下结果：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1622 TX 655398 1361 6 0
从上面的结果可以知道，1622是session 1，1643是session 2，session 2一共出现了4个锁，两个DL锁，一个针对表t的TM锁，一个是online rebuild index时需要的一个中间表的TM锁，中间表用于记录rebuild期间的增量数据，原理类似于物化视图日志，其object_id为10607，这是 一个索引组织表(IOT)，从这里我们也可以发现IOT的优点和适合的场合，这张中间表只有插入，不会有删除和修改操作，而且只有主键条件查询，正是 IOT最合适的场景：
SQL> select object_name,object_type from all_objects where object_id=10607;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_JOURNAL_10602 TABLE
SQL> select table_name,iot_type from all_tables where table_name='SYS_JOURNAL_10602';
TABLE_NAME IOT_TYPE
------------------------------ ------------
SYS_JOURNAL_10602 IOT
Session 2在请求一个模式为4的TM锁，模式4会阻塞这个表上的所有DML操作，所以这是再往这个表上执行DML也会挂起
session 3：
SQL> delete from t where object_id=46;
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 0 3
1622 TX 655398 1361 6 0
1627就是session 3，请求模式为3的TM锁无法获得，会话被阻塞。这是因为锁请求是需要排队的，即使session 3和session 1是可以并发的，但由于session 2先请求锁并进入等待队列，或来的session 3也只好进入队列等待。所以如果在执行rebuild index online前长事务，并且并发量比较大，则一旦执行alter index rebuild online，可能因为长事务阻塞，可能导致系统瞬间出现大量的锁，对于压力比较大的系统，这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill 掉，rebuild index online一旦执行，不可轻易中断，否则可能遇到ORA-08104。
在session 1执行rollback，可以发现很短时间内session 3也正常执行完毕，说明session 2只有模式4的TM锁的时间很短，然后在rebuild online的进行过程中，对表加的是模式为2的TM锁，所以这段时间不会阻塞DML操作：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1643 TM 10599 0 2 0
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1627 TX 655392 1361 6 0
保持session 3的事务不提交，等待一段时间后，session 2始终无法完成操作，再观察系统中锁的情况，可以发现又发生了变化：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1643 TX 589852 258 6 0
1627 TX 655392 1361 6 0
Session 2又开始在请求模式4的TM锁，被session 3阻塞!这是在session 1再执行DML操作，同样会被session 2阻塞，进入锁等待队列。
Session 1：
SQL>delete from t where object_id=11;
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 0 3
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1643 TX 589852 258 6 0
1627 TX 655392 1361 6 0
在session 3执行rollback或者commit以后，session 2和session 3都很快执行完毕。
从上面的试验可以发现，虽然rebuild index online在执行期间只持有模式2的TM锁，不会阻塞DML操作，但在操作的开始和结束阶段，是需要短暂的持有模式为4的TM锁的，这段会阻塞表上的所 有DML操作。我们在做rebuild index online的时候，一定要在开始和结束阶段观察系统中是否有长事务的存储，对于并发量较大的系统，最严重的后果，可能在这两个关键点导致数据库产生大量 锁等待，系统负载飙升，甚至宕机。
源文档 <http://www.ningoo.net/html/2008/lock_mechanism_of_rebuild_index_online.html>
|
