java.sql.SQLException: Deadlock found when trying to get lock; try
restarting transaction
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at
com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1371)
The objects may be tables, rows, items depending on the granularity.
Thead/User 1 Thread/User 2
read/lock object 1
read/lock object 2
wants to get object 2 --> has to wait until released by Thread 2
wants to get object 1 --> has to wait until released Thread 1
Regards
Helmut Leininger
So it is not possible to write a deadlock free database application using
Java technologies?
If you only lock a single resource per transaction, you wont deadlock.
If you always lock multiple resources in the same order in a
transaction, you wont deadlock.
If all your doing is a single insert statement in the transaction, as
you indicate, you wont deadlock.
You have the same issues in java with synchronized objects. At least
the database deadlocks timeout.
In general, it is not possible to write a deadlock free database
application using _any_ technology that permits more than one user to
update data. The avoidance of deadlocks is isomorphic to the halting
problem.
In practice, high-quality database servers can attempt to detect
deadlock, and careful locking schemes can help limit resource
contention. But the problem is persistent. There's an informative
article here:
http://en.wikipedia.org/wiki/Deadlock
--
John
jmatthews at wright dot edu
www dot wright dot edu/~john.matthews/
Its impossible to build a database that can't deadlock, but not
impossible to build a database application which avoids deadlock. As
you're link points out...
http://en.wikipedia.org/wiki/Deadlock#Deadlock_prevention .
> John B. Matthews wrote:
> > In article <ELOdnQcnb4A...@comcast.com>,
> > "Dilton McGowan II" <dil...@gmail.com> wrote:
> > [...]
> >
> >>So it is not possible to write a deadlock free database application
> >>using Java technologies?
> >
> >
> > In general, it is not possible to write a deadlock free database
> > application using _any_ technology that permits more than one user to
> > update data. The avoidance of deadlocks is isomorphic to the halting
> > problem.
> >
> > In practice, high-quality database servers can attempt to detect
> > deadlock, and careful locking schemes can help limit resource
> > contention. But the problem is persistent. There's an informative
> > article here:
> >
> > http://en.wikipedia.org/wiki/Deadlock
> >
> Resource contention and deadlock are not the same thing.
True, but mutual exclusion is a necessary condition for deadlock. I
meant to suggest that the OP might look at judicious locking as a
possible solution.
> Contention means you have to wait for a lock, deadlock means you'll
> never get it because of mutual dependencies.
And you don't know if you'll be waiting a long time or forever:-)
> Its impossible to build a database that can't deadlock, but not
> impossible to build a database application which avoids deadlock.
Indeed, the application is the best place to avoid deadlock because it
knows the most about what resources will be put in play.
> As you're link points out...
> http://en.wikipedia.org/wiki/Deadlock#Deadlock_prevention.
After avoidance and prevention, the article goes on to deadlock
detection, offered by many database vendors as an artificial preemption
mechanism. An attempt may be made to degrade gracefully.
In one puzzling scenario, a remote replication operation that worked
fine at night was failing at random times when run (ad hoc) during the
day. The database declared a deadlock and periodically retried the
transaction. It turns out the network vendor was dropping packets during
busy times, and the retries just made the problem worse until nightfall!
1. you need to open multiple connections. ( or use transaction isolation)
2. you need to code for "select for update" which will lock the require
resources, DO NOT lock the table, unless it is absolutly nec, lock the row or
column.
3. ensure in your "update", that it does not wait forever.
4. turn autocommit OFF.