精华内容
下载资源
问答
  • Oracle行级是一种排他,防止其他事务修改此行,但是不会阻止读取此行的操作。 在使用INSERT、UPDATE、DELETE 和SELECT…FOR UPDATE 等 语句时,Oracle会自动应用Oracle行级行级锁定。SELECT…FOR UPDATE ...
  • oracle行级

    千次阅读 2018-10-31 10:58:10
    Oracle有许多的,各种的效用是不一样的。下面重点介绍Oracle行级Oracle行级只对用户正在访问的行进行锁定。可以更好的保证数据的安全性,需要的朋友可以了解下 Oracle有许多的,各种的效用是不一样的...

    Oracle有许多的锁,各种锁的效用是不一样的。下面重点介绍Oracle行级锁,Oracle行级锁只对用户正在访问的行进行锁定。可以更好的保证数据的安全性,需要的朋友可以了解下

    Oracle有许多的锁,各种锁的效用是不一样的。下面重点介绍Oracle行级锁,Oracle行级锁只对用户正在访问的行进行锁定。可以更好的保证数据的安全性。 
    如果该用户正在修改某行,那么其他用户就可以更新同一表中该行之外的数据。 
    Oracle行级锁是一种排他锁,防止其他事务修改此行,但是不会阻止读取此行的操作。 
    在使用INSERT、UPDATE、DELETE 和SELECT…FOR UPDATE 等 语句时,Oracle会自动应用Oracle行级锁行级锁锁定。SELECT...FOR UPDATE 语句允许用户每次选择多行记录进行更新,这些记录会被锁定,且只能由发起查询的用户进行编辑。只有在回滚或提交事务之后,锁定才会释放,其他用户才可以编辑这些记录。
    SELECT...FOR UPDATE 语句的语法如下: 

    复制代码代码如下:


    SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 


    其中: 
    OF 子句用于指定即将更新的列,即锁定行上的特定列。 
    WAIT 子句指定等待其他用户释放锁的秒数,防止无限期的等待。 
    “使用FOR UPDATE WAIT”子句的优点如下: 
    ⒈防止无限期地等待被锁定的行; 
    ⒉允许应用程序中对锁的等待时间进行更多的控制。 
    ⒊对于交互式应用程序非常有用,因为这些用户不能等待不确定 
    4. 若使用了skip locked,则可以越过锁定的行,不会报告由wait n 引发的‘资源忙'异常报告 
    示例: 

    复制代码代码如下:


    create table t(a varchar2(20),b varchar2(20)); insert into t values('1','1'); insert into t values('2','2'); insert into t values('3','3'); insert into t values('4','4'); 


    现在执行如下操作: 
    在plsql develope中打开两个sql窗口, 
    在1窗口中运行sql 

    复制代码代码如下:


    select * from t where a='1' for update; 


    在2窗口中运行sql1 

    复制代码代码如下:


    1. select * from t where a='1'; 


    这一点问题也没有,因为行级锁不会影响纯粹的select语句 
    再运行sql2 

    复制代码代码如下:


    2. select * from t where a='1' for update; 


    则这一句sql在执行时,永远处于等待状态,除非窗口1中sql被提交或回滚。 
    如何才能让sql2不等待或等待指定的时间呢? 我们再运行sql3 

    复制代码代码如下:


    select * from t where a='1' for update nowait; 


    则在执行此sql时,直接报资源忙的异常。 
    若执行 

    复制代码代码如下:


    select * from t where a='1' for update wait 6; 


    则在等待6秒后,报 资源忙的异常。 
    如果我们执行sql4 
    4. 

    复制代码代码如下:


    select * from t where a='1' for update nowait skip Locke d; 


    则执行sql时,即不等待,也不报资源忙异常。 
    现在我们看看执行如下操作将会发生什么呢? 
    在窗口1中执行: 
    select * from t where rownum 《3 nowait skip Locked; 在窗口2中执行: 
    select * from t where rownum《6 nowait skip Locked; 
    select for update 也就如此了吧,insert、update、delete操作默认加行级锁,其原理和操作与select for update并无两样。 
    select for update of,这个of子句在牵连到多个表时,具有较大作用,如不使用of指定锁定的表的列,则所有表的相关行均被锁定,若在of中指定了需修改的列,则只有与这些列相关的表的行才会被锁定。

    展开全文
  • Oracle-解读

    千次阅读 2016-11-03 14:13:05
    如果是单用户的系统,那完全没有必要这个,就是因为有多用户并发操作,我们为了确保资源的安全性(也就是oracle的数据完整性和一致性)才引申出这个出来。Oracle 利用其机制来实现事务间的数据并发访问及数据...

    概念

    Oracle-OLAP和OLTP解读

    Oracle-index索引解读

    Oracle-分区表解读

    Oracle-锁解读

    Oracle-等待事件解读

    Oracle-procedure/cursor解读


    数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

    如果是单用户的系统,那完全没有必要这个锁,就是因为有多用户并发操作,我们为了确保资源的安全性(也就是oracle的数据完整性和一致性)才引申出这个锁出来。Oracle 利用其锁机制来实现事务间的数据并发访问及数据一致性。

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

    Oracle的锁机制是一种轻量级的锁定机制,不是通过构建锁列表来进行数据的锁定管理,而是直接将锁作为数据块的属性,存储在数据块首部。

    在 Oracle 数据库中,它并不是对某个表加上锁或者某几行加上锁, 锁是以数据块的一个属性存在的。 也就是说, 每个数据块本身就存储着自己数据块中数据的信息,这个地方叫 ITL( Interested Transaction List), 凡是在这个数据块上有活动的事务,它的信息就会记录在这里面供后续的操作查询,一保证事务的一致性。

    在oracle数据库中,不存在真正意义上属于某个对象或数据的锁。oracle锁的信息是数据块的一个物理属性,而不是逻辑上属于某个表或某个行。


    分类

    按用户和系统分可以分为自动锁和显示锁

    自动锁( Automatic Locks)

    当进行一项数据库操作时,缺省情况下,系统自动为此数据库操作获得所有有必要的锁。

    自动锁分为三种:

    • DML 锁
    • DDL 锁
    • systemlocks。

    显示锁( Manual Data Locks)

    某些情况下,需要用户显示的锁定数据库操作要用到的数据,才能使数据库操作执行得更好,显示锁是用户为数据库对象设定的。


    按锁级别分可以分为排它锁和共享锁

    排他锁(exclusive lock,即X锁)和共享锁(share lock,即S锁)

    排他锁(exclusive lock,即X锁)

    事务设置排它锁后,该事务单独获得此资源,另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。


    共享锁(share lock,即S锁)

    共享锁使一个事务对特定数据库资源进行共享访问——另一事务也可对此资源进行访问或获得相同共享锁。

    共享锁为事务提供高并发性,但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。


    按操作分可以分为DML锁、DLL锁和System Locks

    DML锁

    DML 锁用于控制并发事务中的数据操纵,保证数据的一致性和完整性。

    DML锁主要用于保护并发情况下的数据完整性。

    DML 语句能够自动地获得所需的表级锁(TM)与行级(事务)锁(TX)。

    它又分为:
    ( 1) TM 锁(表级锁)
    ( 2) TX 锁( 事务锁或行级锁)

    当 Oracle 执行 DML 语句时,系统自动在所要操作的表上申请 TM 类型的锁。当 TM 锁获得后,系统再自动申请 TX 类型的锁,并将实际锁定的数据行的锁标志位进行置位。

    这样在事务加锁前检查 TX锁相容性时就不用再逐行检查锁标志,而只需检查 TM 锁模式的相容性即可,大大提高了系统的效率。

    在数据行上只有 X 锁(排他锁)。

    在 Oracle 数据库中,当一个事务首次发起一个 DML 语句时就获得一个 TX 锁,该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML 语句时,第一个会话在该条记录上加锁,其他的会话处于等待状态。当第一个会话提交后, TX 锁被释放,其他会话才可以加锁。

    当 Oracle 数据库发生 TX 锁等待时,如果不及时处理常常会引起 Oracle 数据库挂起,或导致死锁的发生,产生 ORA-600 的错误。这些现象都会对实际应用产生极大的危害,如长时间未响应,大量事务失败等。


    TM 锁(表级锁)

    TM 锁用于确保在修改表的内容时,表的结构不会改变,例如防止在 DML 语句执行期间相关的表被移除。当用户对表执行 DDL 或 DML 操作时,将获取一个此表的表级锁。

    当事务获得行锁后,此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行 DDL 语句影响记录行的更新。

    事务也可以在进行过程中获得共享锁或排它锁,只有当事务显示使用 LOCK TABLE 语 句显示的定义一个排它锁时,事务才会获得表上的排它锁,也可使用 LOCK TABLE 显示的定义一个表级的共享锁。

    TM 锁包括了 SS、 SX、 S、 X 等多种模式,在数据库中用 0-6 来表示。不同的 SQL 操作产生不同类型的 TM 锁.

    TM 锁类型表
    这里写图片描述


    TX 锁( 事务锁或行级锁)

    当事务执行数据库插入、更新、删除操作时,该事务自动获得操作表中操作行的排它锁。

    事务发起第一个修改时会得到TX 锁(事务锁),而且会一直持有这个锁,直至事务执行提交(COMMIT)或回滚(ROLLBACK)。

    对用户的数据操纵, Oracle 可以自动为操纵的数据进行加锁,但如果有操纵授权,则为满足并发操纵的需要另外实施加锁。

    DML 锁可由一个用户进程以显式的方式加锁,也可通过某些 SQL 语句隐含方式实现。 这部分属于 Manual Data Locks。

    原理:一个事务要修改块中的数据,必须获得该块中的一个itl,通过itl和undo segment header中的transaction table,可以知道事务是否处于活动阶段。事务在修改块时(其实就是在修改行)会检查行中row header中的标志位,如果该标志位为0(该行没有被活动的事务锁住),就把该标志位修改为事务在该块获得的itl的序号,这样当前事务就获得了对记录的锁定,然后就可以修改行数据了,这也就是oracle行锁实现的原理。

    DML 锁有如下三种加锁方式:

    • 共享锁方式( SHARE)
    • 独占锁方式( EXCLUSIVE)
    • 共享更新锁( SHARE UPDATE)
      其中:
      SHARE, EXCLUSIVE 用于 TM 锁(表级锁)
      SHARE UPDATE 用于 TX 锁( 行级锁)

    共享方式的表级锁( Share)

    共享方式的表级锁是对表中的所有数据进行加锁,该锁用于保护查询数据的一致性,防止其它用户对已加锁的表进行更新。

    其它用户只能对该表再施加共享方式的锁,而不能再对该表施加独占方式的锁,共享更新锁可以再施加,但不允许持有共享更新封锁的进程做更新。

    共享该表的所有用户只能查询表中的数据,但不能更新。

    共享方式的表级锁只能由用户用 SQL 语句来设置.

    语句格式如下:

    LOCK TABLE <表名>[,<表名>]... IN SHARE MODE [NOWAIT]

    执行该语句,对一个或多个表施加共享方式的表封锁。

    当指定了选择项NOWAIT,若该锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句。

    持有共享锁的事务,在出现如下之一的条件时,便释放其共享锁:

    • A、执行 COMMIT 或 ROLLBACK 语句。
    • B、退出数据库( LOG OFF)。
    • C、程序停止运行。

    共享方式表级锁常用于一致性查询过程,即在查询数据期间表中的数据不发生改变。


    独占方式表级锁( Exclusive)

    独占方式表级锁是用于加锁表中的所有数据,拥有该独占方式表封锁的用户,即可以查询该表,又可以更新该表,其它的用户不能再对该表施加任何加锁(包括共享、独占或共享更新封锁)。

    其它用户虽然不能更新该表,但可以查询该表。

    独占方式的表封锁可通过如下的 SQL 语句来显示地获得:

    LOCK TABLE <表名>[,<表名>].... IN EXCLUSIVE MODE [NOWAIT]

    独占方式的表级锁也可以在用户执行 DML 语句 INSERT、UPDATE、DELETE时隐含获得

    拥有独占方式表封锁的事务,在出现如下条件之一时,便释放该封锁:

    • ( 1)、执行 COMMIT 或 ROLLBACK 语句。
    • ( 2)、退出数据库( LOG OFF)
    • ( 3)、程序停止运行。

    独占方式封锁通常用于更新数据,当某个更新事务涉及多个表时,可减少发生死锁.


    共享更新加锁方式( Share Update)

    共享更新加锁是对一个表的一行或多行进行加锁,因而也称作行级加锁。表级加锁虽然保证了数据的一致性,但却减弱了操作数据的并行性。

    行级加锁确保在用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改。
    因而行级锁即可保证数据的一致性又能提高数据操作的迸发性。

    可通过如下的两种方式来获得行级封锁:
    ( 1)、执行如下的 SQL 封锁语句,以显示的方式获得:

    LOCK TABLE < 表 名 >[,< 表 名 >].... IN SHARE UPDATE MODE
    [NOWAIT]

    ( 2)、用如下的 SELECT …FOR UPDATE 语句获得:

    SELECT <列名 >[,<列名 >]...FROM <表名 > WHERE <条件 > FOR
    UPDATE OF <列名>[,<列名>].....[NOWAIT]

    一旦用户对某个行施加了行级加锁,则该用户可以查询也可以更新被加锁的数据行,其它用户只能查询但不能更新被加锁的数据行.

    如果其它用户想更新该表中的数据行,则也必须对该表施加行级锁.即使多个用户对一个表均使用了共享更新,但也不允许两个事务同时对一个表进行更新,真正对表进行更新时,是以独占方式锁表,一直到提交或复原该事务为止。

    行锁永远是独占方式锁。

    当出现如下之一的条件,便释放共享更新锁:

    • ( 1)、执行提交( COMMIT)语句;
    • ( 2)、退出数据库( LOG OFF)
    • ( 3)、程序停止运行。

    执行 ROLLBACK 操作不能释放行锁。


    DLL锁( dictionary locks)

    DDL 锁用于保护数据库对象的结构,如表、索引等的结构定义。

    DDL 锁又可以分为:

    • 排它 DDL 锁、
    • 共享 DDL 锁、
    • 分析锁

    排它 DDL 锁

    创建、修改、删除一个数据库对象的 DDL 语句获得操作对象的 排它锁。

    如使用 alter table 语句时,为了维护数据的完成性、一致性、合法性,该事务获得一排它 DDL 锁


    共享 DDL 锁

    需在数据库对象之间建立相互依赖关系的 DDL 语句通常需共享获得 DDL锁。

    如创建一个包,该包中的过程与函数引用了不同的数据库表,当编译此包时该事务就获得了引用表的共享 DDL 锁。


    分析锁

    ORACLE 使用共享池存储分析与优化过的 SQL 语句及 PL/SQL 程序,使运行相同语句的应用速度更快。

    一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。

    分析锁是一种独特的 DDL 锁类型, ORACLE 使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。

    当一个事务修改或删除了共享池持有分析锁的数据库对象时, ORACLE 使共享池中的对象作废,下次在引用这条SQL/PLSQL 语 句时, ORACLE 重新分析编译此语句。

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

    字典/语法分析加锁共分三类:

    • ( 1)字典操作锁:
      用于对字典操作时,锁住数据字典,此封锁是独占的,从而保护任何一个时刻仅能对一个字典操作。

    • ( 2) 字典定义锁:
      用于防止在进行字典操作时又进行语法分析,这样可以避免在查询字典的同时改动某个表的结构。

    • ( 3)表定义锁:
      用于一个 SQL 语句正当访问某个表时,防止字典中与该表有关的项目被修改。


    悲观封锁和乐观封锁

    悲观封锁

    锁在用户修改之前就发挥作用:

    Select ..for update [nowait] 
    Select * from tab1 for update 

    用户发出这条命令之后,oracle将会对返回集中的数据建立行级封锁,以防止其他用户的修改。

    如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。

    • 1:对返回结果集进行update或delete操作会发生阻塞。左下角的时间执行了很久。
      这里写图片描述
    • 2:对该表进行ddl操作将会报:Ora-00054:resource busy and acquire with nowait specified.

    这里写图片描述

    原因分析 :
    此时Oracle已经对返回的结果集上加了排它的行级锁,所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放,产生的外在现象就是其他的操作将发生阻塞,这个这个操作commit或rollback.

    同样这个查询的事务将会对该表加表级锁,不允许对该表的任何ddl操作,否则将会报出ora-00054错误::resource busy and acquire with nowait specified.

    悲观的缺陷是,加锁的时间可能会很长,这样可能会长时间的限制其他用户的访问,也就是说悲观锁的并 发访问性不好.

    栗子

    会话A:

    在这里新开一个plsql窗口模拟会话A

    --建表
    create table xgj (name varchar2(20));
    --新增数据
    insert into xgj values('xiaogongjiang');
    --提交数据
    commit ;
    --使用for update方式获取排他行级锁
    select * from xgj where name='xiaogongjiang' for update ;

    会话B:

    在这里是在plsql中另外新开了一个窗口模拟会话B,不能在同一个会话窗口,否则测试不出来。

    alter table xgj add(salary  number(5));

    注意看左下角的时间,会看到已经执行时间了很长时间,如果会话A不提交则会一直等待,A提交后,马上执行成功。
    这里写图片描述

    乐观封锁

    乐观的认为数据在select出来到update数据并提交的这段时间数据不会被更改。乐观锁多个会话可以同时操作数据。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定,是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁,因为这样会更安全。

    比较常见的方式使用版本列来,每次更新时都和旧版本的数据比较。


    System Locks

    oracle使用不同类型的系统锁来保护内部数据库和内存结构.

    这些机制是用户无法访问的。


    死锁

    当两个用户希望持有对方的资源时就会发生死锁.

    即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚。

    场景

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

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

    3:如果用户 2 此时对 A 表作 update,则会发生阻塞,需要等到用户一的事物结束。
    4:如果此时用户 1 又对 B 表作 update,则产生死锁。此时 Oracle 会选择其中一个用户进行会滚,使另一个用户继续执行操作。


    起因分析

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

    在 Oracle 系统中能自动发现死锁,并选择代价最小的,即完成工作量最少的事务予以撤消,释放该事务所拥有的全部锁,记其它的事务继续工作下去。

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

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

    死锁后的解决办法

    如果死锁不能自动释放,就需要我们手工的 kill session

    生成Kill Session语句

    1. 查看有无死锁对象,如有 kill session
    SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
      FROM v$session
     WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
    

    如果有,会返回类似与如下的信息:

    这里写图片描述

    kill session:
    执行

    alter system kill session '646,3953';

    注意: 应当注意对于 sid 在 100 以下的应当谨慎,可能该进程对应某个application,如对应某个事务,可以 kill


    查看导致死锁的 SQL

    SELECT s.sid, q.sql_text
    FROM v$sqltext q, v$session s
    WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的
    ORDER BY piece;

    执行后,输入对应的sid即可查看对应的sql.

    这里写图片描述

    如果输入的sid找不到对应的sql,可以先执行查看谁锁了谁(2)的sql, 查到另外一个sid, 根据另外一个sid,会查到对应的sql .


    查看谁锁了谁

    SELECT s1.username
    || '@'
    || s1.machine
    || ' ( SID='
    || s1.sid
    || ' ) is blocking '
    || s2.username
    || '@'
    || s2.machine
    || ' ( SID='
    || s2.sid
    || ' ) '
    AS blocking_status
    FROM v$lock l1,
    v$session s1,
    v$lock l2,
    v$session s2
    WHERE s1.sid = l1.sid
    AND s2.sid = l2.sid
    AND l1.BLOCK = 1
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2;

    这里写图片描述
    或者

    SELECT 
    LPAD (' ', DECODE (l.xidusn, 0, 3, 0))
    || l.oracle_username
    User_name,
    o.owner,
    o.object_name,
    o.object_type,
    s.sid,
    s.serial#
    FROM v$locked_object l, dba_objects o, v$session s
    WHERE l.object_id = o.object_id AND l.session_id = s.sid
    ORDER BY o.object_id, xidusn DESC;

    这里写图片描述


    锁和阻塞

    概念

    通常来讲,系统如果平时运行正常,突然会停止不动,多半是被阻塞( Blocked)住了。 我们可以通过 v$lock 这张视图,看查看阻塞的信息。

    SQL> desc v$lock
    Name Type   Nullable Default Comments 
    ------- ----------- -------- ------- -------- 
    ADDR RAW(8) Y                        
    KADDR RAW(8) Y                        
    SID  NUMBER Y                        
    TYPE VARCHAR2(2) Y                        
    ID1  NUMBER Y                        
    ID2  NUMBER Y                        
    LMODE NUMBER Y                        
    REQUEST NUMBER Y                        
    CTIME NUMBER Y                        
    BLOCK NUMBER Y                        
    
    SQL> 

    这里写图片描述

    我们关注的比较多的是 request 和 block 字段。
    如果某个 request 列是一个非 0 值,那么它就是在等待一个锁。 如果 block 列是1,这个 SID 就持有了一个锁,并且阻塞别人获得这个锁。

    这个锁的类型由 TYPE字段定义。锁的模式有 LMODE 字段定义, ID1 和 ID2 字段定义了这个锁的相关信息。

    ID1 相同,就代表指向同一个资源。 这样就有可能有加锁者和等待者。

    LMODE 的 6 中模式参考上面的 TM 锁类型表

    这里写图片描述

    可以结合 v$lockv$session 视图来查询相关的信息:

    SELECT sn.username,
           m.SID,
           sn.SERIAL#,
           m.TYPE,
           DECODE(m.lmode,
                  0,
                  'None',
                  1,
                  'Null',
                  2,
                  'Row Share',
                  3,
                  'Row Excl.',
                  4,
                  'Share',
                  5,
                  'S/Row Excl.',
                  6,
                  'Exclusive',
                  lmode,
                  LTRIM(TO_CHAR(lmode, '990'))) lmode,
           DECODE(m.request,
                  0,
                  'None',
                  1,
                  'Null',
                  2,
                  'Row Share',
                  3,
                  'Row Excl.',
                  4,
                  'Share',
                  5,
                  'S/Row Excl.',
                  6,
                  'Exclusive',
                  request,
                  LTRIM(TO_CHAR(m.request, '990'))) request,
           m.id1,
           m.id2
      FROM v$session sn, v$lock m
     WHERE (sn.SID = m.SID AND m.request != 0)
          --存在锁请求,即被阻塞
        OR (sn.SID = m.SID
           --不存在锁请求,但是锁定的对象被其他会话请求锁定
           AND m.request = 0 AND lmode != 4 AND
           (id1, id2) IN (SELECT s.id1, s.id2
                             FROM v$lock s
                            WHERE request != 0
                              AND s.id1 = m.id1
                              AND s.id2 = m.id2))
     ORDER BY id1, id2, m.request;
    

    或者

    SELECT /*+ rule */
     s.username,
     DECODE(l.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) lock_level,
     o.owner,
     o.object_name,
     o.object_type,
     s.sid,
     s.serial#,
     s.terminal,
     s.machine,
     s.program,
     s.osuser
      FROM v$session s, v$lock l, dba_objects o
     WHERE l.sid = s.sid
       AND l.id1 = o.object_id(+)
       AND s.username IS NOT NULL;
    

    引起阻塞的几种常见情况

    ( 1) DML 语句引起阻塞
    ( 2)外键没有创建索引


    1.DML 语句引起阻塞

    当一个会话保持另一个会话正在请求的资源上的锁定时,就会发生阻塞。被阻塞的会话将一直挂起,直到持有锁的会话放弃锁定的资源为止。

    4 个常见的 dml 语句会产生阻塞:

    • ( 1) INSERT
    • ( 2) UPDATE
    • ( 3) DELETE
    • ( 4) SELECT…FOR UPDATE

    INSERT

    Insert 发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。

    当 2 个会话同时试图向表中插入相同的数据时,其中的一个会话将被阻塞,直到另外一个会话提交或会滚。一个会话提交时,另一个会话将收到主键重复的错误。回滚时,被阻塞的会话将继续执行。


    Update 和 Delete

    UPDATE 和 DELETE 当执行 Update 和 delete 操作的数据行已经被另外的会话锁定时,将会发生阻塞,直到另一个会话提交或会滚。


    Select …for update

    当一个用户执行 select..for update 对返回的结果集进行修改时,如
    果结果集已经被另一个会话锁定,此时 Oracle 已经对返回的结果集上加了排它的行级锁, 所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放(操作 commit 或 rollback.),产生的外在现象就是其他的操作将发生阻塞.

    同样这个查询的事务将会对该表加表级锁,不允许对该表的任何 ddl 操作,否则将会报出 Ora-00054:resource busy and acquire with nowait specified.

    可以通过发出 select ... for update nowait 的语句来避免发生阻塞,如果资源已经被另一个会话锁定,则会返回以下错误: Ora-00054:resource busy and acquire with nowait specified.


    2.外键没有创建索引

    如果系统中有主,外键引用关系,并且满足一下三个条件中的任意一个,那么就应该考虑给外键字段创建索引,否则系统的性能可能会下降甚至阻塞。

    • ( 1) 主表上有频繁的删除操作
    • ( 2) 主键上有频繁的修改操作。
    • ( 3) 业务上经常会出现主表和从表做关联查询的情况。

    第一和第二个条件操作的时候,主表会在从表上创建一个锁定,以保证主表主键的修改不会导致从表的数据在引用上出现问题,这是一个数据引用完整性的要求。

    如果主表上经常出现这样的删除或者是对主键列进行修改的操作,或者每次操作的记录数很多,都将会造成从表长时间被锁定,而影响其他用户的正常操作。

    比如主表每次删除 1000 行数据,它就需要扫描从表 1000 次,以确定每一行记录的改变都不会造成从表数据在引用上的不完整。

    特别是在 OLAP 系统中,从表经常会是非常巨大的表,在这种情况下,如果从表没有索引,那么查询几乎是不可想象的。



    Latch

    latch概述

    Latch属于 System Lock, 用于保护 SGA区中共享数据结构的一种串行化锁定机制。

    Latch 的实现是与操作系统相关的,尤其和一个进程是否需要等待一个latch、需要等待多长时间有关.

    Latch 是 Oracle 提供的轻量级锁资源, 是一种能够极快地被获取和释放的锁,能快速,短时间的锁定资源,

    Latch用于防止多个并发进程同时修改访问某个共享资源, 它只工作在 SGA 中, 通常用于保护描述 buffer cache 中 block 的数据结构。

    比如 SGA 中,各种数据被反复从磁盘读取到内存,又被重新写回到磁盘上,如果有并发的用户做相同的事情, Oracle 必须使用一种机制,来保证数据在读取的时候,只能由一个会话来完成,这种保护机制就是 Latch。

    • 并发( concurrency): 是说有超过两个以上的用户对同样的数据做修改(可能包括插入,删除和修改)。

    • 并行( parallel): 是说将一件事情分成很多小部分,让每一部分同时执行,最后将执行结果汇总成最终结果。

    与每个 latch 相联系的还有一个清除过程,当持有 latch 的进程成为死进程时,该清除过程就会被调用。

    Latch 还具有相关级别,用于防止死锁,一旦一个进程在某个级别上得到一个 latch,它就不可能再获得等同或低于该级别的 latch。

    Latch 不会造成阻塞,只会导致等待。 阻塞是一种系统设计上的问题,等待是一种系统资源争用的问题。

    spin概述

    比如数据缓存中的某个块要被读取,我们会获得这个块的 latch, 这个过程叫做 spin,另外一个进程恰好要修改这个块,他也要 spin 这个块,此时他必须等待,当前一个进程释放 latch 后才能 spin 住,然后修改, 如果多个进程同时请求的话,他们之间将出现竞争,没有一个入队机制,一旦前面进程释放所定,后面的进程就蜂拥而上,没有先来后到的概念, 并且这一切都发生的非常快,因为Latch 的特点是快而短暂。

    SPIN 与休眠( sleep)
    Oracle 选择了 spin,让进程继续占有 CPU,运行一些空指令,之后继续请求,继续 spin,直到达到_spin_count 值,这时会放弃 CPU,进行短暂的休眠,再继续刚才的动作。

    进程休眠的时间也是存在算法的.休眠的阀值限制由隐含参数_max_exponential_sleep控制, 默认是 2 秒.

    如果当前进程已经占用了别的 Latch,则他的休眠时间不会太长(过长会引起别的进程的 Latch 等待),此时的休眠最大时间有隐含参数_max_sleep_holding_latch 决定, 默认是 4 厘秒.

    总之,Latch 获取的流程: 请求-SPIN-休眠-请求-SPIN-休眠 … … 占用。


    Latch 和 Lock

    从某种意义上说, Latch 是内存中的资源锁,数据库对象(表,索引等)的锁叫Lock。

    Latch 和 Lock 的区别:

    • ( 1) . Latch 是对内存数据结构提供互斥访问的一种机制,而 Lock 是以不同的模式来套取共享资源对象,各个模式间存在着兼容或排斥,从这点看出, Latch的访问,包括查询也是互斥的,任何时候,只能有一个进程能 spin 住内存的某一块,幸好这个过程是相当的短暂,否则系统性能将没的保障,从 9I 开始,允许多个进程同时查询相同的内存块。

    • ( 2) . Latch 只作用于内存中,他只能被当前实例访问,而 Lock 作用于数据库对象,在 RAC 体系中实例间允许 Lock 检测与访问

    • ( 3) . Latch 是瞬间的占用,释放, Lock 的释放需要等到事务正确的结束,他占用的时间长短由事务大小决定

    • ( 4) . Latch 是非入队的,而 Lock 是入队的

    • ( 5) . Latch 不存在死锁,而 Lock 中存在。


    Latch 争用

    如果发现系统中经常由于 Lock 导致用户等待

    这时需要考虑系统在逻辑设计上是否有问题,比如多用户对主键的删除或者修改,是否有用户使用 select … for update 这样的语法,外键是否创建索引的因素。 这些因素是需要结合系统的业务逻辑性来进行数据库对象设计的。

    如果发现系统慢是因为很多的 Latch 争用

    就要考虑系统及数据库自身设计上是否存在问题,比如是否使用绑定变量,是否存在热快,数据存储参数设计是否合理等因素。
    导致 Latch 争用而等待的原因非常多,内存中很多资源都可能存在争用。

    最常见的两类 latch 争用如下:
    ( 1) 共享池中的 Latch 争用。
    ( 2)数据缓冲池中的 latch 争用。


    共享池中的 Latch 争用

    共享池中如果存在大量的 SQL 被反复分析,就会造成很大的 Latch 争用和长时间的等待, 最常见的现象就是没有绑定变量。

    最常见的集中共享池里的 Latch 是 library cache。

    可以通过一下 SQL 来查询:

    select * from v$latchname where name like 'library cache%';

    这里写图片描述

    在分析系统性能时,如果看到有 library cache 这样的 Latch 争用,就可以断定是共享池中出现了问题,这种问题基本是由 SQL 语句导致的,比如没有绑定变量 或者一些存储过程被反复分析。

    资源的争用可以通过如下 SQL 来查看

     select event,count(*) from v$session_wait group by event;

    这里写图片描述


    数据缓冲池中的 latch 争用

    访问频率非常高的数据块被称为热快( Hot Block),当很多用户一起去访问某几个数据块时,就会导致一些 Latch 争用.

    最常见的 latch 争用有:

    • ( 1) buffer busy waits
    • ( 2) cache buffer chain

    这两个 Latch 的争用分别发生在访问数据块的不同时刻。

    产生这些 Latch 争用的直接原因是太多的会话去访问相同的数据块导致热快问题, 造成热快的原因可能是数据库设置导致或者重复执行的 SQL 频繁访问一些相同的数据块导致。

    展开全文
  • oracle锁

    2016-08-16 12:01:27
    oracle锁
    一、ANSI/ISO事务的隔离级别指的是,一个事务对数据库的修改与另外一个事务的隔离程度
    
    1.两个并发事务T1和T2在访问相同的行,可能存在的三种问题
    a.幻像读取---T1通过where条件进行查询返回的行,T2插入了新的纪录,正好满足T1的where的条件,这时T1再次查询
    看到了T2新插入的值,对于T1来说就像变魔术一样
    b.不可重复读取---T1读取了1行,这时T2修改了T1读取的纪录,当T1再次读取的时候发现数据不一样了,这个称为
    不可重复读
        c.脏读---T1更新了1条纪录的内容但是并没有进行提交,T2读取更新后的行,然后T1执行了回滚,T2所读取的行就无效了
        
    2.为了处理上述问题,sql标准定义了几种事务隔离级别,按照隔离性级别从低到高依次为
    a. READ UNCOMMITTED :幻像读、不可重复读和脏读都允许
    b. READ COMMITTED   :允许幻像读和不可重复读,但是不允许脏读
    c. REPEATABLE READ  :允许幻像读,但是不允许不可重复读和脏读
    d. SERIALIZABLE     :幻像读、不可重复读和脏读都不允许
    二、oracle支持READ COMMITTED和SERIALIZABLE两种事务的隔离级别,不支持其他2种,oracle默认的隔离级别是READ COMMITTED,这里其实由于undo的作用不会出现脏读
    三、事务的隔离级别可以使用 set transaction 语句设置
        set transaction isolation level serializable;
    四、锁模式
      1.排他锁:简记为X锁,T1对数据对象A加上X锁,则只允许T1读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T1释放A上的锁。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁
      2.共享锁:共享锁又称为读锁(Share lock,简记为S锁),若事务T1对数据对象A加上S锁,则其它事务只能再对A加S锁,而不能加X锁,直到T1释放A上的S锁。
    五、oracle锁分类
        分为三大类
        1.dml锁,主要是保护数据,主要有表锁和行锁,防止并发的ddl和dml操作导致的破坏
        分为2类
        a.Row lock(TX)
         行上的锁,insert、update、delete、merge、select ... for update,和别的数据库需要单独的在内存中存在一个锁列表不同,oracle是通过在数据块
         头部包含行锁的信息的
         V$TRANSACTION视图列出了活动的事务
        b.Table locks(TM)
         表上的锁,insert、update、delete、merge、select ... for update,lock table语句
         这里又分为几类
         i.Row Share (RS):行共享锁
           也叫做subshare table lock (SS),表示事务在表上的锁主要是锁定表中的行,行共享锁是表锁中最不受限制
           的模式,为表提供了最高的并发度
         ii.Row Exclusive Table Lock (RX)
           也叫做subexclusive table lock (SX),一般指的是更新表行的时候持有的锁或者select for update语句,它允许其他事务
           select,insert,update,delete或同时在同一个表上进行行锁,因此sx允许多个事务获取同一个表的SX和subshare表锁
         iii.Share Table Lock (S)
           一个表级共享锁,允许其他事务select(不是select ... for update),但是只有当前保持共享锁的事务才可以修改内容,又因为可以多个事务
           都在表上加共享锁,因此保持此锁不一定可以修改对应的内容
         iv.Share Row Exclusive Table Lock (SRX)
           也可以叫做share-subexclusive table lock (SSX),它比S锁更严格,同一时刻只能有一个事务请求到ssx锁,请到到共享锁后,允许其他事务进行查询,但是不可以进行
           更新
         v.Exclusive Table Lock (X)
           这个是最严格的锁,它禁止其他事务执行任何类型的dml语句或放置任何类型的表锁
        2.ddl锁,主要是保护对象的结构,这里的ddl是data dictionary (DDL) lock,不是Data Definition Language,
          从字面上看这个是数据字典锁,就是对数据对象进行加锁保护,ddl锁是oracle自动控制的,用户无法进行显示请求
          比如用户创建一个存储过程,oracle会自动在存储过程中定义的对象上加锁,主要是阻止在程序编译过程中这些对象被修改或删除
          a.Exclusive DDL Locks        
          b.Share DDL Locks  
          c.Breakable Parse Locks      
        3.system锁,主要是保护内部数据结构     
    六、查看锁类型
    1.我们执行下面的语句
      SQL> select * from dept for update;
        --查看这个会加什么锁
        SQL> select se.USERNAME,lo.type,decode(lo.lmode,0,'none',1,'null',2,'row-s(ss)',3,'row-x(sx)',4,'share',5,'s/row-x(ssx)',6,'exclusive',to_char(lo.lmode)) lmode from v$lock lo,v$session se where lo.sid=se.sid
        2  and se.username='SCOTT';
    USERNAME                       TY LMODE
    ------------------------------ -- ----------------------------------------
    SCOTT                          TX exclusive
    SCOTT                          AE share
    SCOTT                          TM row-x(sx)
    --提一条TX表示的是事务锁也就是行锁,exclusive是排他锁,也就是行上的排他锁
    --第二条AE表示的是编辑锁,share表示的时共享锁,也就是编辑共享锁
    --第三条TM表示的是dml锁或对象锁也就是表锁,row-x(sx)表示的时行排他锁
    这里可以通过v$lock_type来查看
    其实在行上加的锁也就会自动升级到表上,一个事务如果在行上加了一个锁的话,那么这个事务也会请求在表上
    增加这个行锁,因此tm类型的锁区分为行和表,这里也就是为什么insert、update、delete、merge、select ... for update
    即会造成tx锁也会造成tm锁
    展开全文
  • Oracle

    2015-12-30 22:27:54
    1.什么是?  数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的...

    1.什么是锁?


            数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

            加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

            在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Locks,即S锁)。当数据对象被加上排它锁时,其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取,但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。


    2.Oracle的锁都有什么类型?


            根据保护的对象不同,Oracle数据库锁可以分为以下几大类:DML锁(data locks,数据锁),用于保护数据的完整性;DDL锁(dictionary locks,字典锁),用于保护数据库对象的结构,如表、索引等的结构定义;内部锁和闩(internal locks and latches),保护数据库的内部结构。

            DML锁的目的在于保证并发情况下的数据完整性,。在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁,TX锁称为事务锁或行级锁。

            当Oracle 执行DML语句时,系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后,系统再自动申请TX类型的锁,并将实际锁定的数据行的锁标志位进行置位。 这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志,而只需检查TM锁模式的相容性即可,大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式,在数据库中用0-6来表示。不同的SQL操作产生不同类型的TM锁。

            在数据行上只有X锁(排他锁)。在 Oracle数据库中,当一个事务首次发起一个DML语句时就获得一个TX锁,该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时,第一个会话在该条记录上加锁,其他的会话处于等待状态。当第一个会话提交后,TX锁被释放,其他会话才可以加锁。

            当Oracle数据库发生TX锁等待时,如果不及时处理常常会引起Oracle数据库挂起,或导致死锁的发生,产生ORA-60的错误。这些现象都会对实际应用产生极大的危害,如长时间未响应,大量事务失败等。



            今天首先给大家介绍了什么是锁以及Oracle中锁的类型,接下来给大家详细的分享一下什么是TM锁和TX锁。







    展开全文
  • ORACLE

    2015-02-10 23:23:48
    若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。     加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务...
  • oracle的分类

    千次阅读 2017-10-13 10:35:46
    若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。  是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该...
  • ORACLE锁

    2015-07-03 07:57:23
    Oracle有两种模式的:(按模式进行分类) 排他(exclusive lock,即X) 共享 (shared lock 即S)   加上排他,其他的事务不能对其进行读取和更改.注意加上排他我们一般也认为可以进行读取,但这个...
  • Oracle数据库处理SQL都会经过三个过程:解析(parse)、执行(exec)、返回结果(fetch)。为了便于理解,我们首先省略的SQL的处理过程,而直接介绍数据块的读取或修改过程。 物理...
  • oracle 行级

    千次阅读 2010-11-09 16:53:00
    DML事务锁定的机制 锁定能够保证当某个用户正在更新表里的一行数据时,其他用户不能同时更新相同的数据行,而且也不能删除或修改被更新的表。 锁定分为两种级别:行级别(TX)...  Oracle在对
  • oracle手动

    千次阅读 2015-06-04 08:41:17
    [转载]oracle手动表  手工表: lock table tbl_t1 in row share mode nowait; --2 lock table tbl_t1 in share update mode nowait; --2 lock table tbl_t1 in row exclu
  • ORACLE机制

    2011-07-05 13:53:50
    数据库使用(lock)来保证任何给定时刻最多只有一个事务在修改给定的一段数据。实质上讲,正是机制才...ORACLE的封锁策略: 1、只有当修改时,Oracle在行级上定数据,不要把锁定上升到块或表级。 2、Oracle决不
  • Oracle处理

    2019-04-19 17:24:47
    一:解决方案 --查询 select t.SID, t.SERIAL#, t2.OBJECT_TYPE, t.USERNAME ORACLE_USER, t2.OBJECT_NAME, t.OSUSER, t.MACHINE, t3.SQL_TEXT from v$sess...
  • Oracle锁机制

    2018-05-09 11:08:30
    一、Oracle锁机制 1、什么是 是控制“共享资源”并发存取的一种机制。注意这里说“共享资源”而不仅指“数据行”,数据库的却在行一级对表的数据加锁,但是数据库也在其它地方对各种资源的并发存取使用。...
  • Oracle悲观和乐观

    2009-12-08 22:30:00
    什么叫悲观呢,悲观顾名思义,就是对数据的冲突采取一种悲观的态度,也就是说假设数据肯定会冲突,所以在数据开始读取的时候就把数据锁定住。而乐观就是认为数据一般情况下不会造成冲突,所以在数据进行提交...
  • Oracle悲观和乐观

    千次阅读 2009-08-23 03:47:00
    什么叫悲观呢,悲观顾名思义,就是对数据的冲突采取一种悲观的态度,也就是说假 设数据 肯定会冲突,所以在数据开始读取的时候就把数据锁定住。而乐观就是认为数据一般情况下不会造成冲突,所以在数据
  • oracle锁机制

    2017-09-29 14:47:51
    1. 类型 的开始和释放一般是由事务...即多个事务可以同时并发读取同一个资源,但不同时并发执行写操作。1.2 独占 独占又称为写,当对数据库进行写操作时会添加独占。独占对锁定的资源,只能有由持有该
  • ORACLE锁深入分析

    2011-08-19 19:12:34
    Oracle数据库中,DML主要包括TM和TX,其中TM称为表级,TX称为事务或行级。? 当Oracle执行DML语句时,系统自动在所要操作的表上申请TM类型的。当TM获得后,系统再自动申请TX类型的,并将实际...
  • Oracle lock 机制 总结

    千次阅读 2012-04-11 19:59:00
    对于lock这个词,要从两方面理解,首先,它代表的是一种控制机制;...lock structure();enqueue(排队机制) resource和lock是数据结构,enqueue是使用的算法。 下面看一下lock的组成结构图: resource struc
  • http://www.dedecms.com/web-art/shujuku/Oracle/20061008/37324.html 介绍了Oracle数据库的...本文通过对Oracle数据库机制的研究,首先介绍了Oracle数据库的种类,并描述了实际应用中遇到的与相关的异常情
  • 关于oracle的级别以及介绍

    千次阅读 2018-05-15 15:40:33
    关于oracle的级别以及介绍 (2015-03-21 22:52:25)转载▼标签: 股票分类: oracle语句oracle造成表的情况:一、查看的对象视图:select object_id,session_id,locked_mode from v$locked_object;...
  • oracle有两种模式的:排他(exclusive lock,即X)和共享(share lock,即S)。 共享:如果事务T对数据A加上共享后,则其他事务只能对A再加共享,不能加排他。获准共享的事务只能读数据,不能...
  • ORACLE中有两种类型:排它(Exclusive Locks即X)和共享(Share Locks即S)  加了Exclusive Locks后,其他事务不能对此对象进行读取和修改;加了share locks后,其他事务能对此对象进行读取但不能...
  • Oracle 数据库机制

    2008-04-10 09:18:00
    若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 50,222
精华内容 20,088
关键字:

oracle读取锁