开发者社区> 东方瀚海鲍> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PLSQL_Oracle Lock锁的处理(案例)

简介: 2014-06-21 Created By BaoXinjian 一、摘要 设立封锁机制主要是为了对并发操作进行控制,对干扰进行封锁,保证数据的一致性和准确性。Oracle数据库封锁方式有三种:共享封锁,独占封锁,共享更新封锁 封锁类型 内部级封锁 DDL级封锁(字典/语法分析封锁) ...
+关注继续查看

2014-06-21 Created By BaoXinjian

一、摘要


设立封锁机制主要是为了对并发操作进行控制,对干扰进行封锁,保证数据的一致性和准确性。Oracle数据库封锁方式有三种:共享封锁,独占封锁,共享更新封锁

封锁类型

  • 内部级封锁
  • DDL级封锁(字典/语法分析封锁)
  • DML级封锁

数据库DML封锁方式

  • 享封锁
  • 独占封锁
  • 共享更新封锁

特殊锁 - 死锁

  • Procedure类型死锁
  • Table类型死锁

 

二、具体解析


1. 封锁类型

1.1 内部级封锁

内部级封锁是用于保护ORACLE内部结构,由系统内部实现,用户不能访问,因此我们不必对此做过多的了解。

1.2 DDL级封锁(字典/语法分析封锁)

DDL级封锁也是由ORACLE RDBMS来控制,它用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对SQL定义语句作语法分析时自动地加锁,无需用户干予。字典/语法分析封锁共分三类:

  • 字典操作锁:用于对字典操作时,锁住数据字典,此封锁是独占的,从而保护任何一个时刻仅能对一个字典操作。
  • 字典定义锁:用于防止在进行字典操作时又进行语法分析,这样可以避免在查询字典的同时改动某个表的结构。
  • 表定义锁:用于 一个SQL语句正当访问某个表时,防止字典中与该表有关的项目被修改。

1.3 DML级封锁

DML级封锁用于控制并发事务中的数据操纵,保证数据的一致性和完整性,其封锁对象可以是表或行。
对用户的数据操纵,Oracle可以自动为操纵的数据进行封锁,但如果有操纵授权,则为满足并发操纵的需要另外实施封锁。DML封锁可由一个用户进程以显式的方式加锁,也可通过某些SQL语句隐含方式实现。
DML锁有如下三种封锁方式:

  • 共享封锁方式(SHARE)
  • 独占封锁方式(EXCLUSIVE)
  • 共享更新封锁(SHARE UPDATE)

 

2. 数据库DML封锁方式

2.1 共享封锁方式

  • 概念:共享方式的表封锁是对表中的所有数据进行封锁,该锁用于保护查询数据的一致性,防止其它用户对已封锁的表进行更更新。其它用户只能对该表再施加共享 方式的锁,而不能再对该表施加独占方式的封锁,共享更新锁可以再施加,但不允许持有共享更新封锁的进程做更新。共享该表的所有用户只能查询表中的数据,但 不能更新。共享方式的表封锁只能由用户用SQL语句来设置,基语句格式如下:
  • 加锁语法
    • LOCK TABLE <表名>[,<表名>]...IN SHARE MODE [NOWAIT]
  • 持有共享锁的事务,在出现如下之一的条件时,便释放其共享锁:
    • 执行COMMIT或ROLLBACK语句。
    • 退出数据库(LOG OFF)。
    • 程序停止运行。
  • 注意
    • 共享方式表封锁常用于一致性查询过程,即在查询数据期间表中的数据不发生改变。
    • 执行该语句,对一个或多个表施加共享方式的表封锁。当指定了选择项NOWAIT,若该封锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句。

2.2 独占封锁方式

  • 概念:独占方式表封锁是用于封锁表中的所有数据,拥有该独占方式表封锁的用户,即可以查询该表,又可以更新该表,其它的用户不能再对该表施加任何封锁(包括共享、独占或共享更新封锁)。其它用户虽然不能更新该表,但可以查询该表。
  • 语法
    • LOCK TABLE <表名>[,<表名>]....IN EXCLUSIVE MODE [NOWAIT]
  • 拥有独占方式表封锁的事务,在出现如下条件之一时,便释放该封锁:
    • 执行COMMIT或ROLLBACK语句。
    • 退出数据库(LOG OFF)
    • 程序停止运行。
  • 注意
    • 独占方式封锁通常用于更新数据,当某个更新事务涉及多个表时,可减少发生死锁。
    • 独占方式的表封锁也可以在用户执行DML语句INSERT、UPDATE、DELETE时隐含获得。

2.3 共享更新封锁方式

  • 概念:共享更新封锁是对一个表的一行或多行进行封锁,因而也称作行级封锁。表级封锁虽然保证了数据的一致性,但却减弱了操作数据的并行性。行级封锁确保在 用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改。因而行级锁即可保证数据的一致性又能提高数据操作的迸发性。
  • 语法
    • (1). 执行如下的SQL封锁语句,以显示的方式获得:
      • LOCK TABLE <表名>[,<表名>].... IN SHARE UPDATE MODE [NOWAIT]
    • (2). 用如下的SELECT ...FOR UPDATE语句获得:
      • SELECT <列名>[,<列名>]...
      • FROM <表名>
      • WHERE <条件>
      • FOR UPDATE OF <列名>[,<列名>].....[NOWAIT]
  • 当出现如下之一的条件,便释放共享更新锁:
    • 执行提交(COMMIT)语句;
    • 退出数据库(LOG OFF)
    • 程序停止运行。
  • 注意
    • 执行ROLLBACK操作不能释放行锁。
    • 从上面讲述可见,ORACLE RDBMS的加锁机制,解决了并发事务的相容与互斥问题。相容保证事务的并发性,互斥确保数据的一致性。不同用户锁的相容与互斥关系由下图给出。
    • 其中最后一行最后一列为其它用户提供在不同行上设置SHARE UPDATE锁。但当用户1在某行上进行更新操作时,用户2只有等待用户1提交事务后,才能更新自己所封锁的行。
    • 一旦用户对某个行施加了行级封锁,则该用户可以查询也可以更新被封锁的数据行,其它用户只能查询但不能更新被封锁的数据行.如果其它用户想更新该表 中的数据行,则也必须对该表施加行级锁.即使多个用户对一个表均使用了共享更新,但也不允许两个事务同时对一个表进行更新,真正对表进行更新时,是以独占 方式封锁表,一直到提交或复原该事务为止。行锁永远是独占方式锁。

 

3. 特殊锁 - 死锁

封锁虽然能够有效的解决并发操作,但是任何资源的独占都会有死锁的危险。

例子:

1:用户1对A表进行Update,没有提交。

2:用户2对B表进行Update,没有提交。

此时双反不存在资源共享的问题。

3:如果用户2此时对A表作update,则会发生阻塞,需要等到用户一的事物结束。

4:如果此时用户1又对B表作update,则产生死锁。此时Oracle会选择其中一个用户进行会滚,使另一个用户继续执行操作。

起因:

Oracle的死锁问题实际上很少见,如果发生,基本上都是不正确的程序设计造成的,经过调整后,基本上都会避免死锁的发生。

从系统性能上考虑,应该尽可能减少资源竞争,增大吞吐量,因此用户在给并发操作加锁时,应注意以下几点:

  • 对于UPDATE和DELETE操作,应只封锁要做改动的行,在完成修改后立即提交。
  • 当多个事务正利用共享更新的方式进行更新,则不要使用共享封锁,而应采用共享更新封锁,这样其它用户就能使用行级锁,以增加并行性。
  • 尽可能将对一个表的操作的并发事务施加共享更新锁,从而可提高并行性。
  • 在应用负荷较高的期间,不宜对基础数据结构(表、索引、簇和视图)进行修改。

 

三、案例分析 - 死锁


处理死锁所涉及的表:v$session,v$lock,v$locked_object,dba_objects,dba_blockers。

  • 动态视图v$session
    • 主要显示了当前数据库连接的会话信息,对于检测死锁问题主要有这几个字段可以用:sid,serial#,username。
  • 动态视图v$lock
    • 主要存放的每个session获得的相应的锁的信息,但不一定是死锁。里面还包含了锁的类型,创建时间,地址等信息。
  • 动态视图v$locked_object
    • 包含里面的session_id对应与v$session里面的sid字段,锁的对象id,即v$lock表中锁所锁定的对象object_id,还包括了操作系统用户、oracle用户、锁模式等信息。
  • dba_objects
    • 就不用详细介绍了,预了解可以参考官方文档。
  • dba_blockers
    • 里面只有一个字段HOLDING_SESSION,是拥有锁的session_id和SID同义。

 

1. Procedure 死锁

(1). SQL查询

    SELECT b .sid,
           b. SERIAL#,
           a.OBJECT,
          'alter system kill session   '
           || ''''
           || b.sid
           || ','
           || b. SERIAL#
           || ''';'
          kill_command
      FROM sys.v_$access a, sys. v_$session b
     WHERE     a.TYPE = 'PROCEDURE'
           AND (   a.OBJECT LIKE UPPER ('%存储过程名%' )
                OR a.OBJECT LIKE LOWER ('%存储过程名%' ))
           AND a.sid = b .sid
           AND b. status = 'ACTIVE' ;

(2). 结果

    

2. Table 死锁

(1).SQL查询

      SELECT t2.username,
             t2 .sid,
             t2 .serial# ,
             t2 .logon_time ,
             t3 .sql_text
        FROM v$locked_object t1 , v$session t2 , v$sqltext t3
       WHERE t1.session_id = t2.sid AND t2.sql_address = t3 .address
    ORDER BY t2. logon_time;

(2).结果

    

3. 解锁

alter system kill session 'SID, SERIAL';

个人觉得:解锁不能乱解,在解锁前必须了解该session是做什么的,目前资料处理情况,一旦解锁完成后,该session已经完成处理部分和未完成部分的处理该如何处理,做好风险评估

一旦解锁完后处理后续数据问题后

必须对发生死锁的原因进行分析,因为死锁的发生一般是某些程式的设计出现了问题,需要解决根本原因

 

Thanks and Regards

ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
oracle常用sql整理
1、holder with holder_tree as (select distinct ff.INST_ID, level level_num, ff.SID||'-->'||ff.
828 0
Oracle锁基础知识
001 引言     在任何多用户数据库应用程序中,最终免不了会出现两个用户希望同时使用同一行的情况。数据库必须确保这在物理上不可能实现。事务的隔离性原则要求数据库确保:在一个会话的事务完成之前,另一个会话的事务不能影响此会话。
723 0
oracle 表级锁
表级锁 共享模式(in share mode) 共享更新模式(in share update mode) 排他锁模式 锁定表的通用语法: lock table 表名 in ; ...
697 0
Oracle 性能相关常用脚本(SQL)
在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通过,Oracle 11g可能要做相应调整。
785 0
Oracle与MySQL的几点区别
大家都经常使用Oracle数据库和MySQL数据库,虽然在大多数sql处理上都大同小异,但是还是有一些明显的区别,以下为整理的一些区别点,希望能够对各位有所帮助。
1099 0
Oracle lock 锁机制 总结
对于lock这个词,要从两方面理解,首先,它代表的是一种控制机制;其次,在这个机制中有个一成员也叫lock。 从机制的角度看,lock框架包括3个组件:resource structure(资源);lock structure(锁);enqueue(排队机制) resource和lock是数据结构,enqueue是使用的算法。
1160 0
Oracle表空间规划处理
配置: 操作系统:Windowns2000Server(Sp4,安装目录:C:\Winnt) 数据库系统:Oracle8.1.7(安装目录:C:\Oracle) 硬盘:5块SCSI硬盘(分C、D、E、F、G五个盘符) 应用系统:大型Erp系统(安装目录:C:\Erp)   一、前提: Oracle安装完毕之后,Erp系统安装之前,应对表空间作一个有效的规划,以免系统在运行之时,出现数据库不协调问题。
717 0
518
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载