Home > Database > Mysql Tutorial > Oracle锁实验

Oracle锁实验

WBOY
Release: 2016-06-07 17:41:02
Original
1131 people have browsed it

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

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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template