INITRANS DB_BLOCK_SIZE 表级别参数 index要在有数据情况下重建生效

ORA-00060: deadlock detected while waiting for resource

In most cases, the deadlock is caused by a problem in the application. It

can be traced back to a programming error.

Under certain conditions, a deadlock may also be triggered by Oracle.

This deadlock has the following cause:

If a data record is locked in the database, this lock information is

written to the block. The space provided in the block header is used for

this(ITL). The space is defined when the table is created with the parameters

INITRANS and maxtrans. If a block becomes too full, the database cannot

allocate any more space to store this block and must wait for memory space

in the corresponding block. As a result, row level locking can become block

level locking.

If some parallel scripts now lock a large number of data records that are

in the same block, two or more of the scripts may sometimes cause a

deadlock, not because they lock the same data record, but because no

additional memory space can be allocated for the lock.

To find out whether this is a deadlock in Oracle, you need to examine the

trace file written by Oracle in a lot more detail. The file is usually

stored in the /oracle//saptrace/usertrace directory. In addition, the

exact file name/directory can usually be determined from the ORA-00060

error message in /oracle//saptace/background/alert.log.

Open the file - the 'deadlock graph' appears on the first two pages.

The deadlock graph is as follows:

---------Blocker(s)-------- ---------Waiter(s)-----

Resource Name process sess. holds waits process sess. hold waits

TX-00090004-00011000 43 39 X 35 46 S

TX-0006001a-0001397d 35 46 X 43 39 S

Here, the last column that specifies the Waits as type 'S' is important.

If an 'X' is displayed instead in the graph, it is NOT an Oracle deadlock.

Which object is it?

After the deadlock graph, the system immediately displays further

information on the object for which the deadlock was generated:

Rows waited on:

Session 39: obj - rowid = 000016F2 - 0003BC42.0000.0093

Session 46: obj - rowid = 000018C2 - 0001012D.004B.0016

Although the select statement of the session terminated by the deadlock is

usually also displayed, you can also refer to these two lines to see which

table it is：

000016F2 [hex value] corresponds to 5874 [decimal]

000018C2 [hex value] corresponds to 6338 [decimal]

sqlplus "sapr3/"

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=5874;

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=6338;

Is it an index?

If no rows are displayed here, for example,

Rows waited on:

Session 39: no row

Session 46: no row

the deadlock most probably occurred while index blocks were being locked.

Solution

Measures to eliminate/minimize the problem:

The problem, in most cases, is that the system tries to dynamically extend

the block space, but the block is too full. INITRANS defines the static

value reserved for each block for transaction information. However, this

value can increase to MAXTRANS if several transactions simulaneously try to

perform a lock. To prevent this error from occurring, you have to extend

the INITRANS value as much as possible so that a dynamic extension is not

required. The default value for INITRANS is 1. This is usually sufficient

for 'standard' tables/indexes. However, it is necessary to adjust this

value for special tables/indexes, for example, spool tables (TST01, TST03)

and BW tables/indexes into which data is loaded sim