A JDBC Question --- Deadloack

[Jerry]

I am writing an application with multiple threads to insert data into
database. Each thread is responsible for inserting the data to the same
table. While I run the application, sometimes I got the following
deadlock exception. This exception does not happen very often, only
occasionally. To me, before each thread inserts data to the table, it
will aquire a lock on the table. There should be no deadlock. Anyone
knows why I got such a deadlock exception and how to solve this
problem? Thanks a lot!

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)

 

[Helmut Leininger]

I am writing an application with multiple threads to insert data into
database. Each thread is responsible for inserting the data to the same
table. While I run the application, sometimes I got the following
deadlock exception. This exception does not happen very often, only
occasionally. To me, before each thread inserts data to the table, it
will aquire a lock on the table. There should be no deadlock. Anyone
knows why I got such a deadlock exception and how to solve this
problem? Thanks a lot!

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)

Hi,
I don't know your spoecial reason for getting a deadlock. But
principally, you cannot totally avoid deadlocks in multi-user /
multi-thread DB applications. You my onmly reduce the probability by
designing short transactions (commit frames). A deadlock occurs in these
situations (all databses, all programming languages):

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

 

[Dilton McGowan II]

Hi,
I don't know your spoecial reason for getting a deadlock. But
principally, you cannot totally avoid deadlocks in multi-user /
multi-thread DB applications. You my onmly reduce the probability by
designing short transactions (commit frames). A deadlock occurs in these
situations (all databses, all programming languages):

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?

 

[Wibble]

So it is not possible to write a deadlock free database application using
Java technologies?

Of course you can avoid deadlocks, you just have to program and design
carefully.

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.

 

[John B. Matthews]

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

 

[Wibble]

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. Contention
means you have to wait for a lock, deadlock means you'll never get it
because of mutual dependencies.

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]

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!

 

[steve]

I am writing an application with multiple threads to insert data into
database. Each thread is responsible for inserting the data to the same
table. While I run the application, sometimes I got the following
deadlock exception. This exception does not happen very often, only
occasionally. To me, before each thread inserts data to the table, it
will aquire a lock on the table. There should be no deadlock. Anyone
knows why I got such a deadlock exception and how to solve this
problem? Thanks a lot!

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)

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.