Announcement Announcement Module
No announcement yet.
How to replicate database deadlocks? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to replicate database deadlocks?

    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,

  • #2
    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?


    • #3
      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.


      • #4
        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.


        • #5
          Okay here's how to do it:

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

          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

          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

          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");

          // Executing sp_deadlock_2 will cause a deadlock
          jt.execute("exec sp_deadlock_2");

          Thanks to Larry Coon's posting here:
          for showing how to create a deadlock.


          • #6
            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">

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