Take the 2-minute tour ×

I'm trying to debug an issue with a Derby deadlock under Glassfish (EJB app). I want to view the lock table at various points so I wrote the following code. The problem is every where I place a call to it, the lock table is always coming back as empty. What am I missing?

private void dumpLockTable()
    try ( Connection connection = dataSource.getConnection() )
        PreparedStatement ps = connection.prepareStatement( "SELECT * FROM SYSCS_DIAG.LOCK_TABLE" );
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columns = rsmd.getColumnCount();            
        StringBuilder sb = new StringBuilder();
        sb.append( "Lock Table\n" );
        while( rs.next() )
            for ( int col = 1; col <= columns; col++ )
                sb.append( rs.getString( col ) );
                sb.append( "\t|" );
            sb.append( "\n" );
        logger.info( sb.toString() );

    catch ( SQLException sqle )
        logger.throwing( LOG_CLASS_NAME, "dumpLockTable", sqle );

This is Derby 10.8 under Glassfish I'm obtaining the DataSource with:

private DataSource dataSource;

All other Derby activity is via entity beans and the entity manager.

share|improve this question

2 Answers 2

Your code looks OK to me; I'm not sure what's wrong with it.

But perhaps when you run it, there simply aren't any locks being held at that time.

You might start the diagnosis process by doing this using the 'ij' tool, and two 'ij' sessions:

  1. in the first session, connect to the database and use the LOCK TABLE statement to lock some tables
  2. in the second session, connect to the database and run SELECT * FROM SYSCS_DIAG.LOCK_TABLE

See if you can see the contents of the LOCK_TABLE diagnostic table in a scenario like that. Once you can, you can use that same technique (artificially holding locks in an ij session while you run your code) to further debug your subroutine code.

There are some good suggestions about diagnosing Derby lock behaviors in the wiki and in the docs.

You might want to vote for this Derby enhancement request.

share|improve this answer
I gave that a shot. ij> lock table app.mytable in exclusive mode; 0 rows inserted/updated/deleted ij> select * from SYSCS_DIAG.LOCK_TABLE; ... 0 rows inserted/updated/deleted Both ij sessions show the lock table as empty. –  Integrator Feb 3 '13 at 18:07
Note that derby.log does show some limited lock data when the deadlock exception happens, so it must be creating locks at some point. –  Integrator Feb 3 '13 at 18:15
Make sure you're not in AUTOCOMMIT mode, both in your IJ sessions and in your application. In AUTOCOMMIT mode, locks are taken normally, but transactions commit at the end of each statement, releasing the locks. –  Bryan Pendleton Feb 4 '13 at 14:54
That was it for the IJ test sequence. IJ apparently is in autocommit mode by default. If I first do "autocommit off" in each IJ session then I see locks in the table with both IJ sessions. –  Integrator Feb 6 '13 at 0:58
up vote 1 down vote accepted

I got an answer from the Derby mailing list that I figure I should add for completeness. Apparently the entity manager is not actually sending the commands out the the database right away (I think that sounds familiar but I had forgotten about it), so that would explain why the lock table did not show any locks. If I add an entityManager.flush() before I do the lock table dump, I get a good list of locks. Now I am able to debug my sequences that lead to a deadlock.

share|improve this answer

Your Answer


