oracle--lock分析

最新推荐文章于 2024-10-08 17:39:57 发布

cuicang5610

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量128

点赞数

文章标签：数据库操作系统

如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会发生死锁。
用下面实验来说明死锁的产生原因和解决办法。
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 --我们主要关注TX和TM两种类型的锁 --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一致。