oracle bug 6825287导致DX锁等待

最新推荐文章于 2021-04-04 14:54:30 发布

cssmya086682

最新推荐文章于 2021-04-04 14:54:30 发布

阅读量384

点赞数

文章标签：数据库

1 概述

问题简述：scdb数据库锁阻塞

2 问题描述

scdb数据库某日上午发生DX锁阻塞现象，影响部分业务操作。当时监控到的锁阻塞情况如下：

HOLD_SID H_USER H_LOCKWAIT H_STATUS H_MODULE H_OBJ H_ROW WAIT_SID W_USER W_LOCKWAIT W_STATUS W_MODULE W_OBJ W_ROW H_TYPE H_ID1 H_ID2 H_LMODE H_REQUEST H_CTIME H_BLOCK R_TYPE R_ID1 R_ID2 R_LMODE R_REQUEST R_CTIME R_BLOCK KILLHOLD HSQL WSQL

1 2321 DB_SC ACTIVE 81912 0 91 DB_YW 0700000866676D18 ACTIVE -1 0 DX 38 0 6 0 1975 1 DX 38 0 0 4 1210 0 alter system kill session '2321,64240'; -- kill -9, 7131170 INSERT INTO DB_YW.T_TEMP_SC..... SELECT formatid, globalid, branchid FROM SYS.DBA_PENDING_TRANSACTIONS

3 问题分析

从监控结果看，阻塞锁类型是DX锁（Distributed transaction entry），等待锁的SQL语句是SELECT formatid, globalid, branchid FROM SYS.DBA_PENDING_TRANSACTIONS ，持有锁的SQL是一条INSERT语句。锁持有方式是6 - exclusive (X) ，锁申请方式4 - share (S) 。

DBA_PENDING_TRANSACTIONS是系统数据字典，DBA_PENDING_TRANSACTIONS describes unresolved transactions (either due to failure or if the coordinator has not sent a commit/rollback). DBA_PENDING_TRANSACTIONS is a view which is basically a table @imposed on the structure representing in-memory state of distributed txns.

The DX-enqueue serves as a serialization primitive to prevent concurrency among various tightly-coupled branches of a distributed transaction on any database. It also provides for serialization of 2PC operations like abort and forward going changes.

在一个高负载、高并发、并使用了分布式事务的数据库环境中,可能会产生DX锁等待（may present a problem in a high activity system where the application logic performs work in short bursts i.e. work is performed in several client -> server roundtrips. Each trip into the database server, would request the DX-enqueue if the session has a distributed txn attached to it. Additionally, 2PC operations on detached branches also require the DX-enqueue.）

具体描述请查阅Oracle metalink 文档[ID 762412.1]

这是Oracle一个Unpublished Bug（querying DBA_PENDING_TRANSACTIONS in a high concurrency environment and to resolve the potential issue of acquisition of the DX-enqueue with infinite timeout. The fix for this issue is to request the enqueue/lock with a timeout and handle the enqueue/lock acquisition timeouts appropriately. Thus relieving the contention issue.）

4 结论

从锁阻塞现场情况看，很可能是遇到ORACLE Bug 6825287，这个bug在9i 10g 11.1版本都存在，需要安装补丁patch 8354686才可能解决，但这个补丁是Oracle停止oracle9i标准服务之后才发布，所以在Oracle9i版本下属于“扩展支持”范围，需要与Oracle签订Oracle9i“扩展支持”服务合同才能获取该补丁。

5 附Oracle metalink DOC [ID 762412.1]

Select From Dba_pending_transaction or GV$GLOBAL_TRANSACTION Takes A Long Time (very slow) [ID 762412.1]

修改时间 08-FEB-2010 类型 PROBLEM 状态 PUBLISHED

In this Document
Symptoms
Cause
Solution
References

5.1 Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1.0.6 - Release: 9.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.8 to 11.1.0.6

5.2 Symptoms

SELECT FROM DBA_PENDING_TRANSACTION TAKES A LONG TIME,

The investigation shows the session waiting for 'DX' lock .

This issue may also be observed with XA transactions running :
MSDTC / OraMTS / ODBC
- Microsoft Distributed Transaction Coordinator service
- Oracle Services for Microsoft Transaction Server (MTS)
In this case, the issue will fire in the Client Applications which are periodically encountering :
ORA-02049: timeout: distributed transaction waiting for lock

Contention is observed :
gv$session_wait shows session waiting "enq: DX - contention"

The Key points which relate to the DX enqueue under the circumstances here are as follows.

- The DX-enqueue serves as a serialization primitive to prevent concurrency among various tightly-coupled branches of a distributed transaction on any database. It also provides for serialization of 2PC operations like abort and forward going changes (e.g. out-of-band abort requests sent in by Transaction
Monitors like MSDTC on expiration of application transaction time-to-live).

- OraMTS runs a periodic recovery job as a scheduled job via the jobq infrastructure. This purpose of this job is to query
DBA_PENDING_TRANSACTIONS looking for any MSDTC-initiated and coordinated transactions that go in-doubt on the database.

- DBA_PENDING_TRANSACTIONS is a view which is a union of a fixed table and a persistent table.

which is basically a table @imposed on the structure representing in-memory state of distributed txns.
Iterating over this table requires acquisition of the DX-enqueue for each entry in the table. In 9.2 and 10gR1 and 10gR2,this lock is acquired with an infinite wait time in S-mode.

- The above may present a problem in a high activity system where the application logic performs work in short bursts i.e. work is performed in several client -> server roundtrips. Each trip into the database server, would request the DX-enqueue if the session has a distributed txn attached to it.

Additionally, 2PC operations on detached branches also require the DX-enqueue.

The above contributes to DX contention and the ORA-2049 errors reported.

5.3 Cause

The symptoms seem to match Bug 6825287, this bug was closed a duplicate of unpublished bug 5843814, following is the problem description from the developers:

Problem description:
XA_RECOVER is blocked by a Fore Ground process holding DX in 'X' mode

REDISCOVERY INFORMATION:
XA_RECOVER is very slow or query on GV$GLOBAL_TRANSACTION is very slow.

Unpublished Bug 5843814 was filed to address performance issues that were encountered when
querying DBA_PENDING_TRANSACTIONS in a high concurrency environment and to resolve the potential issue of acquisition of the DX-enqueue with infinite timeout.
The fix for this issue is to request the enqueue/lock with a timeout and handle the enqueue/lock acquisition timeouts appropriately. Thus relieving the contention issue.

5.4 Solution

Bug 6825287 is reported on version 9.2 . The base unpublished bug 5843814 was reported and reproduced on both 10.2 and 11.1 and it leads us to believe that the same problem can occur on 9i, 10g or 11g.

To fix the problem, please follow the steps outlined below:

The Original patch created for this issue was patch 5843814 , but then the developers has created new Patch 8354686 to solve some regression and conflict issues reported from the original patch, also the new patch contains also the fix of bug 8354686. the plan to solve this issue then is :

1. Connect to metalink and search for the patch 8354686 and make sure to select the correct platform and RDBMS version.

2. If the patch is not available for your system, please open new service request, and ask the assigned analyst to file a new one off request for your platform.

3. please make sure that you are already on the latest patchset before requesting the patch.

4. Apply the patch on testing environment and after verifying the fix ,apply it to the production system.

In addition as per Note 738108.1, and the document: Oracle Whitepaper ??????Oracle SOA Suite XA and RAC Database Configuration Guide?????? [XA], on OTN: http://www.oracle.com/technology/tech/soa/ha/index.html

There also the same recommendation to apply Patch number : 5843814 which has now been replaced by patch 8354686 so I thoght it will be good to mention this as well. .