欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

TM表锁各种mode的实验及2

程序员文章站 2022-05-22 17:18:38
...

TM 表锁,发生在insert,update,delete以及select for update操作时,目的是保证操作能够正常进行,并且阻止其它人对表执行DDL操作。 TM锁几种模式的互斥关系图: 在巡检期间,检查如果发现有相关的session_wait锁,可按以下步骤查找相应的锁 1. 使用V$LOCK找


TM 表锁,发生在insert,update,delete以及select for update操作时,目的是保证操作能够正常进行,并且阻止其它人对表执行DDL操作。

TM锁几种模式的互斥关系图:

TM表锁各种mode的实验及2

在巡检期间,检查如果发现有相关的session_wait锁,可按以下步骤查找相应的锁

1. 使用V$LOCK找出session持有的锁。

2. 使用V$SESSION找出持有锁或等待锁的session执行的sql语句。

3. 使用V$SESSION_WAIT找出什么原因导致session持有锁堵塞。

4. 使用V$SESSION获取关于持有锁的程序和用户的更多信息。

V$LOCK中的常用列

SID:表示持有锁的会话信息。

TYPE:表示锁的类型。值包括TM和TX等。

LMODE:表示会话等待的锁模式的信息。用数字0-6表示,和表1相对应。

REQUEST:表示session请求的锁模式的信息。

ID1,ID2:表示锁的对象标识。

例如:首先查出当前TM','TX等待事件的等待会话

BYS@ bys3>select a.sid,a.kaddr,a.addr,a.type,a.id1,a.id2,a.lmode,a.request,a.block from v$lock a where a.type in('TM','TX') order by 1,2;
SID KADDR ADDR TY ID1 ID2 LMODE REQUEST BLOCK
---------- -------- -------- -- ---------- ---------- ---------- ---------- ----------
1 B693D590 B693D560 TM 22326 0 0 3 0
44 B693D590 B693D560 TM 22326 0 5 0
BYS@ bys3>select sql_id from v$session where sid=1; 通过上一步会话SID,查出1号会话当前在等待的语句
SQL_ID
-------------
01mv5v746cfbq
BYS@ bys3>select * from v$sqltext where sql_id='01mv5v746cfbq'; --通过上一步查出SQL_SID,查出SQL_TEXT
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ------------------------------
25DDD5FC 3362142582 01mv5v746cfbq 6 0 update test set status=8899

用一条语句来查询是:select a.*,b.sid,b.username from v$sqltext a,v$session b,v$lock c where a.sql_id=b.sql_id and b.sid=c.sid and c.type in('TM','TX');

详见:v$session/v$process视图涉及的相关会话信息的查询

1.环境准备:--有些实验数据是后来补充的,可能不太一致。
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>insert into test values(1);
1 row created.
BYS@ bys001>commit;
Commit complete.
BYS@ bys001>select * from test;
A
----------
1
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;

no rows selected

TM锁1号锁实验:

create table order_info(customerid number);

CREATE OR REPLACE PROCEDURE kzcz214001 (cust_id in NUMBER)
AS
BEGIN
insert into order_info values(cust_id);
commit;
END;
/
col owner for a10
col name for a10
col type for a10
select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
这一句查询dba_ddl_locks表,如果手工建库,dba_ddl_locks表可能不存在,需要SYS用户运行脚本去创建:?/rdbms/admin/catblock.sql
exec kzcz214001(1);
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ---------- ---------- --------- ---------
1 BYS KZCZ214001 Table/Proc Null None
edure/Type
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
VALID

BYS@ bys3>alter table order_info add(name varchar2(10));
Table altered.
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
INVALID
索引失效后,1号锁还能查到。。。
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from dba_ddl_locks WHERE NAME='KZCZ214001';
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ---------- ---------- --------- ---------
1 BYS KZCZ214001 Table/Proc Null None
edure/Type

####################################

等级共享锁 2 row share----rs

产生2号TM锁方法1:--手动锁定

把在19会话将test表设置为等级共享锁模式

BYS@ bys001>lock table test in row share mode;
Table(s) Locked.
可以查询到是在test表加了个模式2的锁
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
BYS@ bys001>select object_name from dba_objects where object_id=77389;
OBJECT_NAME
-------------------------------
TEST
在另一个会话147上做DML操作:
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>insert into test values(2);
1 row created.
BYS@ bys001>delete from test where a=1;
1 row deleted.
BYS@ bys001>select * from test;
A
----------
2
BYS@ bys001>update test set a=22 where a=2;
1 row updated.
BYS@ bys001>select * from test;
A
----------
22
在会话19上查询:
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
147 TM 77389 0 3 0 0

147 TX 196621 28393 6 0 0

如果使用DML语句,会报错:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

产生2号TM锁方法2:--大表在线建索引

在会话1:--在大表上在线创建索引,要用ONLINE,不用ONLINE不会有2号锁。
BYS@ bys3>create index idx_test1 on test1(id) online;
Index created.
会话2:--有4号锁和2号锁
BYS@ bys3>select * from v$lock where sid=1;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2AD66694 2AD666C0 1 AE 100 0 4 0 2154 0
2AD6697C 2AD669A8 1 DL 22325 0 3 0 4 0
2AD66AF0 2AD66B1C 1 OD 22325 0 4 0 4 0
2AD66C64 2AD66C90 1 DL 22325 0 3 0 4 0
B6A0DB98 B6A0DBC8 1 TM 22325 0 2 0 4 0
B6A0DB98 B6A0DBC8 1 TM 22354 0 4 0 4 0
2926D618 2926D690 1 TS 4 16777362 6 0 2 0
28471AE4 28471B24 1 TX 327696 2557 6 0
BYS@ bys3>col object_name for a20
BYS@ bys3>select a.sid,a.type,a.lmode,a.request,b.object_name from v$lock a,dba_objects b where a.id1=b.object_id and sid=1;
SID TY LMODE REQUEST OBJECT_NAME
---------- -- ---------- ---------- --------------------
1 TM 2 0 TEST1
1 TM 4 0 SYS_JOURNAL_22360
1 AE 4 0 ORA$BASE
1 OD 4 0 TEST1
1 DL 3 0 TEST1
1 DL 3 0 TEST1

小结:行级共享锁,是限制最少的TM锁,可以提供最高程度的并发性。其他会话可以对锁定的表进行任何类型的DML操作,还可以与其他会话锁并存。
################################################################################

等级排他锁 row exclusive table lock---RX 3

BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>lock table test in row exclusive mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
3级锁是一个灵活性比较大的锁,insert delete update,for update 都可以产生一个3级锁,也允许其他事务来修改锁定的表
BYS@ bys001>select * from test;
A
----------
22
BYS@ bys001>update test set a=99 where a=22;
1 row updated.
BYS@ bys001>select * from test;
A
----------
99
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>insert into test values(55);
1 row created.
BYS@ bys001>select * from test;
A
----------
22
55
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
147 TM 77389 0 3 0 0

147 TX 589824 28423 6 0 0

block列全部是0,没有阻塞。

此时,如执行DDL语句,报错:ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired

小结:行级排他锁,通常已经有事务在修改行或者select…for update 修改结果集。允许其他事务对锁定的表进行select insert update delete 或 lock table 同时锁定一张表。

#####################################################################################

共享锁 share table lock 4

BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>select * from test;
A
----------
99
55
BYS@ bys001>lock table test in row share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
模式标识:4
4级锁禁止其他会话对锁定的表进行DML操作但可以select查询,还允许多个事物一起持有
在会话147上进行删除操作,

BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>DELETE TEST ;


BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 1
147 TM 77389 0 3 0 0
147 TX 655390 28214 0 6 0
BYS@ bys001>lock table test in share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 0
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 1
147 TM 77389 0 0 3 0

共享锁,其它事务可以查询锁定的表但不能修改,只允许当前事务修改,但可以多个事务持有它。

另一个产生4号锁的,见: --2号锁在线建索引实验。

##########################################################

TM锁5号锁实验:

BYS@ bys001>lock table test in share row exclusive mode;

Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
BYS@ bys001>insert into test values(88);
1 row created.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
19 TX 524320 28676 6 0 0
在147会话执行DML操作会hang住
BYS@ bys001>insert into test values(88);
此时查询
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 1
19 TX 524320 28676 6 0 0
147 TM 77389 0 0 3 0

小结:共享行级排他锁,同一时间只允许一个事务持有和修改锁定的表,其他事务可以查询但不能修改。

5号锁--在存储过程运行中会出现:A B两个会话同时执行同一个存储过程时

session A exec p
session B exec p
#############################################

TM锁6号锁实验:

BYS@ bys001>lock table test in exclusive mode;

Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 6 0 0
在会话147进行hang住
BYS@ bys001>lock table test in row share mode; ---在此HANG住

BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 6 0 1
147 TM 77389 0 0 2 0