暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片

错过血亏!一文搞懂Oracle锁相关视图及相关操作

新站 2019-05-15
2066

本文主要研究锁的相关视图v$transaction、v$lock、v$enqueue_lock、v$locked_object、v$session视图transactionsdml_locks参数介绍死锁的产生及避免一个事务多个TM锁、锁的时间、三个事务的锁争用、两个事务间锁争用实例以及锁的相关操作,通过视图查锁的问题。

一、v$transaction视图

第一个视图是v$transaction,就是Oracle数据库所有活动的事务数,所有活动的事务每一个活动的事务在这里有一行。

  • v$transaction

  • XIDUSN表示当前事务使用的回滚段的编号

  • XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号(也可以叫做槽号)

  • XIDSQN说明序列号

  • STATUS说明该事务是否为活动的

这是v$transaction视图的结构,这里做一个实验

在一个hr用户的sqlplus中执行

    SQL> delete from employees where rownum=1;


    1 row deleted.

    我删一行,开始一个事务,以管理员身份运行,去执行一下

      select xidusn,xidslot,xidsqn,status from v$transaction;

      看看有几个事务

      结果:

        SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
        XIDUSN XIDSLOT XIDSQN STATUS
        ---------- ---------- ---------- ----------------
        4 43 216 ACTIVE

        一个事务在这里面有一行

          XIDUSN是事务使用的回滚段的编号
          XIDSLOT是哪个槽位数
          XIDSQN是覆盖多少次
          这三个唯一的标示一个事务的编号

          STATUS是当前事务的状态,这个事务为ACTIVE;这是v$transaction,所有的活动事务里面都有


          二、v$lock视图


            • v$lock
            • 记录了session已经获得的锁定以及正在请求的锁定的信息
            • SID说明session的ID号
            • TYPE说明锁的类型,主要关注TX和TM
            • LMODE说明已经获得的锁定的模式,以数字编码表示
            • REQUEST说明正在请求的锁定的模式,以数字编码表示
            • BLOCK说明是否阻止了其他用户获得锁定,大于0说明是,等于0说明否

            v$lock这里面,记录了session已经获得的锁定以及正在请求的锁定的信息,就是每个会话,它已经获取的锁和正在申请的锁它都会列出来

            上面执行了

              delete from employees where rownum=1;

              一个事务开始以后至少产生几个锁,第一个行上加锁了,行上的锁你是看不见的,因为它在行上,但是我们开始一个事务有一个事务锁,同时在表上应该加了个RX锁,应该这时候有两个锁,一个TX锁事务锁,一个是TM级别上的表级的RX锁。

              使用语句

                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=129;

                这里把它取名为(#A)语句,最后where条件有sid,sid是会话的编号

                先查一下我们会话的编号是多少,在我的实验环境下,在hr用户的sqlplus中

                  SQL> select sid from v$mystat where rownum=1;
                  select sid from v$mystat where rownum=1
                  *
                  ERROR at line 1:
                  ORA-00942: table or view does not exist

                  hr用户无权访问v$mystat,所以换个方法,使用下面的命令得到hr用户当前的sid

                    SQL> select userenv('sid') from dual;


                    USERENV('SID')
                    --------------
                    132

                    得到SID是132

                    然后用132替换前面(#A)语句where条件下的sid的值,然后在管理员用户下查一下,因为hr用户依然无法访问v$lock。

                    执行结果

                      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,
                      decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
                      2 3 from v$lock
                      4 where sid=132;


                      SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
                      ---------- -- ---------- ---------- ------------------- ------------------- ----------
                      132 TM 51852 0 Row share None 0
                      132 TM 51855 0 Row share None 0
                      132 TM 51857 0 Row Exclusive None 0
                      132 TM 51864 0 Row share None 0
                      132 TM 51871 0 Row share None 0
                      132 TM 51889 0 Row share None 0
                      132 TM 51894 0 Row share None 0
                      132 TM 51902 0 Row share None 0
                      132 TX 262187 216 Exclusive None 0


                      9 rows selected.

                      我们看一下132这个会话在很多的表上产生了TM锁,132这个会话至少底下产生了一个TX锁,同时132产生了TM锁,LOCK_MODE中是Row share说明是RS锁,是select for update产生的锁;132这个会话产生的TM锁的ID1列的ID数,这个TM在某个表上产生的锁,ID1就是这个表的编号

                      有一个是51902,我们可以根据51902查出来

                        select object_name from dba_objects where object_id=51902;

                        查出51902是哪个表,执行结果

                          SQL> select object_name from dba_objects where object_id=51902;


                          OBJECT_NAME
                          -------------------------------------------------------------------------------------------------------
                          PRODUCT_INFORMATION

                          对象编号51902是PRODUCT_INFORMATION表,说明我们找错了,这个表上加的锁是Row share类型的锁删除操作的表应该产生Row Exclusive类型的锁,前面delete语句删除EMPLOYEES表中行时牵涉到了PRODUCT_INFORMATION这个表,是主外键约束关系的原因在PRODUCT_INFORMATION表产生了RS锁。

                          再看刚才的结果

                            132 TM      51857          0 Row Exclusive       None                         0

                            锁应该是RX锁,TM级别的RX锁,是51857

                            把语句改为

                              select object_name from dba_objects where object_id=51857;

                              改一下,执行一下,看一下

                                SQL> select object_name from dba_objects where object_id=51857;


                                OBJECT_NAME
                                ----------------------------------------------------------------------------------------------------
                                EMPLOYEES

                                结果是EMPLOYEES,是对的。所以这个TM这个表级锁在哪个表上,根据ID1对应的ID可以找出来;另外结果中TX所在的行,有ID1对应的ID和ID2列对应的ID,ID1和ID2这两个数字标示着这个事务用的那个回滚段、事务表里面的槽位号还有覆盖次数,我们可以通过一个sql语句查出来

                                将ID1拆解

                                  select trunc(393249/power(2,16)) as undo_blk#,bitand(393249,to_number('ffff','xxxx')) + 0 as slot#
                                  from dual;

                                  刚才delete语句产生的TX锁是这一行

                                    132 TX 262187 216 Exclusive None 0

                                    ID1列的值262187替换上面的语句中的值393249

                                    得到

                                      select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#
                                      from dual;

                                      执行一下

                                        SQL> select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number('ffff','xxxx')) + 0 as slot#
                                        from dual; 2


                                        UNDO_BLK# SLOT#
                                        ---------- ----------
                                        4 43

                                        看结果是4号回滚段,回滚段事务表中槽位号SLOT#是43

                                        和以前查询结果是一样的

                                          SQL> select xidusn,xidslot,xidsqn,status from v$transaction;


                                          XIDUSN XIDSLOT XIDSQN STATUS
                                          ---------- ---------- ---------- ----------------
                                          4 43 216 ACTIVE

                                          刚才这个语句XIDSQN的值216

                                          (#A)语句结果行

                                            132 TX     262187        216 Exclusive           None                         0

                                            直接有了

                                            (#A)语句结果中ID2是覆盖次数

                                            通过这个sql语句

                                              select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#
                                              from dual; 2

                                              找出来用的哪个回滚段、槽位号是多少、覆盖次数这三个信息

                                              也就是(#A)语句结果中TX这一行,ID1列和ID2列可以找到哪个事务

                                              行是TM锁

                                                132 TM 51857 0 Row Exclusive None 0

                                                ID1对应的编号51857是对象的编号,这是v$lock

                                                Oracle中的表级锁有

                                                  锁定模式   锁定简称   编码数值
                                                  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$lock视图里面是用编号给列出来了,如编号3对应Row Exclusive锁模式,并且v$lock持有锁和申请锁的都列出来了,我们查询以后得出的结论,SID为132的LOCK_MODE它是持有锁的模式。

                                                  (#A)结果中SID为132的行有9行,它是持有这么多的锁,REQUEST_MODE都是None,请求这块是none

                                                  我们看(#A)结果中这些列,SID是session id,Type是类型,有表级锁TM和事务锁TX,对于ID,对TM来讲ID1是哪个对象,对于TX来讲ID1和ID2分别对应哪个事务。

                                                  LOCK_MODE是SID会话持有的锁,它持有这个锁,REQUEST_MODE这个是请求什么锁,这里我请求都是None,没有请求任何锁,同时,BLOCK这个数字是0或者1,其中:这个0表示,比如说SID为132的会话持有LOCK_MODE为Row share的锁,这个锁并没有把其它的别人锁住,BLOCK就为0;BLOCK如果是1的话,132持有这个锁同时还锁住了别人到底锁住了谁可以去查。

                                                  BLOCK是这个意思,这里结果中block都是0,也就是对132来讲,我持有这么多的锁,但是没有锁其它的任何人,也就是132持有的锁没有对别人造成影响。


                                                  三、v$enqueue_lock视图


                                                  v$enqueue_lock该视图中包含的字段以及字段含义与v$lock中的字段一模一样。只不过该视图中只显示那些申请锁定,但是无法获得锁定的session信息。其中的记录按照申请锁定的时间先后顺序排列,先申请锁定的session排在前面,排在前面的session将会先获得锁定。

                                                  我们接着看v$enqueue_lock,vlock是把会话持有的锁和请求的锁全给列出来了,v$enqueue_lock只是把请求锁的都列出来了,v$enqueue_lock只是把哪些会话它正在请求锁,它把请求的列出来了,它持有锁它没列;因为对我们来讲,有时候我们只关心谁在请求锁,因为请求锁就有可能被锁住,但有时候我们并不关心持有锁


                                                  四、v$locked_object视图


                                                  • v$locked_object记录了当前已经被锁定的对象的信息

                                                  • XIDUSN表示当前事务使用的回滚段的编号

                                                  • XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号

                                                  • XIDSQN说明序列号

                                                  • OBJECT_ID说明当前被锁定的对象的ID号,可以根据该ID号到dba_objects里查找被锁定的对象名称

                                                  • LOCKED_MODE说明锁定模式的数字编码

                                                  • v$locked_object记录了当前已经被锁定的对象的信息,哪些对象被锁定了

                                                  • XIDUSN、XIDSLOT、XIDSQN是锁这些对象的事务信息

                                                  • OBJECT_ID是哪个对象被锁住了

                                                  • LOCKED_MODE是锁的模式是什么,用什么方式锁了,比如某个表被锁住的话这里面可以查出来


                                                  五、v$session视图


                                                  • v$session记录了当前session的相关信息

                                                  • SID表示session的编号

                                                  • SERIAL#表示序列号

                                                  • SID和SERIAL#可以认为是v$session的主键,它们共同唯一标识一个session

                                                  记录的是会话信息,通过SID和SERIAL#,它俩可以唯一的标示一个会话


                                                  六、两个事务间锁争用实例


                                                  1)两个事务争用锁

                                                  我们执行一个update employees set last_name=last_name||’a’ where department_id=60;开始一个事务,开始实验,在其中一个sqlplus中执行

                                                    SQL> update employees set last_name=last_name||'a' where department_id=60;
                                                    5 rows updated.

                                                    之前提过,这个事务一旦开始以后伴随着一堆的锁,执行这个语句的SID是139

                                                    我们先查一下和事务相关的

                                                      select xidusn,xidslot,xidsqn,status from v$transaction;

                                                      刚才已经开始一个事务了,使用sys用户看一下有多少事务,因为刚开始一个事务,它是active的没有提交,它在v$transaction里面一定会出现

                                                        SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
                                                        XIDUSN XIDSLOT XIDSQN STATUS
                                                        ---------- ---------- ---------- ----------------
                                                        7 23 238 ACTIVE

                                                        这就是刚才我们的事务,然后我们可以去查一下,刚才在139开始的一个事务,sys使用下面的语句查一下

                                                        查询结果

                                                          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,
                                                          decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
                                                          2 3 from v$lock
                                                          where sid=139; 4
                                                          SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
                                                          ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                          139 TM 51857 0 Row Exclusive None 0
                                                          139 TX 458775 238 Exclusive None 0

                                                          从结果看139会话,产生了一个TM锁和一个TX锁。

                                                          TM的ID2总是0,ID1是代表着操作所在的表,TX锁的ID1和ID2通过语句可以找到这个事务,从LOCK_MODE看出他们都持有锁,REQUEST_MODE看出都没有请求锁,从BLOCK都是0看出持有的锁都没有阻塞别人,再另外开一个session

                                                          同样的去执行

                                                            update employees set last_name=last_name||’b’ where department_id=60;

                                                            在SID为145的hr会话中执行,它需要的资源被锁住

                                                              SQL> update employees set last_name=last_name||'b' where department_id=60;

                                                              这时的执行被卡住

                                                              我们再去查一下这里是139和145,对(#A)语句稍作修改

                                                              得到语句

                                                                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 in(139,145)
                                                                order by sid;

                                                                查的是v$lock,看看这个锁的状况,好执行一下,结果

                                                                  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 3 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
                                                                  4 5 where sid in(139,145)
                                                                  6 order by sid;
                                                                  SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
                                                                  ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                                  139 TM 51857 0 Row Exclusive None 0
                                                                  139 TX 458775 238 Exclusive None 1
                                                                  145 TM 51857 0 Row Exclusive None 0
                                                                  145 TX 458775 238 None Exclusive 0

                                                                  139和145都出现了,139的TM和TX锁没变,一开始执行的139,后面执行的145,139一开始执行的,持有TX和TM锁,145的TM锁LOCK_MODE为Row Exclusive,持有145和139的TM锁的ID1相同就是对象还一样,也就是说139和145都在这个51857对象上加了RX锁;但是145的TX锁行的REQUEST_MODE的值是Exclusive出现了Exclusive,也就是145被139这个事务锁住了。

                                                                  然后我们看139的TX这行,BLOCK的值是1说明阻塞了别人,阻塞了145,而145 TX的REQUEST_MODE是Exclusive,它正在请求Exclusive锁,也就是被锁住了

                                                                  通过这个我们看到一些问题,但是我们知道这个锁出现这个问

                                                                  题,也不见得有问题,因为锁住很正常,139一旦提交以后,145马上就获取到这个锁了

                                                                  2)关于等待锁中的ID1和ID2

                                                                  另外从结果我们可能发现一个问题

                                                                      139 TX     458775        238 Exclusive           None                         1
                                                                    145 TX 458775 238 None Exclusive 0

                                                                    我们看到139会话和145会话的TX锁的ID1和ID2是相同的,这里的145的锁状态的ID1和ID2并不是145会话的事务信息,145会话的TX锁的REQUEST_MODE为Exclusive说明它在请求一个锁,这个例子中145自己本身的事务还没有开始,这时查询v$transaction并没有145会话的事务。

                                                                    TX锁REQUEST_MODE为Exclusive时,这里的ID1和ID2的值是被请求锁的事务信息,这里在请求139会话的锁,这里ID1和ID2的值就是139会话的信息

                                                                    当145得到锁以后,本例中这时145会话开始了一个事务,这里的ID1和ID2 的值会自动改变为145事务的信息,REQUEST_MODE为Exclusive的锁ID1和ID2的信息始终是被请求的持有锁的事务的信息,有多个事务等待同一个锁,前一个持有锁的事务释放锁后,一个新事务得到了这个锁,这时队列中的其它事务的Exclusive状态的锁信息的ID1和ID2都变为了这个新持有锁的事务的信息。

                                                                    如果145事务在请求锁之前,145已经开始了一个事务,也就是它已经持有了事务锁,这时的结果会把它本身的事务锁也列出,并且ID1和ID2的值是145事务的信息,同时也会列出它正在请求的锁的信息,这条信息的ID1和ID2是被请求锁的信息

                                                                    做出了下面的一个例子的结果

                                                                             SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
                                                                      ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                                      148 TX 262165 242 None Exclusive 0
                                                                      148 TM 51857 0 Row Exclusive None 0
                                                                      148 TX 524327 303 Exclusive None 0
                                                                      150 TX 262165 242 Exclusive None 1
                                                                      150 TM 51857 0 Row Exclusive None 0

                                                                      148会话本身持有一个TX锁

                                                                        148 TX     524327        303 Exclusive           None                         0

                                                                        这条信息的ID1和ID2的信息是它本身事务的信息,148会话还请求一个TX锁

                                                                          148 TX     262165        242 None                Exclusive                    0

                                                                          这条信息的ID1和ID2的信息是148正在请求的被请求锁的事务的信息,这里正在请求150正在持有的锁,所以这行的ID1和ID2列出了150事务的信息

                                                                          有一行

                                                                            150 TX     262165        242 Exclusive           None                         1

                                                                            这行的BLOCK为1,说明150事务它持有的锁有别的事务正在等待,正好和148正在请求锁对应,验证了前面的分析


                                                                            七、三个事务的锁争用


                                                                            1)三个事务争同一个锁

                                                                            我们再打开一个会话,再新建一个会话,以hr用户身份连接

                                                                            先查一下SID

                                                                              SQL> select sid from v$mystat where rownum=1;
                                                                              SID
                                                                              ----------
                                                                              136

                                                                              这个新的当前会话的SID是136,接着前面的实验,也去做同样的一个操作

                                                                                update employees set last_name=last_name||’b’ where department_id=60;

                                                                                它肯定也被锁住

                                                                                执行结果

                                                                                  SQL> update employees set last_name=last_name||'b' where department_id=60;

                                                                                  暂时无返回值,也被锁住,这里是136,现在是139 145 和136操作,139应该把145和136锁住了

                                                                                  再去查一个语句

                                                                                    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(145,136);

                                                                                    根据分析两个被锁住的是145和136,查的是v$enqueue_lock,这里面它会只是把谁被锁住了谁给列出来,请求锁的被列出来,执行一下,这里访问的是v$enqueue_lock

                                                                                    执行结果

                                                                                      SQL> 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(145,136); 2 3 4 5
                                                                                      SID TY REQUEST_MODE
                                                                                      ---------- -- -------------------
                                                                                      145 TX Exclusive
                                                                                      136 TX Exclusive

                                                                                      我们看145和136都在,v$enqueue_lock列出的都在请求锁,145和136都在请求事务锁,都被别人锁住了,其实139锁住了145 和 136,根据语句执行的顺序,145是第一个被锁住的,136是第二个被锁住的

                                                                                      这里面如果我139释放了的话,139把145和136同时锁住了,第一个获得锁的应该是145,再就是136,锁是可以排队的。

                                                                                      我们看一个TX锁这个锁是139的,这个锁把145和136同时锁住了,145和136会到139下面去排队,先是145,后面是136要过来排队,139释放以后,145第一个获取,第一个获得锁,有可能获得锁以后145又把136锁住了。

                                                                                      如果它们获取一样的资源,145把136锁住了,如果说139释放以后,145获取的资源和136获取的资源不一样的话,这两个可以同时获取到锁。通过这个我们可以看出并记住锁是排队的。

                                                                                      2)v$lock中BLOCK字段的值

                                                                                      举一个三个事务争用相同锁的例子,查询v$lock视图的结果

                                                                                        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 3 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
                                                                                        4 from v$lock
                                                                                        5 where sid in(132,135,139)
                                                                                        6 order by sid;


                                                                                        SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
                                                                                        ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                                                        132 TM 51857 0 Row Exclusive None 0
                                                                                        132 TX 655370 242 Exclusive None 1
                                                                                        135 TM 51857 0 Row Exclusive None 0
                                                                                        135 TX 655370 242 None Exclusive 0
                                                                                        139 TM 51857 0 Row Exclusive None 0
                                                                                        139 TX 655370 242 None Exclusive 0
                                                                                        6 rows selected.

                                                                                        这三个事务开始执行的顺序是132,135,139,这时132事务的信息是ID1:655370 ,ID2:242,135和139会话中的事务还没有开始,执行rollback释放第一个事务占用的锁,然后执行相同的语句

                                                                                        得到的结果是:

                                                                                               SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
                                                                                          ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                                                          135 TM 51857 0 Row Exclusive None 0
                                                                                          135 TX 327683 338 Exclusive None 1
                                                                                          139 TM 51857 0 Row Exclusive None 0
                                                                                          139 TX 327683 338 None Exclusive 0

                                                                                          132事务释放锁后,第一个排队的135得到了锁,得到锁后135会话中的事务就开始了,这时135会话中事务的信息是ID1:327683,ID2:338,这时它锁住了139,139还在等待,39会话中的事务仍然没有开始

                                                                                          不管是1个事务还是2个事务在等待锁,持有锁的事务的信息的BLOCK都为1,这个字段并不是说明有多少个事务在等待锁,只是说明有没有事务在等待这个锁

                                                                                          等待锁的139会话

                                                                                          在第一次查询时的结果

                                                                                            139 TX 655370 242 None Exclusive 0

                                                                                            在第二次查询时的结果

                                                                                              139 TX 327683 338 None Exclusive 0

                                                                                              ID1和ID2的值变化了,但都是139它等待的当前正在持有这个锁的事务的信息


                                                                                              八、锁的时间


                                                                                              我们找一个非常有意义的一个

                                                                                                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.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
                                                                                                order by time_held, time_waited;

                                                                                                这个sql语句是我们用的最多的一个sql语句,它做一件什么事情呢?

                                                                                                  SQL> 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,
                                                                                                  2 3 b.ctime as time_held,c.sid as waiter_sid,
                                                                                                  4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
                                                                                                  5 c.ctime time_waited
                                                                                                  6 from v$lock b, v$enqueue_lock c, v$session a
                                                                                                  where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
                                                                                                  7 8 order by time_held, time_waited;
                                                                                                  BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
                                                                                                  ----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
                                                                                                  139 2746 HR TX Exclusive 3909 136 Exclusive 1790
                                                                                                  139 2746 HR TX Exclusive 3909 145 Exclusive 2931

                                                                                                  前两个字段,BLOCKER_SID为139和SERIAL#为2746标明一个会话,这个会话使用BLOCKER_USERNAME为hr用户登陆的,它的TY是TX锁,它持有TIME_HELD为3909厘秒。

                                                                                                  第一行WAITER_SID为136事务,TIME_WAITED等待了1790这么长时间,也就是说136目前在等待139,就是136被139锁住了,139持有锁的时间是TIME_HELD 3909这么长了,WAITER_SID 136等待TIME_WAITED 1790这么长了,有了时间了就能判断这个锁是不是有没有问题。

                                                                                                  还有一个136等待了1790这么长的时间,145等待139等待了2931这么长时间,就说明145比136等的时间长了,过了一段时间再执行一次上面的命令

                                                                                                    SQL> 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,
                                                                                                    2 3 b.ctime as time_held,c.sid as waiter_sid,
                                                                                                    4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
                                                                                                    5 c.ctime time_waited
                                                                                                    from v$lock b, v$enqueue_lock c, v$session a
                                                                                                    6 7 where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
                                                                                                    8 order by time_held, time_waited;
                                                                                                    BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
                                                                                                    ----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
                                                                                                    139 2746 HR TX Exclusive 6334 136 Exclusive 4215
                                                                                                    139 2746 HR TX Exclusive 6334 145 Exclusive 5356

                                                                                                    145等待的时间比136时间长,也就是从某种意义上来讲145它排在136的前面,这个命令是有意义的

                                                                                                    我们就看TIME_WAITED列,再看TIME_HELD,如果你持有时间太长了,也就是说明你这个事务迟迟不提交,就根据BLOCKER_SID和SERIAL#这里是139和2746,就可以执行一个sql语句

                                                                                                    可以用

                                                                                                      alter system kill session139,2746’;

                                                                                                      把它kill掉,执行这个命令,执行它以后,它就可以把139给kill掉,kill以后它就会自动回滚

                                                                                                      系统管理员sys会话中做一下

                                                                                                        SQL> alter system kill session '139,2746';
                                                                                                        System altered.

                                                                                                        kill以后我们看,139已经kill掉了,这时我们看145

                                                                                                          SQL> update employees set last_name=last_name||'b' where department_id=60;
                                                                                                          5 rows updated.

                                                                                                          145的等待状态解除了,update操作成功了,也就是145现在持有了锁,136仍在等待,136还被锁着得不到执行,因为145又把136锁了

                                                                                                          我们再去查

                                                                                                            SQL> 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,
                                                                                                            2 3 4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
                                                                                                            5 c.ctime time_waited
                                                                                                            6 from v$lock b, v$enqueue_lock c, v$session a
                                                                                                            where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
                                                                                                            7 8 order by time_held, time_waited;
                                                                                                            BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
                                                                                                            ----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
                                                                                                            145 1015 HR TX Exclusive 221 136 Exclusive 221

                                                                                                            现在是145持有锁,但它阻塞了WAITER_SID 为136的会话,139会话就没锁了


                                                                                                            我们把刚才的事务都回滚了,在139会话中执行

                                                                                                              SQL> rollback;
                                                                                                              rollback
                                                                                                              *
                                                                                                              ERROR at line 1:
                                                                                                              ORA-00028: your session has been killed

                                                                                                              看出session 139已经被kill了,再把145的会话回滚了

                                                                                                                SQL> rollback;
                                                                                                                Rollback complete.

                                                                                                                再把136的会话回滚了

                                                                                                                  SQL> update employees set last_name=last_name||'b' where department_id=60;
                                                                                                                  5 rows updated.
                                                                                                                  SQL> rollback;
                                                                                                                  Rollback complete.

                                                                                                                  136会话在145会话回滚后得到了执行,最终它也得到了锁

                                                                                                                  为了试验把它也回滚了


                                                                                                                  九、一个事务多个TM锁


                                                                                                                  一个事务修改多行,产生一个TX锁,可以在多个表上产生多个TM锁,一个事务只产生一个事务锁TX锁

                                                                                                                  我们在一个事务里面多执行几条sql语句

                                                                                                                    update employees set last_name=last_name||'a' where department_id=60;
                                                                                                                    update departments set department_name='unknow' where department_id=10;
                                                                                                                    update locations set city='unknown' where location_id=1100;

                                                                                                                    在一个hr会话

                                                                                                                      SQL> select sid from v$mystat where rownum=1;
                                                                                                                      SID
                                                                                                                      ----------
                                                                                                                      132

                                                                                                                      先执行一条update

                                                                                                                        SQL> update employees set last_name=last_name||'a' where department_id=60;
                                                                                                                        5 rows updated.

                                                                                                                        更新了employees这个表

                                                                                                                        第二个语句,它接着更新departments

                                                                                                                          SQL> update departments set department_name='unknow' where department_id=10;
                                                                                                                          1 row updated.

                                                                                                                          都是一个事务里面的,下面语句是更新locations

                                                                                                                            SQL> update locations set city='unknown' where location_id=1100;
                                                                                                                            1 row updated.

                                                                                                                            更新了三个语句,然后我们再使用

                                                                                                                              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=132;

                                                                                                                              再去查132这个会话它持有锁的情况,在sys用户会话中执行结果

                                                                                                                                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 3 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block
                                                                                                                                4 from v$lock
                                                                                                                                5 where sid=132;


                                                                                                                                SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
                                                                                                                                ---------- -- ---------- ---------- ------------------- ------------------- ----------
                                                                                                                                132 TM 51857 0 Row Exclusive None 0
                                                                                                                                132 TM 51852 0 Row Exclusive None 0
                                                                                                                                132 TM 51847 0 Row Exclusive None 0
                                                                                                                                132 TX 589860 329 Exclusive None 0

                                                                                                                                一个会话的一个事务它修改了三个表,对三个表产生TM锁,它产生了一个TX锁,TX锁就只有一个。


                                                                                                                                十、transactions和dml_locks参数


                                                                                                                                再看一个sql语句

                                                                                                                                  select name,value from v$parameter where name in(‘transactions’,’dml_locks’);

                                                                                                                                  可以获得的TX锁定的总个数由初始化参数transactions决定,而可以获得的TM锁定的个数则由初始化参数dml_locks决定

                                                                                                                                  transactions参数表示Oracle一个实例最多可有的事务数

                                                                                                                                  dml_locks参数表示一个Oracle实例中最多可产生的TM锁就是表级锁的数量

                                                                                                                                  对整个数据库来讲

                                                                                                                                  它能获得的TX锁和TM锁的总数由’transactions’和’dml_locks’它俩限制

                                                                                                                                  如果这两个参数过小的话

                                                                                                                                  有可能影响并发的事务的数量以及访问的表的数量

                                                                                                                                  我们执行一下看有多大

                                                                                                                                    SQL> select name,value from v$parameter where name in('transactions','dml_locks');
                                                                                                                                    NAME VALUE
                                                                                                                                    --------------- ----------
                                                                                                                                    dml_locks 748
                                                                                                                                    transactions 187

                                                                                                                                    这个有时候也会碰到一些问题,结果中一个dml_locks是748,一个transactions是187,对数据库来讲,同时可以有187个事务可以同时运行,而锁的数量,同时修改的表可以有700多个。

                                                                                                                                    一般的我们把这个都修改的抬高一些,比如把transactions修改为300,比如dml_locks我们修改成1500,可以给它增加,到底该不该增加,我们有一个查询

                                                                                                                                      select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" 
                                                                                                                                      from v$resource_limit
                                                                                                                                      where resource_name in('transactions','dml_locks');

                                                                                                                                      这个很有用,很有帮助,有v$resource_limit这个视图,我们大家可以查一下,里面有好多的信息

                                                                                                                                      我们先查这一个transactions和dml_locks参数

                                                                                                                                        SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"
                                                                                                                                        from v$resource_limit
                                                                                                                                        where resource_name in('transactions','dml_locks'); 2 3
                                                                                                                                        R_N C_U M_U I_U
                                                                                                                                        ------------------------------ ---------- ---------- --------------------
                                                                                                                                        dml_locks 3 48 748
                                                                                                                                        transactions 2 9 187

                                                                                                                                        R_N这个列是资源名字,如dml_locks是资源名,C_U是current_utilization当前已经使用的数目,当前锁定了3个表,M_U是max_utilization最大同时使用的数目,最大锁过48个,I_U是initial_allocation初始可分配的数量,最大可分配的748

                                                                                                                                        这是dml_locks,当前C_U是3个,最大可以是I_U是748,M_U为48是曾经达到的最大值是48,只要这个48没达到748,说明我这个dml_locks没出现过问题

                                                                                                                                        那么transactions,曾经最大是9个,最大可以是187,这都没问题,v$resource_limit视图我们查一下访问一下,里面有很多资源

                                                                                                                                          SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"
                                                                                                                                          from v$resource_limit; 2
                                                                                                                                          R_N C_U M_U I_U
                                                                                                                                          ------------------------------ ---------- ---------- --------------------
                                                                                                                                          processes 27 37 150
                                                                                                                                          sessions 31 42 170
                                                                                                                                          enqueue_locks 13 22 2300
                                                                                                                                          enqueue_resources 17 38 968
                                                                                                                                          ges_procs 0 0 0
                                                                                                                                          ges_ress 0 0 0
                                                                                                                                          ges_locks 0 0 0
                                                                                                                                          ges_cache_ress 0 0 0
                                                                                                                                          ges_reg_msgs 0 0 0
                                                                                                                                          ges_big_msgs 0 0 0
                                                                                                                                          ges_rsv_msgs 0 0 0
                                                                                                                                          gcs_resources 0 0 0
                                                                                                                                          gcs_shadows 0 0 0
                                                                                                                                          dml_locks 3 48 748
                                                                                                                                          temporary_table_locks 0 0 UNLIMITED
                                                                                                                                          transactions 2 9 187
                                                                                                                                          branches 0 0 187
                                                                                                                                          cmtcallbk 0 2 187
                                                                                                                                          sort_segment_locks 0 1 UNLIMITED
                                                                                                                                          max_rollback_segments 11 11 187
                                                                                                                                          max_shared_servers 1 1 UNLIMITED
                                                                                                                                          parallel_max_servers 0 2 40
                                                                                                                                          22 rows selected.

                                                                                                                                          只要这个M_U的值没有跟I_U的值相等,当然不可能超过,没有跟它相等就说明我设置的参数都没有问题,比如有人经常说这个processes、这个sessions是不是设小了,连不上可以看看sessions的M_U的值有没有超过I_U的值,有没有等于它,等于它说明可能就有问题

                                                                                                                                          记住v$resource_limit这个很有意义,这里讲了和事务相关的一些操作


                                                                                                                                          十一、死锁


                                                                                                                                          1)死锁的发生

                                                                                                                                          Oracle里面还有一种锁叫死锁,Oracle一直这么说,Oracle中只要产生死锁一定是你的应用写的有问题,碰到死锁的时候Oracle自动会释放,会杀掉一个事务,一个死锁会产生一个trc文件,我们来看什么叫死锁一个事务要修改一个资源,A事务修改了这个资源,B事务修改了另一个资源,A事务修改了一个资源以后在这个资源上加了锁了,事务修改了另一个资源后也加了锁。

                                                                                                                                            A想持有B正在修改的这个资源,但已被B锁住了
                                                                                                                                            A修改了一个资源但是它还想修改B正在修改的资源但已被B锁住
                                                                                                                                            A被B锁住了
                                                                                                                                            B修改了一个资源后又想去修改A正在修改的资源
                                                                                                                                            B被A锁住了

                                                                                                                                            产生死锁,并且这个结它解不开,因为,这时只有A回滚了以后,B才能持有A现在拥有的资源

                                                                                                                                            死锁以后会有什么现象呢?

                                                                                                                                            我把原来实验的会话都给它rollback,并在新会话中实验,在session1里面,我更新100

                                                                                                                                              SQL> select sid from v$mystat where rownum=1;
                                                                                                                                              SID
                                                                                                                                              ----------
                                                                                                                                              150
                                                                                                                                              SQL> update employees set last_name=last_name||'a'


                                                                                                                                              where employee_id=100; 2
                                                                                                                                              1 row updated.

                                                                                                                                              我把100给锁住了

                                                                                                                                              然后在 session2里面呢?

                                                                                                                                                SQL> select sid from v$mystat where rownum=1;
                                                                                                                                                SID
                                                                                                                                                ----------
                                                                                                                                                148
                                                                                                                                                SQL> update employees set last_name=last_name||'b'
                                                                                                                                                where employee_id=101; 2
                                                                                                                                                1 row updated.

                                                                                                                                                把101给锁住了,A里面把100锁住了,B把101锁住了,然后session1想去:

                                                                                                                                                  SQL> update employees set last_name=last_name||'c' where employee_id=101;

                                                                                                                                                  想去锁B锁住的资源,已被B锁住了,这时A被B锁住了,然后session2中:

                                                                                                                                                    SQL> update employees set last_name=last_name||'d' where employee_id=100;

                                                                                                                                                    也在等待锁,B又被A锁住了,形成一个死循环了,这时在A里面出现

                                                                                                                                                      SQL> update employees set last_name=last_name||'c' where employee_id=101;
                                                                                                                                                      update employees set last_name=last_name||'c' where employee_id=101
                                                                                                                                                      *
                                                                                                                                                      ERROR at line 1:
                                                                                                                                                      ORA-00060: deadlock detected while waiting for resource

                                                                                                                                                      这时候A会话马上出一个问题,另外一个会话B中我们回车以后,这个A会话一下子检测到死锁,被回滚了,马上被回滚了,这里回滚的不是A会话中的整个事务,只是被回滚了一条语句,就是把造成死锁的那条语句给回滚了,这个事务中前面其它语句没有影响,并没有回滚整个的事务

                                                                                                                                                      如果这时查询A会话,查询后可以得知这条语句前执行的语句仍然有效,也就是当死锁发生的时候,Oracle马上会检测,同时将其中一个事务的一条造成死锁的语句给自动回滚

                                                                                                                                                      这里是回滚了第一个会话造成死锁的语句,就是请求第二个会话占有的锁但是未得到锁的语句,但这时第二个会话请求的锁,第一个会话仍然占有,既然如此,死循环被解除了,这样在在造成死锁的两个会话中解除了死锁。

                                                                                                                                                      我们执行rollback将其中一个会话全部回滚

                                                                                                                                                        SQL> rollback;
                                                                                                                                                        Rollback complete.

                                                                                                                                                        并且这里又把第一个会话中占用锁的语句回滚后

                                                                                                                                                        第二个会话中等待锁的语句得到了执行

                                                                                                                                                          SQL> update employees set last_name=last_name||'d' where employee_id=100;
                                                                                                                                                          1 row updated.

                                                                                                                                                          把第二个会话也回滚

                                                                                                                                                            SQL> rollback;
                                                                                                                                                            Rollback complete.

                                                                                                                                                            当死循环发生的时候会做几件事情,第一个Oracle自动的对死锁自动的检测,而且还能快速检测,而且把其中一个事务给回滚,但只是回滚部分sql语句

                                                                                                                                                            2)死锁的信息

                                                                                                                                                            同时当死锁发生的时候会出现一件很重要的事情,Oracle会记录下死锁的信息

                                                                                                                                                            死锁发生的时候

                                                                                                                                                              [oracle@redhat4 bdump]$ pwd
                                                                                                                                                              /u01/app/oracle/admin/jiagulun/bdump

                                                                                                                                                              在这个目录里面

                                                                                                                                                                [oracle@redhat4 bdump]$ ls
                                                                                                                                                                alert_jiagulun.log jiagulun_lgwr_13577.trc jiagulun_mmnl_6638.trc
                                                                                                                                                                jiagulun_cjq0_13651.trc jiagulun_lgwr_13643.trc jiagulun_p000_6646.trc
                                                                                                                                                                jiagulun_lgwr_13460.trc jiagulun_lgwr_6626.trc jiagulun_p001_6648.trc

                                                                                                                                                                有alert日志alert_jiagulun.log,alert日志是数据库的总日志,可以查看这个alert日志cat alert_jiagulun.log

                                                                                                                                                                执行结果

                                                                                                                                                                  [oracle@redhat4 bdump]$ cat alert_jiagulun.log
                                                                                                                                                                  Mon Apr 11 13:38:53 2016
                                                                                                                                                                  Starting ORACLE instance (normal)
                                                                                                                                                                  LICENSE_MAX_SESSION = 0
                                                                                                                                                                  LICENSE_SESSIONS_WARNING = 0
                                                                                                                                                                  Shared memory segment for instance monitoring created
                                                                                                                                                                  Tue Nov 21 06:45:15 2017
                                                                                                                                                                  MMNL absent for 63369 secs; Foregrounds taking over
                                                                                                                                                                  MMNL absent for 63369 secs; Foregrounds taking over
                                                                                                                                                                  MMNL absent for 63369 secs; Foregrounds taking over
                                                                                                                                                                  Tue Nov 21 08:14:17 2017
                                                                                                                                                                  ORA-00060: Deadlock detected. More info in file u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.
                                                                                                                                                                  Tue Nov 21 08:25:31 2017
                                                                                                                                                                  ORA-00060: Deadlock detected. More info in file u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

                                                                                                                                                                  在alert日志里面会自动把死锁信息给列出来,说死锁发生了

                                                                                                                                                                  如:

                                                                                                                                                                    Tue Nov 21 08:25:31 2017
                                                                                                                                                                    ORA-00060: Deadlock detected. More info in file u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

                                                                                                                                                                    在信息里面可以得到死锁对应的trc文件

                                                                                                                                                                      /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc

                                                                                                                                                                      以上就是讲的Oracle的锁的一些情况,分享至此,供大家参考学习,如有帮助,欢迎转发+点赞~

                                                                                                                                                                      最后修改时间:2021-03-12 10:45:12
                                                                                                                                                                      文章转载自新站,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

                                                                                                                                                                      评论