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

Oracle锁实验

程序员文章站 2022-04-30 16:28:04
...

MASICONG@orclselectsidfromv$mystatwhererownum=1;SID----------35MASICONG@orclselectsidfromv$mystatwhererownum=1;SID---..

MASICONG@orcl> select sid from v$mystat where rownum=1;

SID

----------

35

MASICONG@orcl> select sid from v$mystat where rownum=1;

SID

----------

1

1.分别模拟insert,update和delete造成阻塞的示例,并对v$lock中的相应的信息进行说明,给出SQL演示。

MASICONG@orcl> create table test (id varchar(2) primary key);

Table created.

INsert阻塞

MASICONG@orcl> insert into test values (10);

1 row created.

MASICONG@orcl> insert into test values(10); 另一个用户提交同样的请求就会卡住

MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 TX 131090 674 0 4 0

35 TM 74571 0 3 0 0

1 TM 74571 0 3 0 0

1 TX 65540 508 6 0 0

35 TX 131090 674 6 0 1

说明1会话需要一个4级锁,网站空间,但是35已经加了一个6级的锁阻塞了会话。因为会话1已经插入了一条记录,所以又一个TX锁可以通过。

MASICONG@orcl> select object_name from dba_objects where object_id=74571;

OBJECT_NAME

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

TEST

通过TM知道目前是在一个表上加的锁,所有通过ID可以查到具体操作的是哪个表。

MASICONG@orcl> commit;

Commit complete.

MASICONG@orcl> insert into test values(10); 当一个会话提交后,另一个会话才能访问

*

ERROR at line 1:

ORA-00001: unique constraint (MASICONG.SYS_C0011055) violated

Update阻塞

MASICONG@orcl> commit

2 ;

Commit complete.

MASICONG@orcl> select * from test;

ID

--

1

10

MASICONG@orcl> update test set id=2 where id =1;

1 row updated.

MASICONG@orcl> update test set id=2 where id =1; 另一回话处于Block状态

MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

1 TX 458775 552 0 6 0

1 TM 74571 0 3 0 0

35 TM 74571 0 3 0 0

35 TX 458775 552 6 0 1

显示出现了阻塞信息,会话在等待一个6级的锁

MASICONG@orcl> select sid,event from v$session_wait where sid in (1,35);

SID EVENT

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

1 enq: TX - row lock contention

35 SQL*Net message from client

上面标示1会话需要TX锁

DELETE操作

MASICONG@orcl> delete from test where id=10;

1 row deleted.

MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;

SID TY ID1 ID2 LMODE REQUEST BLOCK

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

相关标签: Oracle 实验