对oracle几种锁模式的理解

相关主题

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

对oracle锁几种模式的理解

一．几种锁模式的描述

在ORACLE动态性能视图v$lock里lmode有以下几种类型（

这里的描述比较模糊，但是在对lock table语句解释中可以找到相对应的详细解释（/cd/B19306_01/server.102/b14200/statements_9015.htm#i2064405）

ROW SHARE ROW SHARE permits concurrent access to the locked table but prohibits 1.1.ROW

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 ID1ID2LMODE REQUEST

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

CTIME BLOCK

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

42A8760442A8761C285TM7214202

1051

42A876B042A876C8267TM7214200 6

450

这里SID285的TM2阻塞了SID267的TM6

ROW EXCLUSIVE ROW EXCLUSIVE is the same as ROW SHARE,but it also prohibits 2.2.ROW

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 ID1ID2LMODE REQUEST

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

CTIME BLOCK

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

42A8760442A8761C285TM7214204 0

6511

42A876B042A876C8267TM7214200 3

1050

这里可以看到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=1231where id=23423;

1row updated.

SID:294

SQL>select*from v$lock where sid in(285,267);

ADDR KADDR SID TYPE ID1ID2LMODE REQUEST

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

CTIME BLOCK

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

42A8760442A8761C285TM7214203 0

42B58AFC42B58C18285TX45877360866 0

当SID285做了修改而没有commit或者rollback时，这里有两个锁，其中一个就是TM3的。SHARE SHARE permits concurrent queries but prohibits updates to the locked table.

3.3.SHARE

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=1231where id=23423;

SID:294

SQL>select*from v$lock where sid in(285,267);

ADDR KADDR SID TYPE ID1ID2LMODE