Hit deadlock with 2 threads updating DIFFERENT tables! Why?

From: theRat <john_hilgedick_at_yahoo.com>
Date: 11 May 2006 11:15:12 -0700
Message-ID: <1147371312.776648.139490@j33g2000cwa.googlegroups.com>

Hey all,

I have a deadlock situation that I can't figure out. I have one thread that creates a connection to the DB, starts a transaction, and then does a "select for update" on a table called "managers". I have a second thread that creates a connection to the DB, starts a transaction, and then does an "update task blah blah blah" - it is updating a row in a second, DIFFERENT table. Yet, I get deadlock. Why?

Here's the trace that oracle produced for me. I have to admit, I'm not good at interpreting these files. But it seems to me that neither of the SQL statements below could conflict with each other. Also, it seems to me that it is practically and theoretically IMPOSSIBLE to obtain deadlock when one of the threads is using a BRAND new connection and brand new transaction and hasn't locked anything yet.

Anyone have any thoughts?

-john

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /app/oracle/product/9.2.0
System name: Linux

Node name:      olympus
Release:        2.4.21-20.ELsmp
Version:        #1 SMP Wed Aug 18 20:46:40 EDT 2004
Machine:        i686

Instance name: SDS
Redo thread mounted by this instance: 1
Oracle process number: 105
Unix process pid: 20337, image: oracle_at_olympus (TNS V1-V3)

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 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) 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:

PROCESS STATE

Received on Thu May 11 2006 - 13:15:12 CDT