一.几种锁模式的描述
在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.
SID:285