How to replicate database deadlocks?

[eliot]

Hi all,

Can anybody recommend a way to replicate database deadlocks programmatically, which forces the database to kill one of the transactions?

The specific situation I'd like to replicate in an integration test is as follows:

Step 1) Transaction A starts
Step 2) Transaction A performs an update to 'MyTable'
Step 3) Transaction B starts
Step 4) Transaction B attempts to read from 'MyTable' but cannot as Transaction A has not completed, so the database (Sybase in this case) kills transaction B and "com.sybase.jdbc2.jdbc.SybSQLException: Your server command (family id #0, process id #388) encountered a deadlock situation. Please re-run your command. " is thrown

At the moment I get to Step 4 and then the read hangs. I want Step 4 to throw the exception immediately.

The test would then test the deadlock is handled correctly, where DeadlockLoserDataAccessException is used to signify the deadlock.

Ideas appreciated,
Eliot

[Costin Leau]

I'm not sure if there is an easy way to do this (not all databases have this functionality). To efficiently determine a deadlock you will have to know what resources are affected by a lock - this way when another lock is created you can see if there are conflicts and thus abort the operation. However, while a database can do this for you (when starting the transaction and reading/writing the data) you can do this unless you start replicating db functionality.
Why do you need this requirement?

[eliot]

Thanks for your quick response Costin.

This is what the requirement boils down to:

To test that when the database experiences a deadlock Sybase throws an exception with an expected error code.

[Costin Leau]

If you want to test this why not translate the sequence mentioned above into JDBC (or Hibernate or whatever tool you are using for database access)?
You can start the tx try to access table a and in a different thread start another tx and try to write on the same table.
The test package from Spring contains actually such methods (endTransaction, startNewTransaction). I'm used this to test behaviour of deadlocks and there might be even a test inside Spring unit tests - I'm not sure though.

[eliot]

Okay here's how to do it:

1) Create these tables in your database:
create table deadlock_test_1 (a int)
go
create table deadlock_test_2 (a int)
go

2) Create these procedures in your database:

create procedure sp_deadlock_1 as
begin transaction
lock table deadlock_test_1 in exclusive mode
waitfor delay '00:00:15'
lock table deadlock_test_2 in exclusive mode
commit transaction
return
go

create procedure sp_deadlock_2 as
begin transaction
lock table deadlock_test_2 in exclusive mode
lock table deadlock_test_1 in exclusive mode
commit transaction
return
go


3) Then to replicate a deadlock in code you need to run sp_deadlock_1 and sp_deadlock_2 concurrently on 2 separate Connections, e.g.:

public static void main() {
// Assume you already have a connection pool dataSource,
// If not, create 2 Connections and wrap with SingleConnectionDataSource
// and run each proc on their own Connection.
final JdbcTemplate jt = new JdbcTemplate(dataSource);

// JdbcTemplate is thread safe so can be used concurrently in this thread
// and the spawned thread "thread1"
Thread thread1 = new Thread() {
public void run() {
jt.execute("exec sp_deadlock_1");
}
};

thread1.start();
Thread.sleep(7000);
// Executing sp_deadlock_2 will cause a deadlock
jt.execute("exec sp_deadlock_2");
}

Thanks to Larry Coon's posting here: http://www.dbtalk.net/comp-databases...ed-102292.html
for showing how to create a deadlock.

[eliot]

Adding this to make posting comprehensive:

If you want the 2nd jt.execute() call above to throw a DeadlockLoserDataAccessException then copy the sql-error-codes.xml out of the spring.jar, add the following entry to the Sybase bean:

<property name="deadlockLoserCodes">
<value>1205</value>
</property>

Now put your modified sql-error-codes.xml into the application's classpath.