oracle 设置 锁模式,对oracle几种锁模式的理解

本帖最后由 paololiu 于 2012-4-26 11:24 编辑

知道一般情况下alter index rebuild online不会堵塞DML操作，那又是为什么呢。正好闲着没事做，就试着从锁上去找原因。

在这过程中写了个文档，和大家一起分享下对oracle几种锁模式的理解。

一．几种锁模式的描述

0 - none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

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.

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的。

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

ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST

-------- -------- ---------- ------ ---------- ---------- ---------- ----------

CTIME      BLOCK

---------- ----------

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

ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST

-------- -------- ---------- ------ ---------- ---------- ---------- ----------

CTIME      BLOCK

---------- ----------

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

ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST

-------- -------- ---------- ------ ---------- ---------- ---------- ----------

CTIME      BLOCK

---------- ----------

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引发的血案

根据上面的实验，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

ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST

-------- -------- ---------- ------ ---------- ---------- ---------- ----------

CTIME      BLOCK

---------- ----------

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

ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST

-------- -------- ---------- ------ ---------- ---------- ---------- ----------

CTIME      BLOCK

---------- ----------

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操作。 "

根据前面的实验：lmode4--->lmode3，这样也就可以理解了

而且根据网友的实验，11g开始后面的那个DML(就是SID154)将不会再被阻塞。

对oracle几种锁模式的理解.pdf

(199.43 KB, 下载次数: 246)

2012-4-26 11:03 上传

点击文件名下载附件