I am trying to post this question multiple times, as the question is getting deleted automatically, not sure because its posted to other forums as well.
I have removed the question from other groups, to avoid duplication, which I had posted, and this is the only one active.
So I would request the administrator of the group to not delete this question without answering it.
I am trying to root-cause a deadlock issue.
I have few questions regarding the same:
- How to find which is the Resource involved in the Deadlock?
- I already checked using the SQL command - select object_name,owner,data_object_id from dba_objects where data_object_id=39341
- I get no rows when I run that.
- From Resource Name what can be deciphered?
The inserts are all ordered by value of the primary key, but still we are ending up with Deadlock. Could this be due to Lock on Unique Key constraint
If yes, how to find out whether this is a lock acquired on Table - DeviceDetails OR on the Unique Key constraint of the table DeviceDetails?
Under what scenarios would "S" lock mode be used? We are not using select for update anywhere, and anyway, this seems to be insert statements as per the graph below.
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
TX-0007001f-000131c5 38 486 X 47 731 S
TX-00020005-000131f6 47 731 X 38 486 S
session 486: DID 0001-0026-000000E2 session 731: DID 0001-002F-0000001B
session 731: DID 0001-002F-0000001B session 486: DID 0001-0026-000000E2
Rows waited on:
Session 486: obj - rowid = 000099AD - AAAJ4xAAKAAACgrAAA
(dictionary objn - 39341, file - 10, block - 10283, slot - 0)
Session 731: obj - rowid = 000099AD - AAAJ4xAAKAAACgrAAA
(dictionary objn - 39341, file - 10, block - 10283, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 731:
sid: 731 ser: 1603 audsid: 896907 user: 37/user1
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 47 O/S info: user: oracle, term: UNKNOWN, ospid: 22799
image: oracle@term7252
client details:
O/S info: user: root, term: unknown, ospid: 1234
machine: term7252 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
insert into DeviceDetails (INSTANCE_VERSION, term2, lastModifiedAt, term3DiscoveredFrom, hostUserName, profile, userPassword, description, lifeTime, startTime, endTime, campus, building, floorArea, outdoorArea, configGroup, emailID, disclaimerText, startDate, endDate, weekDays, ishostUser, applyUserTo, serviceDomainId, isScheduledUser, userStatus, isNewUserName, validity, isDefaultEditable, userRole, createdBy, createdAt, term6, rebootterm4, term1Years, term1Months, term1Days, term1Hours, term1Mins, term1Secs, isPasswordEncr, attributeList, term1StartTime, term1FirstLoginTime, term5EntityId, term5EntityClass, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 ,
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=dnj1vu47agct7) -----
insert into DeviceDetails (INSTANCE_VERSION, term2, lastModifiedAt, term3DiscoveredFrom, hostUserName, profile, userPassword, description, lifeTime, startTime, endTime, campus, building, floorArea, outdoorArea, configGroup, emailID, disclaimerText, startDate, endDate, weekDays, ishostUser, applyUserTo, serviceDomainId, isScheduledUser, userStatus, isNewUserName, validity, isDefaultEditable, userRole, createdBy, createdAt, term6, rebootterm4, term1Years, term1Months, term1Days, term1Hours, term1Mins, term1Secs, isPasswordEncr, attributeList, term1StartTime, term1FirstLoginTime, term5EntityId, term5EntityClass, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 )
===================================================