oracle v$lock系列之三

最新推荐文章于 2021-04-06 09:07:43 发布
最新推荐文章于 2021-04-06 09:07:43 发布
阅读量109 收藏
点赞数
文章标签： 数据库 后端

结论

1,oracle11g测试环境
2,产生事务时会同时在表上加2个锁，分别为事务锁tx锁以及表锁tm锁
3,表锁tm锁又从锁定模式分为7个不同值
  本文仅测试了其中5个不同值
  分别为：
   2 - row-S (SS)
   3 - row-X (SX)
   4 - share (S)
   5 - S/Row-X (SSX)
   6 - exclusive (X)


4，上述的锁定模式数字越大，持锁规模越大，越影响并行操作，即:
   6 - exclusive (X) >  5 - S/Row-X (SSX)  >  4 - share (S) > 3 - row-X (SX) > 2 - row-S (SS)


5,关于这些持锁模式相互兼容性，请见下述的具体测试
6，关于如下2种持锁模式在另一则文章进行测试 
   0 - none
   1 - null (NULL)




测试



SQL> create table t_lock(a int);


Table created.


SQL> insert into t_lock values(1);


1 row created.




commit;




SQL> update t_lock set a=999;


1 row updated.




数字1的十六进制（在数据块以16进制存储）
SQL> select dump(1,16) from dual;


DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2


可见新事务会产生行锁（TX锁）及表锁(TM锁)
SQL> select sid,type,id1,id2,lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM','TX');


       SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
        37 TM        74639          0          3          0        261          0
        37 TX     10289154       4472          6          0        261          0




SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) from t_lock;


DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                98894
测试下tm表锁不同锁模式的适用场景


先摘录下ORACLE V$LOCK关于LMODE的含义
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)




先看  2 - row-S (SS)
会话1
SQL> lock table t_testlock in row share mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-s               0          4          0


 会话2
 SQL> insert into t_testlock values(2);


1 row created.




SQL> delete from t_testlock;


2 rows deleted.




SQL> update t_testlock set a=33;


1 row updated.


SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> drop table t_testlock purge;
drop table t_testlock purge
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;


Table(s) Locked.


SQL> lock table t_testlock in share  mode;


Table(s) Locked.


SQL> lock table t_testlock in share row exclusive mode;


Table(s) Locked.


SQL> lock table t_testlock in share update mode;


Table(s) Locked.


SQL> lock table t_testlock in exclusive mode;


lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可见row-s行级共享锁不会阻塞其它会话的任何DML操作，但会阻塞ddl操作
即会阻塞表级排它锁操作，但不会阻塞行级排它锁




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


会话1
SQL> create table t_testlock(a int);


Table created.


SQL> create table t_testlock(a int);


Table created.


SQL> insert into t_testlock values(999);


1 row created.


可见insert获取行级排它锁row-x
SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-x               0        394          0


或者采用如下方式
会话1
SQL> lock table t_testlock in   row exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-x               0          2          0       


会话2
SQL> insert into t_testlock values(2);


1 row created.


SQL>  drop table t_testlock purge;
 drop table t_testlock purge
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;


Table(s) Locked.


SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


可见row-x行级排它锁不会阻塞其它会话的DML操作，但会阻塞ddl操作
同时会阻塞行级或表级排它锁以及表级共享锁




---------------------------------------------------------------
会话1
SQL> lock table t_testlock in  share mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 s                   0          5          0


 会话2
 SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock purge;


SQL> drop table t_testlock purge;
drop table t_testlock purge
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> 


SQL> select * from t_testlock for update;
select * from t_testlock for update
              *
ERROR at line 1:
ORA-01013: user requested cancel of current operation




SQL> select * from t_testlock;


         A
----------
        33


SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;


Table(s) Locked.


SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可见s锁即表级共享锁，锁定级别最高，会阻塞其它会话的DML操作以及DDL操作，但不阻塞其它会话的SELECT查询操作
会阴塞其它会话的行级及表级排它锁




---------------------------------------------------------------
会话1
SQL> lock table t_testlock in  share row exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 ssx                 0          5          0


会话2
SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> select * from t_testlock for update;
select * from t_testlock for update
              *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock;
drop table t_testlock
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> select * from t_testlock;


         A
----------
        33


SQL> 






SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可见ssx锁即共享行排它表级，锁定级别要比s锁即共享表锁更高，会阻塞其它会话的DML操作以及DDL操作，但不阻塞其它会话的SELECT查询操作
而且其它会话不能再持有ssx锁,且仅不会阻塞行级共享锁
而


---------------------------------------------------------------
会话1
SQL> lock table t_testlock in   exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 x                   0          3          0




会话2
SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock;
drop table t_testlock
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> select * from t_testlock for update;
select * from t_testlock for update
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


SQL> lock table t_testlock in row share  mode;
lock table t_testlock in row share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> 


SQL> select * from t_testlock;


         A
----------
        33




x锁即表级排它锁，是锁定级别最高的锁模式，它会阻塞其它会话的任何DML操作以及任何类型的加锁操作 
可见x锁最牛比，锁定级别最高，会阻塞其它会话的任何锁模式


个人简介：


8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
   
   服务过的客户：
          中国电信
          中国移动
          中国联通
          中国电通
          国家电网
          四川达州商业银行
          湖南老百姓大药房
          山西省公安厅
          中国邮政
          北京302医院     
          河北廊坊新奥集团公司
  
 项目经验:
           中国电信3G项目AAA系统数据库部署及优化
           中国联通CRM数据库性能优化
           中国移动10086电商平台数据库部署及优化
           湖南老百姓大药房ERR数据库sql优化项目
           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
           北京高铁信号监控系统RAC数据库部署及优化
           河南宇通客车数据库性能优化
           中国电信电商平台核心采购模块表模型设计及优化
           中国邮政储蓄系统数据库性能优化及sql优化
           北京302医院数据库迁移实施
           河北廊坊新奥data guard部署及优化
           山西公安厅身份证审计数据库系统故障评估
         
 联系方式：
          手机：18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博：wisdomone9
          qq群：275813900    
          itpub博客名称：wisdomone1     http://blog.itpub.net/9240380/


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

转载于:http://blog.itpub.net/9240380/viewspace-1816522/

clg10051
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle LOCK
HKING_H的专栏
05-16 393
--原理 --Oracle数据库的锁类型 --根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。 --DML锁的目的在于保证并发情况...
oracle授权v$lock,oracle v$lock详解
weixin_33838871的博客
04-03 786
我们可以通过v$lock视图查看lock的信息，并且还可以根据v$lock的视图LMODE和REQUEST MODE判断出谁是owner，waiter和converterLMODE>0,REQUEST=0 OwnerLMODE=0,REQUEST>0 WaiterLMODE>0,REQUEST>0 ConverterORACLE里锁有以下几种模式:0：none1：null ...
oracle授权v$lock,请教:v$lock中block=2是什么含义?
weixin_28759259的博客
04-03 416
create or replace view dba_lock asselectsid session_id,decode(type,'MR', 'Media Recovery','RT', 'Redo Thread','UN', 'User Name','TX', 'Transaction','TM', 'DML','UL', 'PL/SQL User Lock','DX', 'Distribu...
oracle授权v$lock,访问V$LOCK视图Oracle 11g出现性能问题
weixin_28726391的博客
04-03 266
最近发现Oracle 11g有个问题，拿出来和大家讨论。是在Oracle 11.2.0.3 For Linux X64环境中。检查数据库是否存在锁信息，在查询V最近发现Oracle11g有个问题，拿出来和大家讨论。是在Oracle 11.2.0.3 For Linux X64环境中。检查数据库是否存在锁信息，在查询V$LOCK视图时发现很长时间没有响应，甚至一度认为数据库HANG住：SQL>...
Oracle v$LOCK
小楼一夜听春雨，深巷明朝卖杏花
10-17 6208
为了实现并发，oracle数据库使用了锁机制。要了解锁，首先要了解视图v$lock。 v$lock这个视图列出 Oracle 服务器当前拥有的锁以及未完成的锁请求。如果你觉着 session 处于等待事件队列当中，那你应该检查视图v$lock。   v$lock中的常用列有以下列：      sid：持有锁的会话SID，通常与v$session关联。      type：锁的类型，其中T...
oracle_manage_sql.rar_oracle
09-19
在Oracle中，我们可以使用`V$SESSION`视图来监控当前系统中的所有会话信息。例如，运行以下SQL查询可以列出所有活动的会话： ```sql SELECT sid, serial#, username, osuser, machine, status, program FROM v$...
Oracle回滚段使用查询代码详解
09-09
FROM v$lock l, v$session s, v$rollname r, v$transaction t WHERE l.sid = s.sid(+) AND TRUNC(l.id1/65536) = r.usn AND l.type = 'TX' AND t.ses_addr = s.saddr AND l.lmode = 6; ``` 这个查询会返回...
如何查询占CPU高的oracle进程
09-11
当遇到Oracle进程占用CPU过高时，需要进行一系列诊断和优化步骤。本文将详细介绍如何查询和处理这些高CPU占用的Oracle进程。 首先，我们可以使用操作系统层面的工具来识别CPU高的进程。在Unix或Linux环境下，可以...
经典Oracle SQL语句收集.txt
最新发布
07-31
在Oracle数据库中，查询语句是最基本也是最重要的操作之一。它可以帮助我们从大量的数据中提取有用的信息。以下是一些经典的查询语句示例： #### 查询新建用户 ```sql SELECT username FROM dba_users WHERE ...
Oracle DBA基础知识
12-11
它是SGA中最大的部分之一，对提高Oracle性能至关重要。 ###### 1.2.3 重做日志缓冲区 重做日志缓冲区存储了所有未写入重做日志文件的事务记录，当事务提交时，这些记录会被写入重做日志文件，确保数据的一致性和...
oracle--v$lock 字段详解
weixin_30697239的博客
06-01 338
oracle--v$lock 字段详解 转载于:https://www.cnblogs.com/jing1617/p/10960470.html
Oracle数据库--表锁的情况查询
coder_doctor的博客
09-17 821
1.查找当前表级锁的SQL如下：select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$ses
oracle死锁 视图,死锁/V$lock表和相关视图
weixin_31119281的博客
04-06 359
oracle官网当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with...
oracle锁详解
richard的专栏（bigdata.han@gmail.com）
06-14 3841
Oracle 多粒度锁机制介绍 根据保护对象的不同，Oracle数据库锁可以分为以下几大类： (1) DML lock（data locks，数据锁）：用于保护数据的完整性； (2) DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）； (3) Internal locks 和latches（内部锁与闩）：
oracle学习笔记 锁相关视图及相关操作
韵筝的博客
12-12 1662
oracle学习笔记 锁相关视图及相关操作 我们继续研究锁 这节课主要研究锁的相关视图，以及锁的相关操作 主要是研究视图 然后通过视图查锁的问题 oracle对于锁来讲我们没有什么太多可操作的地方 但是我们可以通过视图找出锁的问题 一）v$transaction视图 第一个视图是v$transaction 就是oracle数据库所有活动的事务数 所有活动的事务每一个活动的事
ORACLE锁----原理篇
gguxxing008的专栏
10-21 536
在现代的多用户多任务系统中，必然会出现多个用户同时访问共享的某个对象，这个对象可能是表，行，或者内存结构，为了解决多个用户并发性访问带来的数据的安全性，完整性及一致性问题，必须要有一种机制，来使对这些共享资源的并发性访问串行化，oracle中的锁就可以提供这样的功能，当事务在对某个对象进行操作前，先向系统发出请求，对其加相应的锁，加锁后该事务就对该数据对象有了一定的控制权限，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作（可以做select动作，但select 利用的是undo中的前镜像数据了）
访问V$LOCK视图oracle11g出现性能问题
goodboychen的专栏
03-30 3485
最近发现oracle11g有个问题，拿出来和大家讨论。是在Oracle 11.2.0.3 For Linux X64环境中。 　　检查数据库是否存在锁信息，在查询V$LOCK视图时发现很长时间没有响应，甚至一度认为数据库HANG住： 　　SQL> select * from v$lock where type != 'MR'; 　　ADDR KADDR SID TY ID1 ID
oracle表锁住 解锁办法
jadeball的专栏
05-21 4013
第一种方法：用系统账户如sys      as  SYSDBA 登录进去1、查看数据库锁,诊断锁的来源及类型： 　　select object_id,session_id,locked_mode from v$locked_object; 或者用以下命令： select b.owner,b.object_name,l.session_id,l.locked_mode from v$lock
Oracle动态性能视图(V$)深度解析与应用
V$视图 是Oracle服务器维护的一系列基础视图，由前缀"V_$"标识。这些视图不允许直接修改，并且由SYS用户访问。V$视图提供有关操作系统、进程、内存分配、SQL执行情况等的数据，帮助DBA诊断性能问题和进行系统调优。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值