Oracle-OCP学习笔记:锁 latch,lock(上)

原理:

     SGA(BUFFER CACHE,LOG BUFFER,SHARED POOL),所有的进程都可以同时访问SGA,后台进程很少10个多,,server process进程很多,可以达到上100个,oracle数据库的并发性,主要讲server process的并发。所有讨论锁,也就是研究server process的锁.

   

 

1、Oracle锁类型
    
锁的作用
    latch锁(与性能有关系):保护chain,链 (serverprocess会并发访问这些链,要通过latch锁来进行保护)

                SGA中的链会被并发访问,这个server process访问链时,会获取链上的latch锁,访问完成后会释放latch锁.

                  Latch争用:表示一个链被多个server process访问,出现了热块。需要排错。


    LOCK
锁 (用来保护buffer,数据块(行)),最小粒度单位是行
        排他锁(X) 一个资源只能有一个排他锁
        共享锁(S) 一个资源可以有多个共享锁


2
、行级锁:DML语句
    
事务锁TX :一个事务只能产生一个事务锁,事务锁是因为行级锁产生的。使用的最多。
        锁的结构
        事务锁的加锁和解锁过程

                     加锁:DML语句

                     解锁:rollback,commit

              查看系统是否有事务:

                     SYS AS SYSDBA@ORCL>selectxidusn,xidslot,xidsqn,status from v$transaction;
    
只有排他锁
        不影响读(CR块)


3
、表级锁:TM
          
行级排他锁(Row exclusive)RX锁这种锁数量最多,每个事务修改表时都会产生RX,RX锁之间是可以共存的。

A事务要修改表中的行,会在被修改的行上加一个行级锁X,可以有多个行级锁X,同时这个事务A会产生一个事务锁TX,事务A还在表上加一个表级锁,这个锁叫RX锁
        当我们进行DML时,会自动在被更新的表上添加RX锁,可以执行LOCK命令显式的在表上添加RX锁。RX锁产生的原因是因为DML对表中的数据进行了修改,RX锁是表级锁。
        允许其他事务通过DML语句修改相同表里的其他数据行
        允许使用lock命令对表添加RX锁定
        不允许其他事务对表添加X锁


          
行级共享锁(Row Shared,简称RS锁)平时使用最多
        select … from for update

              这个语句会产生事务锁TX,在表上加RS锁。当一个表上有加RS锁时,不能再在这个表上加RS锁,一个表同时只能有一个select … from for update,不能有多个。RS锁与RS锁之间的互斥的。

 

共享锁(Share,简称S锁)
        
通过lock table in share mode命令添加该S锁
    排他锁(Exclusive,简称TX锁)  平时使用最多
        通过lock table inexclusive mode命令添加X锁

作用:对表结构进行修改或删除整张表。加锁后,任何用户都不能修改表中的数据。


    
共享行级排他锁(ShareRow Exclusive,简称SRX锁)
        通过lock table inshare row exclusive mode命令添加SRX锁

4、锁的兼容性

兼容:一个资源上可以加不同的锁,锁之后可以共存

不兼容:一个资源上,不同的锁不能共存。是互斥的。



 

 

5、加锁语句以及锁的释放

 

lock table  in [row share][row exclusive][share][share row exclusive][exclusive]mode;

                 RS锁      RX锁        S锁        SRX锁            X锁

锁的释放

解锁:rollback,commit

 

使用不同锁的原则:

       要锁什么资源,锁的粒度多大,再选择锁的类型。

 

6、锁相关视图
    v$transaction (
未提交活动事务的信息)
        XIDUSN表示当前事务使用的回滚段的编号
        XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号(也可以叫做槽号)
        XIDSQN说明序列号
        STATUS说明该事务是否为活动的

HR @ORCL>select* from rsc;

    ID NAME

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

     1 GMY

     2 12341234

     3 9988

    10 123

Elapsed: 00:00:00.00

HR @ORCL>insert into rsc values (11,'567');

开另一个会话查看事务情况:

HR @ORCL>select xidusn,xidslot,xidsqn,status fromv$transaction;

    XIDUSN    XIDSLOT      XIDSQN STATUS

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

     3          19        2674    ACTIVE

 


    v$lock
        
记录了session已经获得的锁定以及正在请求的锁定的信息
        SID说明session的ID号
        TYPE说明锁定锁定级别,主要关注TX和TM
        LMODE说明已经获得的锁定的模式,以数字编码表示
        REQUEST_MODE说明正在请求的锁定的模式,以数字编码表示
        BLOCK说明是否阻止了其他用户获得锁定,大于0说明是,等于0说明否,表示会话持有这个锁,但没有影响其它的事务。没有锁定其它会话。

              ID1:TM时ID1表示那个对象,TX:表示对应那个事务

              ID2: TX:表示对应那个事务

              LOCK_MODE:这个会话持有的锁

             

查看当前会话的编号:

HR @ORCL>select sid from v$mystat where rownum=1;

       SID

----------

    14

在当前会话执行下面的语句:

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,  

decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

block fromv$lock where sid=14;

       SID TYPE             ID1  ID2 LOCK_MODE

REQUEST_MODE                                           BLOCK

    14 AE               100    0 Share

None                                               0

 

   14 TM            81944       0 Row Exclusive      #表级锁

None                                               0

 

   14 TX           327689   2681 Exclusive       #排它锁

None                                               0

81944:代表被锁表的编号,通过以下语句可以查到锁定了那张表:

select object_name fromdba_objects where object_id=81944;

HR @ORCL>select object_name from dba_objects whereobject_id=81944;

OBJECT_NAME

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

RSC

将ID1拆解,可以查看对应的回滚段和槽位号:

HR @ORCL>select trunc(327689/power(2,16)) asundo_blk#,bitand(327689,to_number('ffff','xxxx')) + 0 as slot# from dual;

 UNDO_BLK#SLOT#

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

     5      9

 

对于TM锁来说,ID1表示被锁定的对象的对象ID,ID2始终为0
    对于TX锁来说,ID1表示事务使用的回滚段编号以及在事务表中对应的记录编号,ID2表示该记录编号被重用的次数(wrap)

    
 
    
锁定模式       锁定简称    编码数值  
    Row Exclusive        RX        3  
    Row Shared        RS        2  
    Share             S         4  
    Exclusive         X        6  
    Share Row Exclusive     SRX        5  
    NULL             N/A         0或者1  
 
    v$enqueue_lock (列出请求的锁)
    该视图中包含的字段以及字段含义与v$lock中的字段一模一样。
    只不过该视图中只显示那些申请锁定,但是无法获得锁定的session信息。
    其中的记录按照申请锁定的时间先后顺序排列,先申请锁定的session排在前面,排在前面的session将会先获得锁定。
 
    v$locked_object
    记录了当前已经被锁定的对象的信息
    XIDUSN表示当前事务使用的回滚段的编号
    XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号
    XIDSQN说明序列号
    OBJECT_ID说明当前被锁定的对象的ID号,可以根据该ID号到dba_objects里查找被锁定

 

 

的对象名称
    LOCKED_MODE说明锁定模式的数字编码
 
    v$session
    记录了当前session的相关信息
    SID表示session的编号
    SERIAL#表示序列号
    SID和SERIAL#可以认为是v$session的主键,它们共同唯一标识一个session

Sqlplus / as sysdba

grant select on v_$mystat to hr;  #通过sys授权select 权限

 

事务与锁的关系:

查看当前会话的SID:

HR @ORCL>select sid from v$mystat where rownum=1;

       SID

----------

        14

update employees set last_name=last_name||'a' wheredepartment_id=60; #更新事务

查看事务记录:(所有会话上的运行结果一样)

HR @ORCL>select xidusn,xidslot,xidsqn,status fromv$transaction;

    XIDUSN    XIDSLOT      XIDSQN STATUS

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

    10     21      2090ACTIVE

查看当前事务所产生的锁:

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,  

decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

block fromv$lock where sid=14;

      SID TYPE             ID1  ID2 LOCK_MODE

REQUEST_MODE                                           BLOCK

    14AE                100    0 Share

None                                               0

 

    14TM              73933       0 Row Exclusive      #73933表示表锁对应的表名对应的编号

None                                               0

 

    14TX             655381   2090 Exclusive         #对应锁的回滚段以及事务槽

None  

 

 

再次打开一个会话

查看这个会话的ID:

select sid from v$mystat where rownum=1;

       SID

----------

        26

update employees set last_name=last_name||'c' wheredepartment_id=60;#这时会话被锁住

 

查询v$lock来获得锁定队列中的session信息

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,  

decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

block fromv$lock where sid in (14,26) order by sid;

      SID TYPE             ID1  ID2 LOCK_MODE

REQUEST_MODE                                           BLOCK

    14 AE               100    0 Share

None                                               0

 

    14 TM             73933       0 Row Exclusive

None                                               0

 

    14 TX            655381 2090 Exclusive

None                                               1  #1表示这个事务阻塞了事务26

 

    26 TM             73933       0 Row Exclusive

None                                               0

 

    26 AE               100    0 Share

None                                               0

 

    26 TX            655381 2090 None

Exclusive                                         0

 

事务26被事务14上面的锁锁住了,26事务请求14上面的锁,

 

ID1:655381 :事务14与事务26有同样的ID1值, 这时这两个事务请求同一个源码,产生了死锁

 

 

 

再打开一个会话:

查看这个会话的ID:

HR @ORCL>select sid from v$mystat whererownum=1;

      SID

----------

      150

又执行同一条语句:

update employees set last_name=last_name||'c' wheredepartment_id=60;#这时会话被锁住

 

查询v$enqueue_lock来获取锁定队列中的session信息:(显示请求锁的信息,这里面的事务被其它事务锁住了)

select sid,type,

decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode

from v$enqueue_lock

where sid in(150,26);

       SID TYPE      REQUEST_MODE

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

    26 AE         None

   26 TX       Exclusive

       150 AE        None

       150 TX      Exclusive

 

查看会话与锁详细情况:

select a.sid blocker_sid, a.serial#, a.username as blocker_username, b.type,

decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,

b.ctime as time_held,c.sid as waiter_sid,

decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

c.ctime time_waited 

from   v$lock b, v$enqueue_lock c,v$session a 

where  a.sid = b.sidand   b.id1= c.id1(+)and b.id2 =c.id2(+) andc.type(+) = 'TX' and  b.type = 'TX' and  b.block  = 1

order by time_held, time_waited;

BLOCKER_SID   SERIAL# BLOCKER_USERNAME      TYPE

LOCK_MODE                                       TIME_HELD WAITER_SID

REQUEST_MODE                                     TIME_WAITED

14               23           HR                TX

Exclusive                                       2001      150

Exclusive                                       389

 

14               23           HR                TX

Exclusive                                        2001       26

Exclusive                                        1291

 

解析:

    会话14,编号是23,通过HR模式登录后,执行了一条SQL语句 。会话150也执行了同一个SQL语句,因为会话14还没有提交,所有会话150等待会话14提交,会话14持有的时间是2001皮秒,会话150等待的时间是389皮秒。

   会话26也在等待会话14提交,会话16等待的时间是1291皮秒。

    从上面可以看出当会话14提交后,会话16会首先获取会话14的资源。

 

当产生锁等待时,kill相对应的会话:

alter system kill session '14,23';

 

当KILL 会话14时,会话26会执行成功,但150还是会被锁,这说明锁是串行的。

 

 

一个事务修改多行,产生一个TX锁

HR @ORCL>select sid from v$mystat whererownum=1;

      SID

----------

         9

update employees set last_name=last_name||'a' wheredepartment_id=60;

update departments set department_name='unknow' where department_id=10;

update locations set city='unknown' wherelocation_id=1100

 

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,

decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block

from v$lock

where sid=9;  

SID TYPE                      ID1       ID2 LOCK_MODE

REQUEST_MODE                                                                                      BLOCK

         9 AE                         100         0 Share

None                                                                                           0

 

         9 TM                       73933         0 Row Exclusive

None                                                                                           0

 

         9 TM                       73928         0 Row Exclusive

None                                                                                           0

 

         9 TM                       73923         0 Row Exclusive

None                         &

YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS