测试库死锁诊断DEADLOCK DETECTED ( ORA-00060 )

最新推荐文章于 2023-05-04 14:10:27 发布

cilonggang7850

最新推荐文章于 2023-05-04 14:10:27 发布

阅读量637

点赞数

2012年5月份的一天，开发同事找到我，说测试库碰到了死锁问题，要求帮忙看看。

1.死锁问题及建议

死锁：DEADLOCK DETECTED ( ORA-00060 )

现象：

Thu May 17 10:35:35 2012

ORA-00060: Deadlock detected. More info in file /oradata/diag/rdbms/orcl/orcl/trace/orcl_ora_893044.trc.

trace文件信息摘录：

*** 2012-05-17 10:35:34.727

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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

TM-0001226e-00000000 118 371 SX SSX 117 310 SX SSX

TM-0001226e-00000000 117 310 SX SSX 118 371 SX SSX

session 371: DID 0001-0076-00000002 session 310: DID 0001-0075-00000008

session 310: DID 0001-0075-00000008 session 371: DID 0001-0076-00000002

Rows waited on:

Session 371: no row

Session 310: no row

----- Information for the OTHER waiting sessions -----

Session 310:

sid: 310 ser: 88 audsid: 2818451 user: 102/DATAMGR flags: 0x100045

pid: 117 O/S info: user: oracle, term: UNKNOWN, ospid: 1212508

image: oracle@localhost

client details:

O/S info: user: bisp, term: unknown, ospid: 1234

machine: dmsvr.bisp program: JDBC Thin Client

application name: JDBC Thin Client, hash value=2546894660

current SQL:

DELETE FROM FORECAST_KEYPOINT WHERE FORECAST_ID = :1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=0jdjc3kdu7dnz) -----

DELETE FROM FORECAST_KEYPOINT WHERE FORECAST_ID = :1

****************************************************************************

Session371和session310发起的两个事务分别包含上述标红的sql语句，相互等待对方资源的释放，造成死锁；

首先注意到的是Deadlock graph中的资源占有情况，可以看到两个session都hold了一个SX类型的锁，同时在等待SSX类型的锁，而且引发的是一个删除语句，并且这个表是系统的一个关键表，大部分的表的外键都引用自此表的主键。因此猜测是碰到了外键引发的死锁。

建议1：

应用程序开发人员检查表FORECAST_AIRPORT和表FORECAST_KEYPOINT之间的关系（如父表FORECAST_KEYPOINT、子表FORECAST_AIRPORT 具体参照哪一列），有哪些约束、索引等；

如果子表上的外键约束列没有建立索引，删除父表记录时不得不对子表加表级锁，防止其他删除操作对该表的操作，要解决这种死锁问题则需要在子表的外键约束裂伤建立相应的索引；

这样，当对子表的外键列添加索引后，死锁可以被消除，因为这时删除父表记录不需要对子表加表级锁。 （后面有模拟实验可以验证）

创建索引：create index index_name on schema.table_name(column_name);

create index index_name on pdmcompard.FORECAST_AIRPORT (FORECAST_ID);

create index index_name on datamgr.FORECAST_AIRPORT (FORECAST_ID);

建议2：

也有可能是循环删除的问题（如果第1个建议没有成功，可以试试此方法）
如果可以，改成delete from t_user where ID in (:1,:2...)

《调整业务逻辑，修改应用程序，合理分配资源》

2.附录及实验

表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、创建一个表，此表作为子表
SQL> create table fk_table as select * from user_objects;

Table created
2、创建一个表，此表作为父表
SQL> create table pk_table as select * from user_objects;

Table created
3、创建父表的主键
SQL> alter table PK_TABLE add constraint pk_pktable primary key (OBJECT_ID);

Table altered
4、创建子表的外键
SQL> alter table FK_TABLE add constraint fk_fktable foreign key (OBJECT_ID) references pk_table (OBJECT_ID);

Table altered
5、如下sql取自TOAD工具，用来显示数据库锁的信息
SELECT LK.SID,
SE.USERNAME,
SE.OSUSER,
SE.MACHINE,
DECODE(LK.TYPE,
'TX',
'Transaction',
'TM',
'DML',
'UL',
'PL/SQL User Lock',
LK.TYPE) LOCK_TYPE,
DECODE(LK.LMODE,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.LMODE)) MODE_HELD,
DECODE(LK.REQUEST,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.REQUEST)) MODE_REQUESTED,
TO_CHAR(LK.ID1) LOCK_ID1,
TO_CHAR(LK.ID2) LOCK_ID2,
OB.OWNER,
OB.OBJECT_TYPE,
OB.OBJECT_NAME,
LK.BLOCK,
SE.LOCKWAIT
FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
WHERE LK.TYPE IN ('TM', 'UL')
AND LK.SID = SE.SID
AND LK.ID1 = OB.OBJECT_ID(+);

6、执行一个删除操作，这时候在子表和父表上都加了一个Row-S(SX)锁
delete from fk_table where object_id=94716;
delete from pk_table where object_id=94716;
7、执行另一个删除操作，发现这时候第二个删除语句等待
delete from fk_table where object_id=94702;
delete from pk_table where object_id=94702;
执行查询语句，得到锁信息如下：
857 DML Row-S (SS) None 107220 0 BILL TABLE PK_TABLE 0 00000000D555A0E8
872 DML Row-X (SX) None 107220 0 BILL TABLE PK_TABLE 0
857 DML Row-X (SX) S/Row-X (SSX) 107219 0 BILL TABLE FK_TABLE 0 00000000D555A0E8
872 DML Row-X (SX) None 107219 0 BILL TABLE FK_TABLE 1
可以看到session 857在请求一个SSX类型的锁，此时如果执行如下操作：
delete from pk_table where object_id=94716;
死锁马上发生，因为857 SESSION拿到了一个对FK_TABLE的行独占锁，并在请求一个表共享锁，而872 SESSION也拿到了一个FK_TABLE上的行独占锁，并请求一个表共享锁。此时两个session谁都不会释放独占锁，并同时请求表的共享锁，死锁由此引发。因为死锁引发的时候两个session不是在等待对数据行进行加锁，所以可以从trace文件中发现等待的行都为no row，同时可以看到两个session都hold了一个SX锁，并且都在等待SSX锁资源。同时trace文件中还记录了引发死锁的sql。

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-0001a2d3-00000000 16 872 SX SSX 20 857 SX SSX
TM-0001a2d3-00000000 20 857 SX SSX 16 872 SX SSX
session 872: DID 0001-0010-000F5EA0 session 857: DID 0001-0014-001D7407
session 857: DID 0001-0014-001D7407 session 872: DID 0001-0010-000F5EA0
Rows waited on:
Session 857: no row
Session 872: no row

Current SQL Statement:
delete from pk_table where object_id=94716
8、当对子表的外键列添加索引后，死锁被消除，因为这时删除父表记录不需要对子表加表级锁，这里不再做测试。

结论：曾经有人讨论过是否所有的数据库设计都应该遵守范式的规范，都把主外键关系建立起来。也有人反对这样做，因为这样复杂的关系在OLTP系统中可能会成为灾难，而提倡通过程序来保证数据的完整性，但程序发生bug导致数据不一致的情况时有发生。而且如果外键设置为级联删除，则不加索引的外键会使得对子表的记录删除走全表扫描。因此，对外键的使用还是要慎重！