Shulou(Shulou.com)06/02 Report--

[20120412] Autonomous transaction (AUTONOMOUS_TRANSACTION) and deadlock problem. Txt

The production system rac encountered a problem in alert*.log:

Sat Mar 17 11:43:31 2012

Global Enqueue Services Deadlock detected. More info in file

/ u01/app/oracle/admin/orcl/udump/orcl2_ora_18076.trc.

Sat Mar 17 11:43:31 2012

Trace dumping is performing id= [cdmp _ 20120317114331]

Sat Mar 17 11:44:29 2012

System State dumped to trace file / u01/app/oracle/admin/orcl/bdump/orcl2_diag_20766.trc

Before 10.2.0.3, you will dump a large file to the corresponding dump file of the process ora_diag_orcl*, look at the corresponding cdmp* directory, and find yourself

The capacity is limited and the problem has not been solved. I have to check the usage of / U01 frequently to avoid running out of disk space.

Google found a lot of links, suggesting that most of them were primary and foreign key problems. In addition, google this link:

Http://studycow.itpub.net/post/37461/502772

Reproduced as follows:

Five. Annoying Bug

I already knew before I came here that the disk where Oracle is installed / opt is often full, causing Oracle to stop responding. Since I have been busy dealing with the performance problem, I put this problem

Put it at the end. The previous consideration is that if the performance problem is solved, it may be solved automatically; indeed, there are times when some bug of Oracle are more likely to be

Trigger. Since adjusting the performance, the frequency of this problem has decreased a little, but it is still coming out; while I was thinking about it, I found that the utilization rate of / opt had reached 90% again. Check.

It is found that a new 4G core dump file has been generated in the bdump directory of the installation directory.

It is this core dump file that takes up a lot of disk space. Check the alertSID.log file and find the following:

Sun Jun 6 18:51:35 2010

Global Enqueue Services Deadlock detected. More info in file

/ opt/oracle/product/admin/orcl/udump/orcl1_ora_26649.trc.

Sun Jun 6 19:14:05 2010

System State dumped to trace file / opt/oracle/product/admin/orcl/bdump/orcl1_diag_4783.trc

....

The log shows that because Oracle detected a deadlock in ges, it did a system-level dump operation. This kind of system-level dump has a lot of content, resulting in a particularly large dump file.

I feel like I met bug. I found a similar phenomenon in metalink. The phenomenon described in bug:6145177,bug is similar to that of a hospital, but not exactly the same, but after patching, it is no longer

There is this problem. This problem has been solved in 10.2.0.4, which is currently used in hospitals. Oracle has provided a separate Patch to avoid major version upgrades.

A separate patch is applied, and the whole process is relatively smooth. After typing this patch, I used it until the next morning, and there was no such error again. It seems that patch has already worked.

Upgrade to 10.2.0.4 according to this recommendation.

I also checked my own alert files, and I found that a large number of occurrences started from 2011. It is estimated that the developer changed the program, and the other party was not sure which program caused the problem after asking for a long time.

It seems that good records and documentation are very important! It also appeared on July 5 last year, and I actually feel that developers can recall it.

$grep-B 1'^ Global Enqueue Services Deadlock detected' alert_orcl2.log | egrep '2011 | 2012' > / tmp/a1

Thu Jun 30 09:12:56 2011

Tue Jul 5 08:37:28 2011

Tue Jul 5 08:38:25 2011

Tue Jul 5 08:38:55 2011

Tue Jul 5 08:39:09 2011

Tue Jul 5 08:39:47 2011

Tue Jul 5 08:40:02 2011

Sat Jul 9 10:50:39 2011

Sat Jul 9 10:50:52 2011

Sun Jul 10 10:11:50 2011

Thu Jul 14 13:36:10 2011

Thu Jul 14 13:36:15 2011

Thu Jul 14 13:41:34 2011

Thu Jul 14 13:41:39 2011

Thu Jul 14 16:24:29 2011

Thu Jul 14 16:24:35 2011

Thu Jul 14 16:25:00 2011

Thu Jul 14 16:25:09 2011

Thu Jul 14 16:25:16 2011

Thu Jul 14 16:25:23 2011

Thu Jul 14 17:02:01 2011

Fri Jul 15 08:45:24 2011

Fri Jul 15 08:45:30 2011

Fri Jul 15 08:49:36 2011

Fri Jul 15 14:20:11 2011

.

Wed Apr 11 11:46:39 2012

Wed Apr 11 19:53:17 2012

Thu Apr 12 07:47:45 2012

Thu Apr 12 14:26:26 2012

Thu Apr 12 14:26:46 2012

From the above information, we can find that this problem did not occur before 2011-6-30, and I think there is a great possibility that there will be problems in programming.

It was upgraded to 10.2.0.4 on March 18, 2012, and I began to keep an eye on whether the previous situation had been resolved. Inspection found that diag files are no longer growing rapidly, but

Global Enqueue Services Deadlock detected still appears, but this time I can locate the problem.

Fri Apr 13 16:29:03 2012

Global Enqueue Services Deadlock detected. More info in file

/ u01/app/oracle/admin/orcl/udump/orcl2_ora_3135.trc.

Fri Apr 13 16:29:03 2012

Trace dumping is performing id= [cdmp _ 20120413162903]

Looking at the corresponding orcl2_ora_3135.trc dump file, it is found that there is a recorded sql statement combined with the XID in the record, and it is finally determined that it is the problem, which is caused by the developer using an autonomous transaction in a stored procedure.

I made a test example to reproduce the problem:

1. Test environment:

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

Create table t (id number,name varchar2 (10))

CREATE UNIQUE INDEX I_T_I ON SCOTT.T (ID)

CREATE PROCEDURE test1 (l_id number,l_name varchar2,flag VARCHAR2) AS

PRAGMA AUTONOMOUS_TRANSACTION

BEGIN

Commit

IF flag = 'INSERT' THEN

Insert into t values (lumped _ name)

END IF

IF flag = 'UPDATE' THEN

Update t set id=l_id,name=l_name where id=l_id

END IF

IF flag = 'DELETE' THEN

Delete from t where id=l_id

END IF

Commit

Dbms_output.put_line (flag)

END

/

Insert into t values (1)

Insert into t values (2)

Commit

two。 Test:

Open session 1 and execute as follows:

SQL > set SERVEROUT on

SQL > update t set name='aaa' where id=1

SQL > exec test1 (1)

BEGIN test1 (1) END

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "SCOTT.TEST1", line 9

ORA-06512: at line 1

The alert prompt is as follows:

Mon Apr 16 10:37:38 2012

ORA-00060: Deadlock detected. More info in file / u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_13751.trc.

-- and the recording under 11G is more detailed and the location is more convenient.

Deadlock graph:

-Blocker (s)-Waiter (s)-

Resource Name process session holds waits process session holds waits

TX-00090008-00001193 26 132 X 26 132 X

Session: DID 0001-001A-00000007 session: DID 0001-001A-00000007

Rows waited on:

Session 132: obj-rowid = 00015D00-AAAV0AAAEAAAAIMAAA

(dictionary objn-89344, file-4, block-524, slot-0)

-Information for the OTHER waiting sessions-

-End of information for the OTHER waiting sessions-

Information for THIS session:

-Current SQL Statement for this session (sql_id=2bbypw0dj45w2)-

UPDATE T SET ID=:B1, NAME=:B2 WHERE ID=:B1

-PL/SQL Stack-

-PL/SQL Call Stack-

Object line object

Handle number name

0x95fcdac0 9 procedure SCOTT.TEST1

0x95f2c440 1 anonymous block

=

-- my test environment is stand-alone. In rac environment, the alert prompt is as follows:

Thu Apr 12 15:56:49 2012

Global Enqueue Services Deadlock detected. More info in file

/ u01/app/oracle/admin/orcl/udump/orcl1_ora_8070.trc.

Thu Apr 12 15:56:49 2012

Trace dumping is performing id= [cdmp _ 20120412155649]

3. Do another test:

SQL > rollback

Rollback complete.

SQL > select * from t

ID NAME

--

1 a

2 b

SQL > insert into t values (3)

1 row created.

SQL > exec test1 (1)

UPDATE

PL/SQL procedure successfully completed.

SQL > select * from t

ID NAME

--

1 A

2 b

3 c

-- Open another session and execute:

SQL > select * from t

ID NAME

--

1 A

2 b

You can find the characteristics of autonomous transactions. The records of id=1 have been modified, but the inserted records have not been committed.

If session 1 is rollback at this time, the modification to id=1 is still valid!

SQL > rollback

Rollback complete.

SQL > select * from t

ID NAME

--

1 A

2 b

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.