I am currently using Oracle 11.2.0.4.
We use hibernate as the ORM, and the order in which the rows in a table are updated is handled by Hibernate.
The reason being application opens a transaction and does bulk edit of multiple rows in one-go.
I would like to know how to avoid deadlocks which are appearing in update of the same table by different sessions.
While performing certain DB transactions I am observing the following prints in the trc files:
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-000f0011-00001043 55 426 X 73 546 X
TX-00080012-000009b1 73 546 X 55 426 X
session 426: DID 0001-0037-00000002 session 546: DID 0001-0049-00000002
session 546: DID 0001-0049-00000002 session 426: DID 0001-0037-00000002
Rows waited on:
Session 426: obj - rowid = 00006BF7 - AAAGv3AQAAAAEAjAAA
(dictionary objn - 27639, file - 1024, block - 16419, slot - 0)
Session 546: obj - rowid = 00006BF7 - AAAGv3AQAAAAEAmAAA
(dictionary objn - 27639, file - 1024, block - 16422, slot - 0)
Session 546:
sid: 546 ser: 801 audsid: 293845 user: 34/WCSDBA
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 73 O/S info: user: oracle, term: UNKNOWN, ospid: 8936
image: oracle@Primeprimary
client details:
O/S info: user: root, term: unknown, ospid: 1234
machine: Primeprimary program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
update BaseTable1 set Name=:1 , Description=:2 , PKey1=:3 , ConfEntry1=:4 , IfType=:5
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=auvwkrdx6czhb) -----
update BaseTable1 set Name=:1 , Description=:2 , PKey1=:3 , ConfEntry1=:4 , IfType=:5 , IfTypeSubBand=:6 , LradIfName=:7 , ChannelNumber=:8 , PowerLevel=:9 , OperStatus=:10 , AdminStatus=:11 , NumberOfClients=:12 , LradEthernetMac=:13 , IpAddress_addressType=:14 , IpAddress_address=:15 , ConvertedIpAddress=:16 , ConvertedDeviceIpAddress=:17 , AntennaAngle=:18 , AntennaElevAngle=:19 , AntennaPatternName=:20 , AntennaGain=:21 , PowerListStr=:22 , IsBackhaul=:23 , RadioOnlyStatus=:24 , Status=:25 , RadioRole=:26 , RawParent_ID=:27 , MacAddress=:28 , AuthEntityId=:29 , AuthEntityClass=:30 , lradIfPId=:31 , phyChannelAssignment=:32 , phyTxPowerControl=:33 , antennaType=:34 , antennaMode=:35 , antennaDiversity=:36 , cellSiteConfigId=:37 , previousChannelNumber=:38 , lastChannelUpdateTime=:39 , previousPowerLevel=:40 , lastPowerLevelUpdateTime=:41 , switchPort=:42 , wlanOverride=:43 , antennaOptions=:44 , apSniffEnable=:45 , sniffChannelNumber=:46 , sniffServerIpAddressT=:47 , sniffServerIpAddress=:48 , channelListStr=:49 , regDomainSupported=:50 , locationmodeEnable=:51 , lomFirstChannel=:52 , lomSecondChannel=:53 , lomThirdChannel=:54 , lomFourthChannel=:55 , bssId=:56 , countryString=:57 , trackingOptimizedModeCommon=:58 , cdpOverAirEnable=:59 , provisionState=:60 where ID=:61