Newsgroups: comp.databases.oracle.server Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!logbridge.uoregon.edu!news.indiana.edu!news.er.usgs.gov!news From: Brian Peasland Subject: Re: Hit deadlock with 2 threads updating DIFFERENT tables! Why? In-Reply-To: <1147371312.776648.139490@j33g2000cwa.googlegroups.com> X-Nntp-Posting-Host: edc-cv-160-69.cr.usgs.gov Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-ID: User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739) Content-Transfer-Encoding: 7bit Organization: U.S. Geological Survey, Reston VA References: <1147371312.776648.139490@j33g2000cwa.googlegroups.com> Mime-Version: 1.0 Date: Thu, 11 May 2006 19:03:23 GMT Lines: 121 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:267426 > *** 2006-05-11 00:08:03.601 > *** SESSION ID:(9.38442) 2006-05-11 00:08:03.601 > DEADLOCK DETECTED > Current SQL statement for this session: > select hmanager0_.id as id1_0_, hmanager0_.version as version1_0_, > hmanager0_.name as name1_0_, hmanager0_.status as status1_0_, > hmanager0_.lastHeartBeat as lastHear5_1_0_, > hmanager0_.shutdownManagerOptions as shutdown6_1_0_, > hmanager0_.shutdownServiceOptions as shutdown7_1_0_, > hmanager0_.scheduledShutdownDate as schedule8_1_0_, hmanager0_.address > as address1_0_, hmanager0_.creationDate as creatio10_1_0_, > hmanager0_.lastRegisteredDate as lastReg11_1_0_, hmanager0_.sessionId > as sessionId1_0_, hmanager0_.clusterid as clusterid1_0_ from MANAGERS > hmanager0_ where hmanager0_.id=:1 for update > 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-00460008-0000017e 105 9 X 98 56 > S > TX-000f0006-000004af 98 56 X 70 125 > S > TX-00460008-0000017e 70 125 X 105 9 > X > session 9: DID 0001-0069-00002DE2 session 56: DID > 0001-0062-0000386A > session 56: DID 0001-0062-0000386A session 125: DID > 0001-0046-00004F1B > session 125: DID 0001-0046-00004F1B session 9: DID > 0001-0069-00002DE2 Session 9 (V$SESSION.SID) is the session that detected the deadlock condition. You can see it's current SQL statement above. This session is attempting to modify a row in OBJ# 35197. Querying DBA_OBJECTS will tell you which object this is, but it is probably the MANAGERS table. Session 9 waiting on Session 56 Session 56 waiting on Session 125 Session 125 waiting on Session 9 The circle is complete and a deadlock has been detected. Note that there are two types of locks at play here. Exclusive locks ('X') and Shared locks ('S'). Session 9 is looking for an exclusive lock but it cannot obtain the lock because sessions 56 and 125 have a shared lock on that common resource. > Rows waited on: > Session 56: no row > Session 125: no row > Session 9: obj - rowid = 0000897D - AAAIl9AANAAAF+SAAO > (dictionary objn - 35197, file - 13, block - 24466, slot - 14) Normally, you'd see all three sessions waiting on the same row. But this can be explained by the fact that sessions 56 and 125 only have a shared lock on this table, not an exclusive lock. > Information on the OTHER waiting sessions: > Session 56: > pid=98 serial=30689 audsid=4140750 user: 71/SDS11 > O/S info: user: fff, term: unknown, ospid: , machine: blahblah.com > program: JDBC Thin Client > application name: JDBC Thin Client, hash value=0 > Current SQL Statement: > > update TASK set version=:1, parent=:2, status=:3, taskType=:4, > orderNo=:5, retryCount=:6, processCode=:7, message=:8, creationDate=:9, > lastModifiedDate=:10, data=:11, description=:12, externalId=:13, > serviceSessionId=:14, serviceStatus=:15, servicePort=:16, > serviceAddress=:17, instanceId=:18, PROCESSID=:19, MANAGERID=:20 where > id=:21 and version=:22 > Session 125: > pid=70 serial=50100 audsid=4140786 user: 71/SDS11 > O/S info: user: fff, term: unknown, ospid: , machine: blahblah.com > program: JDBC Thin Client > application name: JDBC Thin Client, hash value=0 > Current SQL Statement: > > update TASK set version=:1, parent=:2, status=:3, taskType=:4, > orderNo=:5, retryCount=:6, processCode=:7, message=:8, creationDate=:9, > lastModifiedDate=:10, data=:11, description=:12, externalId=:13, > serviceSessionId=:14, serviceStatus=:15, servicePort=:16, > serviceAddress=:17, instanceId=:18, PROCESSID=:19, MANAGERID=:20 where > id=:21 and version=:22 > End of information on OTHER waiting sessions. > =================================================== > PROCESS STATE > ------------- The above only shows you the *current* SQL statement. It is likely that sessions 56 and 125 have issued other SQL statement prior to their current ones and those sessions have not yet committed their transaction. This can make it confusing as you cannot see how a session waiting on the MANAGER table can be waiting on two sessions that are currently working with the TASK table. But the other sessions have a lock on the TASK table and have not yet committed. And they will not commit due to the deadlock condition. HTH, Brian -- =================================================================== Brian Peasland oracle_dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown