ORACLE锁阻塞研究

最新推荐文章于 2021-11-01 15:20:49 发布
最新推荐文章于 2021-11-01 15:20:49 发布
阅读量203 收藏
点赞数
文章标签： 数据库
1. SESION 信息
SESSION 125
SQL> select sid from v$mystat where rownum<2;

       SID
----------
       125


SESSION 17       
SQL> select sid from v$mystat where rownum<2;

       SID
----------
        17
        
        
SESSION 25

SQL> select sid from v$mystat where rownum<2;

       SID
----------
        25
        
           
2. 行锁

SESSION 125
SQL> update lixia.t1 set object_id=20 where object_id=20;

1 row updated.


SESSION 17 执行 UPDATE 被阻塞

update lixia.t1 set object_id=20 where object_id=20;

SESSION 25 执行 UPDATE 被阻塞

update lixia.t1 set object_id=20 where object_id=20;


--检查 V$LOCK 视图
SQL>  select * from v$lock where sid in (125,17,25) order by sid;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007FF14A838D58 00007FF14A838DB8         17 TM     178875          0          3          0        130          0
000000008D58FE90 000000008D58FEE8         17 TX     393240       8029          0          6        130          0
000000008D58F5B8 000000008D58F610         17 AE        100          0          4          0        299          0
000000008D58FDB0 000000008D58FE08         25 AE        100          0          4          0        261          0
00007FF14A838D58 00007FF14A838DB8         25 TM     178875          0          3          0        127          0
000000008D5902F0 000000008D590348         25 TX     393240       8029          0          6        127          0
00007FF14A838D58 00007FF14A838DB8        125 TM     178875          0          3          0        152          0
000000008CBAEE20 000000008CBAEE98        125 TX     393240       8029          6          0        152          1
000000008D58F778 000000008D58F7D0        125 AE        100          0          4          0       2146          0
000000008D58E6C0 000000008D58E718        125 TO      79833          1          3          0       2138          0
000000008D58FF70 000000008D58FFC8        125 TO     170778          1          3          0       2133          0

11 rows selected.


SESSION 17 的 TM 锁模式为行级排它锁（LMODE 为 3），TX 锁的模式为NULL （LMODE 为 0）还没获得 TX锁。

SESSION 25 的情况与 SESSION 17 相同。

SESSION 125 的 TM 锁模式为行级排他锁 （LMODE 为 3），TX 锁的模式为排他模式 （LMODE 为 6），BLOCK 为 1 表示阻塞了其他会话。

--检查事务信息


SQL> select XIDUSN,XIDSLOT,XIDSQN,STATUS from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN STATUS
---------- ---------- ---------- ----------------
         6         24       8029 ACTIVE

使用了6号回滚段，槽号为 24。（XIDSQN）事务ID序列号为 8029

--查看锁信息

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 (125,17,25) order by sid;


       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
        17 TM     178875          0 Row Exclusive       None                         0
        17 TX     393240       8029 None                Exclusive                    0
        17 AE        100          0 Share Row Exclusive None                         0
        25 AE        100          0 Share Row Exclusive None                         0
        25 TM     178875          0 Row Exclusive       None                         0
        25 TX     393240       8029 None                Exclusive                    0
       125 TM     178875          0 Row Exclusive       None                         0
       125 TX     393240       8029 EXclusive           None                         1
       125 AE        100          0 Share Row Exclusive None                         0
       125 TO      79833          1 Row Exclusive       None                         0
       125 TO     170778          1 Row Exclusive       None                         0

11 rows selected.

对于 TM 锁来说，ID1表示被锁定的对象的对象ID，ID2始终为 0。
对于 TX 锁来说，ID1 表示事务使用的回滚段编号已经在事务表中对应的记录编号，ID2
表示该编号被重用的次数（wrap）。使用下面的SQL语句将上面的ID1 转换为直观的数字：

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

 UNDO_BLK#      SLOT#
---------- ----------
         6         24
         
--通过 TM锁的ID获取被阻塞的对象名称
select object_name,object_type from dba_objects
where object_id=178875;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
T1                   TABLE


--查看 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 (125,17,25) ;

       SID TY REQUEST_MODE
---------- -- -------------------
       125 TO None
        17 AE None
       125 AE None
        25 AE None
        17 TX Exclusive
       125 TO None
        25 TX Exclusive

7 rows selected.

17 号 SESSION 先进入队列，25 号 SESSION 后进入队列。



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.block = 1
order by time_held,time_waited;

BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED
----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
        125          5 SYS                            TX EXclusive                 3336         25 Exclusive                  3311
        125          5 SYS                            TX EXclusive                 3336         17 Exclusive                  3314


v$lock.ctime 锁定时间单位为秒。

v$enqueue_lock.ctime 等待锁定时间单位为秒。

--查看锁的阻塞关系
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,ob.object_name,ob.object_type
from v$lock b,v$enqueue_lock c,v$session a,v$locked_object lo,dba_objects ob
where a.sid = b.sid
  and b.id1 = c.id1(+)
  and b.id2 = c.id2(+)
  and c.type(+) = 'TX'
  and b.block = 1
  and lo.SESSION_ID= a.sid
  and lo.OBJECT_ID=ob.object_id
order by time_held,time_waited;

BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED OBJECT_NAME          OBJECT_TYP
----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- ----------- -------------------- ----------
         17         31 SYS                            TX EXclusive                  370         25 Exclusive                   370 T1                   TABLE
         
--查看阻塞会话执行的SQL语句         
select sql_fulltext from v$session a,v$sql s
where a.PREV_SQL_ID=s.sql_id
  and sid=17;
 
SQL_FULLTEXT
--------------------------------------------------------------------------------
update lixia.t1 set object_id=20 where object_id=20
update lixia.t1 set object_id=20 where object_id=20


--查看等待锁的会话执行的SQL
 
select sql_fulltext from v$session a,v$sql s
where a.sql_id=s.sql_id
  and sid=25;     
 
SQL_FULLTEXT
--------------------------------------------------------------------------------
update lixia.t1 set object_id=20 where object_id=20
update lixia.t1 set object_id=20 where object_id=20
 


3. 表锁

3.1 排他表锁

--SESSION 125 获取排他表锁
lock table lixia.t1 in exclusive mode;

--SESSION 19 执行普通 SELECT 不会被阻塞
select object_id,object_name
from lixia.t1
where object_id=100;

--查看锁信息

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 (125,19) order by sid;

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
        19 AE        100          0 Share               None                         0
       125 AE        100          0 Share               None                         0
       125 TM     178875          0 EXclusive           None                         0
       125 TO      79833          1 Row Exclusive       None                         0
       125 TO     170778          1 Row Exclusive       None                         0

结论：排他表锁不会阻塞普通的SELECT 语句

--SESSION 19 执行 select * from table for update 语句被阻塞

select object_id,object_name
from lixia.t1
where object_id=100
for update; --被阻塞


--查看表锁等待关系
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;


      SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID OBJECT_NAME          OBJECT_TYPE
---------- -- ------------------- ---------- ----------- ---------- -------------------- -------------------
       125 TM EXclusive                  253          25         19 T1                   TABLE


--查看表锁等待关系及SQL语句
col sql_text format a80
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'block',
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=a.sid
  and sql.sql_id=s.PREV_SQL_ID
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1
union
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'wait',
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=b.sid
  and sql.sql_id=s.sql_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;


       SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID 'BLOC SQL_TEXT
---------- -- ------------------- ---------- ----------- ---------- ----- --------------------------------------------------------------------------------
       125 TM EXclusive                  290          62         19 block lock table lixia.t1 in exclusive mode
       125 TM EXclusive                  290          62         19 wait  select object_id,object_name from lixia.t1 where object_id=100 for update

结论：排他表锁会阻塞  select * from table for update 语句。

--SESSION 19 使用 CTRL+C 中断SQL执行


--SESSION 19 执行INSERT 语句被阻塞
insert into lixia.t1 (object_id,object_name) values(178876,'test1');

--查看表锁等待关系
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type,
       decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;

      SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID OBJECT_NAME          OBJECT_TYPE
---------- -- ------------------- ---------- ----------- ---------- -------------------- -------------------
       125 TM EXclusive                  506          34         19 T1                   TABLE


--查看表锁等待关系及SQL语句
col sql_text format a80
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'block',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=a.sid
  and sql.sql_id=s.PREV_SQL_ID
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1
union
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'wait',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=b.sid
  and sql.sql_id=s.sql_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;
 
 

       SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID 'BLOC REQUEST_MODE        TY SQL_TEXT
---------- -- ------------------- ---------- ----------- ---------- ----- ------------------- -- --------------------------------------------------------------------------------
       125 TM EXclusive                  838         367         19 block Row Exclusive       TM lock table lixia.t1 in exclusive mode
       125 TM EXclusive                  838         367         19 wait  Row Exclusive       TM insert into lixia.t1 (object_id,object_name) values(178876,'test1')




--SESSION 19 使用 CTRL+C 中断SQL执行

--SESSION 125 回滚事务
SQL> rollback;

Rollback complete.

结论：排他表示会阻塞 INSERT 语句。

###################################################################################
3.2 共享锁、行级排他表锁
--SESSION 125 获取共享表锁

lock table lixia.t1 in share  mode;

--SESSION 25 执行 select * from table for update 语句被阻塞

select object_id,object_name
from lixia.t1
where object_id=100
for update; --被阻塞


--在其他会话中查看锁模式发现 SESSION 25 想获取的是行级排他表锁（并不是获取行共享表锁）。

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 (125,17,25) order by sid;

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
        17 AE        100          0 Share               None                         0
        25 AE        100          0 Share               None                         0
        25 TM     178875          0 None                Row Exclusive                0
       125 TM     178875          0 Share               None                         1
       125 TO     170778          1 Row Exclusive       None                         0
       125 AE        100          0 Share               None                         0
       125 TO      79833          1 Row Exclusive       None                         0

7 rows selected.

--SESSION 125 回滚事务

SQL> rollback;

Rollback complete.


--在其他会话查看锁模式，发现 SESSION 25 获得了 TX 锁和行级排他表锁。

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 (125,17,25) order by sid;

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
        17 AE        100          0 Share               None                         0
        25 AE        100          0 Share               None                         0
        25 TM     178875          0 Row Exclusive       None                         0
        25 TX     589828       8052 EXclusive           None                         0
       125 TO     170778          1 Row Exclusive       None                         0
       125 AE        100          0 Share               None                         0
       125 TO      79833          1 Row Exclusive       None                         0

7 rows selected.

结论：
在11.2.0.4 中 select * from table for update 语句获取的不是行级共享表锁（RS）而是行级排他表锁（RX）。
共享表锁与 TX 锁不兼容；共享表锁与行级排他表锁不兼容。
共享表锁与普通的 SELECT 语句兼容。



--SESSION 125 获取共享表锁

lock table lixia.t1 in share  mode;


--SESSION 19 执行INSERT 语句被阻塞
insert into lixia.t1 (object_id,object_name) values(178876,'test1');


--查看表锁等待关系。注意DML语句在没获得表锁的情况下，是无法获得TX锁的。
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type,
       decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;


       SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID OBJECT_NAME          OBJECT_TYPE         REQUEST_MODE        TY
---------- -- ------------------- ---------- ----------- ---------- -------------------- ------------------- ------------------- --
       125 TM Share                       30          22         19 T1                   TABLE               Row Exclusive       TM


--查看表锁等待关系及SQL语句
col sql_text format a80
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'block',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=a.sid
  and sql.sql_id=s.PREV_SQL_ID
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1
union
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'wait',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=b.sid
  and sql.sql_id=s.sql_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;


     SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID 'BLOC REQUEST_MODE        TY SQL_TEXT
---------- -- ------------------- ---------- ----------- ---------- ----- ------------------- -- --------------------------------------------------------------------------------
       125 TM Share                       57          49         19 block Row Exclusive       TM lock table lixia.t1 in share  mode
       125 TM Share                       57          49         19 wait  Row Exclusive       TM insert into lixia.t1 (object_id,object_name) values(178876,'tes

结论：共享表锁会阻塞 INSERT 语句。


####################################################################
3.3 共享行级排他表锁

--SESSION 125 活动共享行级排他表锁

lock table lixia.t1 in share row exclusive mode;

--SESSION 19 执行普通 SELECT 语句，不会被阻塞
select object_id,object_name
from lixia.t1
where object_id=100;

结论：共享行级排他锁不会阻塞普通的 SELECT 语句。


--SESSION 19 执行 select * from table for update 语句被阻塞
select object_id,object_name
from lixia.t1
where object_id=100 for update;


--查看表锁等待关系。注意DML语句在没获得表锁的情况下，是无法获得TX锁的。
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type,
       decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;

      SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID OBJECT_NAME          OBJECT_TYPE         REQUEST_MODE        TY
---------- -- ------------------- ---------- ----------- ---------- -------------------- ------------------- ------------------- --
       125 TM Share Row Exclusive        143          22         19 T1                   TABLE               Row Exclusive       TM

--查看表锁等待关系及SQL语句
col sql_text format a80
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'block',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=a.sid
  and sql.sql_id=s.PREV_SQL_ID
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1
union
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'wait',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=b.sid
  and sql.sql_id=s.sql_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;

       SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID 'BLOC REQUEST_MODE        TY SQL_TEXT
---------- -- ------------------- ---------- ----------- ---------- ----- ------------------- -- --------------------------------------------------------------------------------
       125 TM Share Row Exclusive        172          51         19 block Row Exclusive       TM lock table lixia.t1 in share row exclusive mode
       125 TM Share Row Exclusive        172          51         19 wait  Row Exclusive       TM select object_id,object_name from lixia.t1 where object_id=100 for update


--SESSION 19 使用 CTRL+C 中断SQL


--SESSION 125 回滚事务
SQL> rollback;

Rollback complete.

结论：共享行级排他表锁（SRX）会阻塞 select * from table for update 语句。



4. 自治事务
--SESSION 125 先执行父事务
SQL> update lixia.t1 set object_id=20 where object_id=20;

1 row updated.

--然后在 SESSION 125 中执行自治事务
declare
   pragma autonomous_transaction;
begin
   update lixia.t1 set object_id=20 where object_id=20;
end;
/

--在其他SESSION 中查看锁信息
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,ob.object_name,ob.object_type
from v$lock b,v$enqueue_lock c,v$session a,v$locked_object lo,dba_objects ob
where a.sid = b.sid
  and b.id1 = c.id1(+)
  and b.id2 = c.id2(+)
  and c.type(+) = 'TX'
  and b.block = 1
  and lo.SESSION_ID= a.sid
  and lo.OBJECT_ID=ob.object_id
order by time_held,time_waited;

BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID  REQUEST_MODE        TIME_WAITED OBJECT_NAME          OBJECT_TYPE
----------- ---------- ------------------------------ -- ------------------- ---------- ----------  ------------------- ----------- -------------------- -------------------
        125          5 SYS                            TX EXclusive                  737        125  Exclusive                     2 T1                   TABLE
        125          5 SYS                            TX EXclusive                  737        125  Exclusive                     2 T1                   TABLE


我们看到 SESSION 125阻塞了自己。


--过几秒钟自治事务会报死锁错误
SQL> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update lixia.t1 set object_id=20 where object_id=20;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2128275/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2128275/

congjiong3047
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle 锁和阻塞
谢辉的专栏
08-22 1187
oracle中的并发是由于两个或两个以上的用户对同一个资源进行争夺而引发的一种并发行为，这就导致了我们所说的锁，锁是为了保持数据的一致性     引起阻塞的一些情况：     1：表中的唯一性约束会引起阻塞（同时对同一条数据做修改，删除，或者新增），如果事物还没有提交或者事务回滚，那么oracle为了保持数据的一致性，就会把其它进程操作此条数据的任务阻塞在外，如果一直没有提交事务或者回滚，那么
Oracle解锁的方式介绍
09-10
通过SQL查询可以查看到被锁住的表AA以及Sid，Serial#;使用DBA身份，通过执行 alter system kill session 'SID,SERIAL#';即可解锁
oracl锁的几种模式
weixin_40908896的博客
07-19 224
ORACLE里锁有以下几种模式: 0：none 1：null 空 2：Row-S 行共享(RS)：共享表锁，sub share 3：Row-X 行独占(RX)：用于行的修改，sub exclusive 4：Share 共享锁(S)：阻止其他DML操作，share 5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 6：exclusive 独占(X)：...
锁的简单操作
君子不器
11-14 452
6、锁相关视图 v$transaction XIDUSN表示当前事务使用的回滚段的编号 XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号（也可以叫做槽号） XIDSQN说明序列号 STATUS说明该事务是否为活动的 举例： SQL> delete from scott.emp where rownum=1; 已删除 1 行。 SQL> sele
oracle客户端连接阻塞,Oracle阻塞(blockingblocked)实例详解
weixin_29769523的博客
04-04 407
一、概述：阻塞是DBA经常碰到的情形，尤其是不良的应用程序设计所造成的阻塞将导致数据库性能的严重下降，直至数据库崩溃。对DBA而言，有必要知道如何定位到当前系统有哪些阻塞，到底谁是阻塞者，谁是被阻塞者。本文对此给出了描述并做了相关演示。二、演示阻塞：--更新表，注，提示符scott@CNMMBO表明用户为scott的session，用户名不同，session不同。scott@CNMMBO> ...
Oracle锁阻塞问题排查步骤
weixin_42538000的博客
11-01 766
1、查看当前会话持有锁和等待锁的情况： select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,DECODE (BLOCK, 0, '', 'blocker') blocker,DECODE (request, 0, '', 'waiter') waiter from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request&g..
oracle锁机制研究
08-16
oracle锁机制研究
Oracle锁处理、解锁方法
09-09
本文通过实例代码较详细的给大家介绍了Oracle锁处理、解锁方法,需要的朋友可以参考下
oracle处理锁表基本操作
09-09
在本篇文章里小编给大家整理的是关于oracle处理锁表基本操作以及相关代码，需要的朋友们学习下。
Oracle字符集研究.ppt
11-21
Oracle字符集研究.ppt
根据oracle数据库sid及serial#查找操作系统sid号
05-02
【oracle】锁、阻塞、死锁
debimeng
11-30 749
oracle 锁、阻塞、死锁 锁：用于管理对共享资源的并发访问。 阻塞：如果一会话持有某一资源的锁，而另一个会话在请求这个资源。 死锁：如果有两个会话，每个会话都持有另一个会话想要的资源。...
Oracle的锁和阻塞
weixin_33817333的博客
05-16 95
锁是由并发产生，并发(concurrency)指超过两个以上的用户对同样的数据做修改，可能包括插入，删除和修改。并行(parallel)指将一个任务分成很多小的部分，每一个部分同时执行，最后将执行结果汇总成最后的结果。 查看数据库中是否存在锁: 1. 查看会话SID：select sid from v$mystat where rownum=1; 2. 通过查看视图v$lock：select...
V$LOCK
03-18 84
v$lock视图的各个列及其说明:ADDR RAW(4) 在内存中锁定的对象的地址KADDR RAW(4) 在内存中锁的地址SID NUMBER 保持或申请锁的会话的标识号T...
Oracle中查找阻塞与被阻塞SID的方法
11-26 658
在Oracle中,会经常遇到阻塞与被阻塞的情况.查找阻塞与被阻塞的方法主要有下面几种:一.通过查找v$lock和v$locked_object这是最常用的也是最直接的方法SQL> select sid,block from...
Select For update语句浅析
ciqu9915的博客
05-04 5711
Select … for update语句是我们经常使用手工加锁语句。通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。...
oracle经常被阻塞,Oracle阻塞(blockingblocked)实例详解
weixin_33622555的博客
04-06 505
update emp set sal=sal*1.1 where empno=7788;1 row updated.scott@CNMMBO> @my_envSPID SID SERIAL# USERNAME PROGRAM11205 1073 4642 robin oracle@SZDB (TNS V1-V3)--另起两个session更新...
锁相关基础知识
03-16 130
1. 锁类型 latch锁: 保护内存结构, chain, 链 lock 锁: 保护 buffer, block | 排他锁 (X), 共享锁 (S) 2. 行级锁 由 DML 语句产生, 数据行的所标记指向事...
oracle锁表解锁
最新发布
07-28
在Oracle中，要解锁被锁住的表，可以按照以下步骤进行操作： 1. 首先，查看数据库锁，诊断锁的来源及类型。可以使用以下SQL语句查询锁信息： SELECT OBJECT_ID, SESSION_ID, LOCKED_MODE FROM V$LOCKED_OBJECT; \...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值