ITPub博客

首页 > 数据库 > Oracle > oracle--lock分析

oracle--lock分析

原创 Oracle 作者:zuoqi_71 时间:2014-11-26 13:40:27 0 删除 编辑
如果有两个会话,每个会话都持有另一个会话想要的资源,此时就会发生死锁。
用下面实验来说明死锁的产生原因和解决办法。
grant select on v_$mystat to scott;

SESSION1:
SQL> select sid from V$mystat where rownum=1;
    SID
-------
    125
    
SQL> create table t2 as select * from emp;
SQL> select * from t2 where empno=7369;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> update t2 set sal=sal+200 where empno=7369;
SQL> select * from t2 where empno=7369;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80       1000                    20

SESSION2:
SQL> select sid from V$mystat where rownum=1;
       SID
----------
       142

SQL> select * from t2 where empno=7900;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

SQL> update t2 set sal=sal+200 where empno=7900;
1 row updated.

SQL> select * from t2 where empno=7900;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81       1150                    30

SESSION3: longin as sysdba

SQL> @lock.sql
Wed Nov 26 02:38:54 CST 2014

    SID  SERIAL# SPID   TY OBJECT_NAME  COMMAND    MODE_HELD   MODE_REQUES LOCK_ID1 LOCK_ID2 Blocking    CTIME STATUS
------- -------- ------ -- ------------ ---------- ----------- ----------- -------- -------- -------- -------- --------
    125        7 11931  TM T2           UNKWON     Row-X (SX)  None        68720    0        0             121 INACTIVE
    125        7 11931  TX              UNKWON     Exclusive   None        196622   768      0             121 INACTIVE
    142       11 11975  TM T2           UNKWON     Row-X (SX)  None        68720    0        0              29 INACTIVE
    142       11 11975  TX              UNKWON     Exclusive   None        393236   764      0              29 INACTIVE
    
SESSION1:
SQL> update t2 set sal=sal-200 where empno=7900; 
出现等待

SQL> @lock.sql
Wed Nov 26 03:26:14 CST 2014

    SID  SERIAL# SPID   TY OBJECT_NAME  COMMAND    MODE_HELD   MODE_REQUES LOCK_ID1 LOCK_ID2 Blocking    CTIME STATUS
------- -------- ------ -- ------------ ---------- ----------- ----------- -------- -------- -------- -------- --------
    125        7 11931  TM T2           UPDATE     Row-X (SX)  None        68720    0        0            2961 ACTIVE
    125        7 11931  TX T2           UPDATE     Exclusive   None        196622   768      0            2961 ACTIVE
    125        7 11931  TX T2           UPDATE     None        Exclusive   393236   764      0               9 ACTIVE
    142       11 11975  TM T2           UNKWON     Row-X (SX)  None        68720    0        0            2869 INACTIVE
    142       11 11975  TX              UNKWON     Exclusive   None        393236   764      Y            2869 INACTIVE
    
SESSION2:
SQL> update t2 set sal=sal-200 where empno=7369;

死锁发生,session2被阻塞
系统回滚session1跟死锁有关的sql。

SESSION1:
update t2 set sal=sal-200 where empno=7900
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SQL> @lock.sql
Wed Nov 26 03:27:11 CST 2014

    SID  SERIAL# SPID   TY OBJECT_NAME  COMMAND    MODE_HELD   MODE_REQUES LOCK_ID1 LOCK_ID2 Blocking    CTIME STATUS
------- -------- ------ -- ------------ ---------- ----------- ----------- -------- -------- -------- -------- --------
    125        7 11931  TM T2           UNKWON     Row-X (SX)  None        68720    0        0            3018 INACTIVE
    125        7 11931  TX T2           UNKWON     Exclusive   None        196622   768      Y            3018 INACTIVE
    142       11 11975  TM T2           UPDATE     Row-X (SX)  None        68720    0        0            2926 ACTIVE
    142       11 11975  TX T2           UPDATE     None        Exclusive   196622   768      0               7 ACTIVE
    142       11 11975  TX T2           UPDATE     Exclusive   None        393236   764      0            2926 ACTIVE
 
SESSION1:
SQL> select * from t2 where empno in (7369,7900);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80       1000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

看到系统只回滚了对empno=7900的修改。
而此时SESSION2的update t2 set sal=sal-200 where empno=7369;依然被SESSION1的第一条语句所阻塞。

用下面的SQL语句可以直接得出谁阻塞谁
SELECT   
DECODE (l.request, 0, 'Holder: ', 'Waiter: ') status, l.SID,s.SERIAL#,
l.inst_id,l.ctime, l.id1, l.id2, l.lmode, l.request, l.TYPE
FROM gv$lock l,v$session s
WHERE l.sid=s.sid and (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0)

STATUS       SID  SERIAL#    INST_ID    CTIME        ID1        ID2      LMODE    REQUEST TY
-------- ------- -------- ---------- -------- ---------- ---------- ---------- ---------- --
Waiter:      142       11          1     6769     196622        768          0          6 TX
Holder:      125        7          1     9780     196622        768          6          0 TX

发生死锁的时候会在alert文件里记录下信息。
--查看操作系统下$ORACLE_BASE/admin/orcl/bdump/alert_orcl.log文件,会找到如下信息
Wed Nov 26 03:27:05 2014
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/infra/infra/trace/infra_ora_11931.trc.

再查看跟踪文件详细信息
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003000e-00000300        17     125     X             29     142           X
TX-00060014-000002fc        29     142     X             17     125           X
 
session 125: DID 0001-0011-0000000F     session 142: DID 0001-001D-00000002 
session 142: DID 0001-001D-00000002     session 125: DID 0001-0011-0000000F 
 
Rows waited on:
  Session 125: obj - rowid = 00010C70 - AAAQxwAAEAAAACrAAL
  (dictionary objn - 68720, file - 4, block - 171, slot - 11)
  Session 142: obj - rowid = 00010C70 - AAAQxwAAEAAAACrAAA
  (dictionary objn - 68720, file - 4, block - 171, slot - 0)
 
----- Information for the OTHER waiting sessions -----
Session 142:
  sid: 142 ser: 11 audsid: 360007 user: 44/SCOTT
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 29 O/S info: user: oracle, term: UNKNOWN, ospid: 11975
    image: oracle@rhel6.6 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 11973
    machine: rhel6.6 program: sqlplus@rhel6.6 (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
   update t2 set sal=sal-200 where empno=7369
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=ay7z0t1f5zkfk) -----
update t2 set sal=sal-200 where empno=7900

SESSION2还在被SESSION1阻塞,只有在SESSION1执行提交或回退才能让SESSION2继续下去。
SESSION1:
commit; --将提交update t2 set sal=sal+200 where empno=7369的修改

rollback;--将取消update t2 set sal=sal+200 where empno=7369的修改

随即SESSION2得到1 row updated.的信息。
我们也可以用管理员账号执行kill命令杀死导致阻塞的会话SESSION1来解决死锁。
杀死这个会话用下面的命令
alter system kill session '125,7';

session2:
立即显示
1 row updated.

session3:
SQL> @lock.sql
    SID    SERIAL# SPID   TY OBJECT_NAME  COMMAND    MODE_HELD   MODE_REQUES LOCK_ID1 LOCK_ID2 Blocking    CTIME STATUS
------- ---------- ------ -- ------------ ---------- ----------- ----------- -------- -------- -------- -------- --------
    142         11 11975  TM T2           UNKWON     Row-X (SX)  None        68720    0        0           10259 INACTIVE
    142         11 11975  TX              UNKWON     Exclusive   None        393236   764      0           10259 INACTIVE

session2:
SQL> commit;
Commit complete.

session3:
SQL> @lock.sql
无显示
   
总结:
    Oralce认为死锁是应用导致的错误,Oracle中很少出现死锁。导致死锁的的头号杀手是外键没有加索引,第二号杀手则是位图索引遭到并发更新。
    如果外键没有索引,我们更新了父表的主键时将会锁住整个字表。如果外键没有索引,删除了父表中的一行,整个子表也会被锁住。这样就很容易产生很多问题,
    如果其中任何阻塞的会话锁住了某一会话需要的资源就会出现一个死锁。至于位图索引的并发更新,是会锁住所有相关联的行,也会导致问题的发生。
    在Oracle9i及以上版本中,这些全表锁都是短期的,也就是仅在DML操作期间存在,而不是存在于整个事务期间。

根据保护的对象不同,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的错误。这些现象都会对实际应用产生极大的危害,如长时间未响应,大量事务失败等。

DML锁分类表


表1 Oracle的TM锁类型

锁模式

  锁描述

  解释

 SQL操作

0

  none

   

1

  NULL

  空

  Select

2

  SS(Row-S)

  行级共享锁,其他对象只能查询这些数据行

  Select for update、Lock for update、Lock row share

3

  SX(Row-X)

  行级排它锁,在提交前不允许做DML操作

  Insert、Update、Delete、Lock row share

4

  S(Share)

  共享锁

  Create index、Lock share

5

  SSX(S/Row-X)

  共享行级排它锁

  Lock share row exclusive

6

  X(Exclusive)

  排它锁

  Alter table、Drop able、Drop index、Truncate table 、Lock exclusive



1.关于V$lock表和相关视图的说明


 Column

Datatype

  Description

  ADDR

RAW(4 | 8)

  Address of lock state object

  KADDR

RAW(4 | 8)

  Address of lock

  SID

NUMBER

  Identifier for session holding or acquiring the lock

  TYPE

VARCHAR2(2)

  Type of user or system lock

  The locks on the user types are obtained by user applications. Any process that is    blocking others is likely to be holding one of these locks. The user type locks are:

  TM - DML enqueue 

  TX - Transaction enqueue

  UL - User supplied

  --我们主要关注TXTM两种类型的锁

  --UL锁用户自己定义的,一般很少会定义,基本不用关注

  --其它均为系统锁,会很快自动释放,不用关注

  ID1

NUMBER

  Lock identifier #1 (depends on type)

  ID2

NUMBER

  Lock identifier #2 (depends on type)

  ---当lock type 为TM时,id1为DML-locked object的object_id

  ---当lock type 为TX时,id1为usn+slot,而id2为seq。

  --当lock type为其它时,不用关注

  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)

  --大于0时表示当前会话以某种模式占有该锁,等于0时表示当前会话正在等待该锁资    源,即表示该会话被阻塞。

  --往往在发生TX锁时,伴随着TM锁,比如一个sid=9会话拥有一个TM锁,一般会拥有一个 或几个TX锁,但他们的id1和id2是不同的,请注意

 REQUEST

NUMBER

Lock mode in which the process requests 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)


--大于0时,表示当前会话被阻塞,其它会话占有改锁的模式

CTIME

NUMBER

Time since current mode was granted

BLOCK

NUMBER

The lock is blocking another lock

0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */

--该锁是否阻塞了另外一个锁

2.其它相关视图说明

视图名

描述

主要字段说明

v$session

查询会话的信息和锁的信息。

sid,serial#:表示会话信息。

program:表示会话的应用程序信息。

row_wait_obj#:表示等待的对象,和dba_objects中的object_id相对应。

lockwait :该会话等待的锁的地址,与v$lock的kaddr对应.

v$session_wait

查询等待的会话信息。

sid:表示持有锁的会话信息。

Seconds_in_wait:表示等待持续的时间信息

Event:表示会话等待的事件,锁等于enqueue

     

dba_locks

对v$lock的格式化视图。

Session_id:和v$lock中的Sid对应。

Lock_type:和v$lock中的type对应。

Lock_ID1: 和v$lock中的ID1对应。

Mode_held,mode_requested:和v$lock中

的lmode,request相对应。

v$locked_object

只包含DML的锁信息,包括回滚段和会话信息。

Xidusn,xidslot,xidsqn:表示回滚段信息。和

v$transaction相关联。

Object_id:表示被锁对象标识。

Session_id:表示持有锁的会话信息。

Locked_mode:表示会话等待的锁模式的信

息,和v$lock中的lmode一致。



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

请登录后发表评论 登录
全部评论

注册时间:2014-02-26

  • 博文量
    48
  • 访问量
    144574