Oracle

joe_echavarria
joe_echavarria used Ask the Experts™
on
Hi,

  I am having always a DeadLock error generated in my database always because of this statment : DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0, all the trace files generated contains the same SQL Statement for the current session, before the PROCESS STATE line.

 -  Do i have to increase the INITRANS for this table ?.
 -  Increasing the size of INITRANS does affect current data and the future ones ?

 Here the first part of the trace file.

Windows thread id: 7016, image: ORACLE.EXE (SHAD)


*** 2011-08-04 08:52:54.549
*** SERVICE NAME:(mydatabase.company.CORP) 2011-08-04 08:52:54.533
*** SESSION ID:(3.40552) 2011-08-04 08:52:54.533
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-0000eb68-00000000        96       3    SX   SSX       86      46    SX   SSX
TM-0000eb68-00000000        86      46    SX   SSX       96       3    SX   SSX
session 3: DID 0001-0060-000009AE      session 46: DID 0001-0056-00002129
session 46: DID 0001-0056-00002129      session 3: DID 0001-0060-000009AE
Rows waited on:
Session 46: obj - rowid = 0000F029 - AAAPApAAGAAMXGjAAA
  (dictionary objn - 61481, file - 6, block - 3240355, slot - 0)
Session 3: obj - rowid = 0000F62F - AAAPYvAAGAAVWKFAAA
  (dictionary objn - 63023, file - 6, block - 5595781, slot - 0)
Information on the OTHER waiting sessions:
Session 46:
  pid=86 serial=21228 audsid=1209103117 user: 55/SMARTSOLVE
  O/S info: user: SYSTEM, term: USLZAPP006, ospid: 7064:8496, machine: FENWAL\USLZAPP006
            program: SmartCommService.exe
  application name: SmartCommService.exe, hash value=0
  Current SQL Statement:
  DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0
===================================================
PROCESS STATE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
Commented:
I believe this has been pointed out to you before.  Your answer is in the trace file:

It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL.


Changing parameters is not going to help anything here.

A basic illustration of what is happening here is:

user1 - update apl_app_level_doc set col=sysdate where apl_id=2
user2 - update apl_app_level_doc set col=sysdate where apl_id=3
user1 - delete apl_app_level_doc where apl_id=3
user2 - delete apl_app_level_doc where apl_id=2

While this is most likely not the exact situation with your application, this is a simple illustration of how this can happen.
joe_echavarriaDatabase Administrator

Author

Commented:
Ok, is just that from the application side hasn't been any changing and this is a new error in the database.

I have been searching and the parameters INITRANS set low can cause deadlocks.  I just wanted to make sure consulting with the experts.
johnsoneSenior Oracle DBA

Commented:
Have you read through this link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6042872196732

There is some good information in there on how to spot an ITL deadlock.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

When INITRANS is set low you might get ORA-00060 "deadlock detected".
A max suggested value for INITRANS would be 100.

For ITL deadlocks - higher INITTRANS/PCTFREE values would allow for more space regarding the  data blocks so that more transaction entries can be allocated when necessary.


joe_echavarriaDatabase Administrator

Author

Commented:
Hi
   After executing the script below, does it means that i have to increase the value of the parameter initrans of these indexes ?

SQL> select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME,         OBJECT_TYPE,
TATISTIC_NAME, VALUE   from v$segment_statistics    where statistic_name = 'ITL
waits'   and value > 0   order by value desc;

OBJECT_NAME     SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_   VALUE
--------------- ---------- ---------- ---------- ---------- -------
IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits      206
FIELDS_2                   _INDEX

IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits      126
FIELDS_1                   _INDEX

IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits       76
1                          _INDEX

IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits       27
3                          _INDEX

OBJECT_NAME     SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_   VALUE
--------------- ---------- ---------- ---------- ---------- -------

IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits        6
4                          _INDEX

IX_AUDIT_TRAIL_            SMARTSOLVE INDEX      ITL waits        4
2                          _INDEX

IX_ACT_REQ_PROG            SMARTSOLVE INDEX      ITL waits        2
RESS_3                     _INDEX

IX_OBTAINED_HIS            SMARTSOLVE INDEX      ITL waits        1

OBJECT_NAME     SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_   VALUE
--------------- ---------- ---------- ---------- ---------- -------
TORY_1                     _INDEX

IX_DOC_LIFE_CYC            SMARTSOLVE INDEX      ITL waits        1
LE                         _INDEX

IX_DOC_REFERENC            SMARTSOLVE INDEX      ITL waits        1
E_1                        _INDEX

IX_ACT_REQ_PROG            SMARTSOLVE INDEX      ITL waits        1
RESS_2                     _INDEX


11 rows selected.
joe_echavarriaDatabase Administrator

Author

Commented:
After executing the script below i guess i have 8 indexes that the INITRANS parameter must be increase ?  Please also see the output of the preview question posted.

Which side should increase this parameter ?

SQL> select event, total_waits, time_waited, average_wait from v$system_event  w
here event like 'enq: TX%'  order by 2 desc;

EVENT      TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ----------- ----------- ------------
enq: TX -         8445     1404640       166.33
row lock c
ontention

enq: TX -         1739        5371         3.09
index cont
ention

enq: TX -           11          90         8.19
contention


EVENT      TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ----------- ----------- ------------
enq: TX -            8          36          4.5
allocate I
TL entry
joe_echavarriaDatabase Administrator

Author

Commented:
I have attached a pic with the output of 2 different queries.  Acoording to this output does it means i have to increase the size of the INITRANS parameter of these 8 indexes? ,if so to which size u think ?

Regards,


initrans.jpg
Usually for indexes it should be set to 4. (for tables -to 2 )
joe_echavarriaDatabase Administrator

Author

Commented:
So according to the image uploaded for thoses indexes the INITRANS parameter should be increase to 4 and the tables where those indexes where created should be se to 2 ?

Are those the reasons of the the deadlock i am having in the database ?
"For ITL deadlocks - higher INITTRANS/PCTFREE values would allow for more space regarding the  data blocks so that more transaction entries can be allocated when necessary."

Additionally here is some usefull reading: http://avdeo.com/2008/06/16/interested-transaction-list-itl/
joe_echavarriaDatabase Administrator

Author

Commented:
@georgekl

So the tables and indexes that need the changes on those parameter will be the ones in the picture uploaded ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial