Home » SQL & PL/SQL » SQL & PL/SQL » deadlock (oracle 10)
deadlock [message #320129] Wed, 14 May 2008 02:55 Go to next message
timorhh
Messages: 2
Registered: May 2008
Junior Member
Dear Forum Participants,
i have following Dead-Lock Problem, which i not understand:

initial state:
table
account
-id PK
-txid FK of transaction

table
transaction
- id PK

1. Deadlock occure if more than one transaction run parallel, with 2 updates:
conn = getConnection;

upd1: "UPDATE Account SET "+ "TXID = ? WHERE id = ?";
(TXID ist one id of Transaction)

upd2: "UPDATE Transaction SET id = ?, tx_status = ?, amount = ?
WHERE id = ?";

conn.commit;

2. Deadlock DO NOT occure any more if i change order of updates
, first Transaction (upd2) second account (upd1)

3. Deadlock DO NOT occure any more if i deaktivate FK relation beetween Account and Transaction.

Regards
Timor
Re: deadlock [message #320132 is a reply to message #320129] Wed, 14 May 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index your FK.
Read AskTom "Indexes on foreign keys"

Regards
Michel
Re: deadlock [message #320143 is a reply to message #320129] Wed, 14 May 2008 03:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you actually getting a Deadlock, or is your second session simply hanging and waiting for the first session to commit it's changes?
Re: deadlock [message #320231 is a reply to message #320143] Wed, 14 May 2008 06:52 Go to previous message
timorhh
Messages: 2
Registered: May 2008
Junior Member
i am getting a deadlock:

ERR: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.

[Updated on: Wed, 14 May 2008 10:37]

Report message to a moderator

Previous Topic: getting error when calling procedure
Next Topic: Critical question
Goto Forum:
  


Current Time: Sat Dec 03 16:02:43 CST 2016

Total time taken to generate the page: 0.12290 seconds