oracl锁的几种模式

最新推荐文章于 2023-02-24 18:38:20 发布

Steam noodle

最新推荐文章于 2023-02-24 18:38:20 发布

阅读量289

点赞数

本文链接：https://blog.csdn.net/weixin_40908896/article/details/96507372

版权

ORACLE里锁有以下几种模式:
0：none
1：null 空
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

1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index,rebuild index, Lock Share
locked_mode为2,3不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

锁阻塞情况：
lmode2—>lmode6
lmode4—>lmode3
lmode5—>lmode4,lmode3
lmode6—>所有的

可以手动操作执行表的锁定模式：
Lock table tab1 in row share mode; lmode2
Lock table tab1 in row exclusive mode; lmode3
Lock table tab1 in share mode; lmode4
Lock table tab1 in share update mode; lmode4
Lock table tab1 in share row exclusive mode; lmode5
Lock table tab1 in exclusive mode; lmode6

–=====Row-S行共享锁=
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
行共享锁允许访问被锁的表，但是不允许用户以排他模式锁这个表，RS锁与S锁相同，为了兼容早期版本
测试环境：
nezhdb01:/home/oracle11g$sqlplus user1

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 18 08:41:00 2019

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from tab1;
ID NAME
---------- ------------------------------
1 a
2 b

 SQL> select distinct sid from v$mystat;
   SID
  ----------
   1

 SQL> select distinct sid from v$mystat;
   SID
  ----------
    39

Sid:1
SQL> lock table tab1 in row share mode;
Table(s) Locked.
SQL> set linesize 300 pagesize 999
SQL> select sid,type,id1,id2,decode(lmode,0,‘None’,1,‘Null’,2,‘Row share’,3,‘Row Exclusive’,4,‘Share’,5,‘Share Row Exclusive’,6,‘Exclusive’) lock_mode,
2 decode(request,0,‘None’,1,‘Null’,2,‘Row share’,3,‘Row Exclusive’,4,‘Share’,5,‘Share Row Exclusive’,6,‘Exclusive’) request_mode,block
3 from v$lock where type in (‘TM’,‘TX’);

   SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK

   1 TM      90444          0 Row share           None                         2

Sid：39
SQL> Lock table tab1 in share row exclusive mode;
Table(s) Locked.
Sid:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

00000001843FE7B8 00000001843FE810 39 AE 100 0 4 0 7229 2
00000001843FE898 00000001843FE8F0 1 AE 100 0 4 0 7404 2
00000001843FE978 00000001843FE9D0 1 TO 79833 1 3 0 7353 2
00007FB8659C0D70 00007FB8659C0DD0 1 TM 90444 0 2 0 128 2
00007FB8659C0D70 00007FB8659C0DD0 39 TM 90444 0 5 0 47 2
可以看出来与行共享排他锁没有冲突。然后rollback执行排他锁。
Sid:39
SQL> Lock table tab1 in exclusive mode;
Sid:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

00000001843FE7B8 00000001843FE810 39 AE 100 0 4 0 7595 2
00000001843FE898 00000001843FE8F0 1 AE 100 0 4 0 7770 2
00000001843FE978 00000001843FE9D0 11 TO 79833 1 3 0 7719 2
00007FB8659C0D70 00007FB8659C0DD0 11 TM 90444 0 2 0 494 1
00007FB8659C0D70 00007FB8659C0DD0 39 TM 90444 0 0 6 102 0
可以看到shared mode 锁和 exclusive 锁有冲突，sid 1阻塞了sid 39的TM 6

–=====Row-X 行独占锁
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.
行排他锁类似于行共享锁，但是也不能应用在share模式中，当update,insert,delete发生时，ROW EXCLUSIVE会自动获得。对应lmode 3

Sid:1
SQL> Lock table tab1 in share mode;
Table(s) Locked.
Sid:39
SQL> Lock table tab1 in row exclusive mode;
Sid:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

00000001843FE7B8 00000001843FE810 39 AE 100 0 4 0 9459 2
00000001843FE898 00000001843FE8F0 1 AE 100 0 4 0 9634 2
00000001843FE978 00000001843FE9D0 1 TO 79833 1 3 0 9583 2
00007FB8659ED318 00007FB8659ED378 1 TM 90444 0 4 0 20 1
00007FB8659ED318 00007FB8659ED378 39 TM 90444 0 0 3 10 0
可以看到sid 1 阻塞了 sid 39的TM 3,中断sid 39的锁命令，然后手动执行update
SQL> Lock table tab1 in row exclusive mode;
^CLock table tab1 in row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SID:39
SQL> update tab1 set name=‘zz’ where id=2;
SID:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

00000001843FE7B8 00000001843FE810 39 AE 100 0 4 0 9612 2
00000001843FE898 00000001843FE8F0 1 AE 100 0 4 0 9787 2
00000001843FE978 00000001843FE9D0 1 TO 79833 1 3 0 9736 2
00007FB8659C0D70 00007FB8659C0DD0 1 TM 90444 0 4 0 173 1
00007FB8659C0D70 00007FB8659C0DD0 39 TM 90444 0 0 3 68 0

发现update 的时候被阻塞，被SID 1 阻塞了，需求TM 3的锁

–===share锁=
SHARE SHARE permits concurrent queries but prohibits updates to the locked table.
Share允许同时查询，但是禁止更新被锁定的表，对应lmode4, share(S)
SID:1
SQL> Lock table tab1 in share mode;

Table(s) Locked.
SID:39
SQL> insert into tab1 values(3,‘c’);
SID:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E8B0 000000008418E908 1 AE 100 0 4 0 480 0
000000008418F7A8 000000008418F800 39 AE 100 0 4 0 465 0
00007FF9481B54E8 00007FF9481B5548 39 TM 87872 0 0 3 81 0
00007FF9481B54E8 00007FF9481B5548 1 TM 87872 0 4 0 123 1
中断插入操作，更改为更新和删除，查询v$lock的情况都是一样的：
SQL> insert into tab1 values(3,‘c’);
^Cinsert into tab1 values(3,‘c’)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> ^C

SQL> select * from tab1;

ID NAME

 1 a
 2 b

SQL> update tab1 set name=‘c’ where id=2;
^Cupdate tab1 set name=‘c’ where id=2
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> delete from tab1 where id=2;
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E8B0 000000008418E908 1 AE 100 0 4 0 1024 0
000000008418F7A8 000000008418F800 39 AE 100 0 4 0 1009 0
00007FF9481B54E8 00007FF9481B5548 39 TM 87872 0 0 3 8 0
00007FF9481B54E8 00007FF9481B5548 1 TM 87872 0 4 0 667 1
–=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 模式用户查看整个表，允许其他sesseion查看表中的行，但是不允许添加share模式的锁。对应lmode 5, S/Row-X
SID:1
SQL> Lock table tab1 in share row exclusive mode;

Table(s) Locked.
SID:39
SQL> lock table tab1 in share mode;
SID:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E8B0 000000008418E908 1 AE 100 0 4 0 1519 0
000000008418F7A8 000000008418F800 39 AE 100 0 4 0 1504 0
00007FF9481B6520 00007FF9481B6580 39 TM 87872 0 0 4 8 0
00007FF9481B6520 00007FF9481B6580 1 TM 87872 0 5 0 57 1
可以看到SID 1 阻塞了 SID 39 ，SID 39需求的 TM 4 的锁

–=exclusive==
EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
EXCLUSIVE模式允许查询被锁表上的数据，但是禁止任何其他任何活动。对应lmode 6 ,exclusive(X)

SID:1
SQL> Lock table tab1 in exclusive mode;

Table(s) Locked.
SID:39
SQL> update tab1 set name=‘c’ where id=1;
SID:41
SQL> select * from v$lock where sid in (1,39);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E8B0 000000008418E908 1 AE 100 0 4 0 1883 0
000000008418F7A8 000000008418F800 39 AE 100 0 4 0 1868 0
00007FF9481B54E8 00007FF9481B5548 39 TM 87872 0 0 3 8 0
00007FF9481B54E8 00007FF9481B5548 1 TM 87872 0 6 0 30 1

总结一下上面提到的堵塞的几种情况：
lmode2—>lmode6
lmode4—>lmode3
lmode5—>lmode4
lmode6—>所有的

alter index ** rebuild 的过程中会产生TM4的锁，此时如果有insert、update、delete需求lmod3的锁，会产生阻塞：(下面的实验是在索引rebuild的时候还没结束，快速执行update操作，然后查看锁的情况)
SID:1
SQL> insert into tab1 select * from tab1;
262144 rows created.

SQL> SQL>
SQL> commit;

Commit complete.
SQL> alter index IDX_ID rebuild;

SID:48
SQL> update tab1 set name=‘z’ where id=2;

SID:2
SQL> select * from v$lock where sid in (1,48);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E1B0 000000008418E208 48 TO 87456 1 3 0 1756 0
000000008418E8B0 000000008418E908 1 AE 100 0 4 0 4062 0
000000008418F428 000000008418F480 1 TO 87456 2 3 0 1 0
000000008418F508 000000008418F560 1 TO 79833 1 3 0 219 0
000000008418F5E8 000000008418F640 1 TO 87460 1 3 0 555 0
000000008418F6C8 000000008418F720 1 TO 87456 1 3 0 554 0
000000008418F888 000000008418F8E0 1 DL 87872 0 3 0 1 0
000000008418FB40 000000008418FB98 48 TO 87460 1 3 0 1757 0
000000008418FC20 000000008418FC78 1 OD 87881 0 6 0 1 0
000000008418FD00 000000008418FD58 48 AE 100 0 4 0 3991 0
000000008418FEC0 000000008418FF18 1 DL 87872 0 3 0 1 0
00007F32DB8742A8 00007F32DB874308 48 TM 87872 0 0 3 0 0
00007F32DB8742A8 00007F32DB874308 1 TM 87872 0 4 0 1 1
00007F32DB8742A8 00007F32DB874308 1 TM 87439 0 3 0 1 0
00007F32DB8742A8 00007F32DB874308 1 TM 87470 0 3 0 1 0
00007F32DB8742A8 00007F32DB874308 1 TM 87466 0 3 0 1 0
00007F32DB8742A8 00007F32DB874308 1 TM 87456 0 3 0 1 0
0000000082A465D0 0000000082A46648 1 TX 131100 1183 6 0 1 0
可以看到SID 48需求lmod3 的锁，被阻塞，等索引建完后，释放了TM4 的锁，update就抢到了lmod 3 的锁和lmod 6的锁：
SQL> /

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E1B0 000000008418E208 48 TO 87456 1 3 0 1769 0
000000008418E8B0 000000008418E908 1 AE 100 0 4 0 4075 0
000000008418F348 000000008418F3A0 1 RO 65555 1 5 6 2 0
000000008418F428 000000008418F480 1 TS 4 16777434 6 0 2 0
000000008418F508 000000008418F560 1 TO 79833 1 3 0 232 0
000000008418F5E8 000000008418F640 1 TO 87460 1 3 0 568 0
000000008418F6C8 000000008418F720 1 TO 87456 1 3 0 567 0
000000008418F888 000000008418F8E0 1 TT 4 16 4 0 2 0
000000008418FB40 000000008418FB98 48 TO 87460 1 3 0 1770 0
000000008418FD00 000000008418FD58 48 AE 100 0 4 0 4004 0
000000008418FEC0 000000008418FF18 1 TS 4 16777435 6 0 2 0
0000000084190160 00000000841901B8 1 RO 65555 2 1 0 2 0
00007F32DB8752E0 00007F32DB875340 48 TM 87872 0 3 0 2 0
00000000829DB308 00000000829DB380 48 TX 655389 964 6 0 2 0

14 rows selected.

SQL> /

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

000000008418E1B0 000000008418E208 48 TO 87456 1 3 0 2182 0
000000008418E8B0 000000008418E908 1 AE 100 0 4 0 4488 0
000000008418F508 000000008418F560 1 TO 79833 1 3 0 645 0
000000008418F5E8 000000008418F640 1 TO 87460 1 3 0 981 0
000000008418F6C8 000000008418F720 1 TO 87456 1 3 0 980 0
000000008418FB40 000000008418FB98 48 TO 87460 1 3 0 2183 0
000000008418FD00 000000008418FD58 48 AE 100 0 4 0 4417 0
00007F32DB8742A8 00007F32DB874308 48 TM 87872 0 3 0 415 0
00000000829DB308 00000000829DB380 48 TX 655389 964 6 0 415 0

9 rows selected.