Re: SYSCS_DIAG.LOCK_TABLE keeps coming up enpty

[Rick Hillegas]

On 2/3/13 8:21 AM, Mark Stephen Krueger wrote:
I'm trying to debug an issue with a deadlock with an EJB app running under
Glassfish 3.1.2.1.  The app uses entity beans and the entity manager.  I
want to view the lock table at various points so I wrote the following code.
The problem is everywhere I place a call to it, the lock table is always
coming back as empty (no rows). What am I missing?
     @Resource(mappedName="jdbc/myderbyjndi")
     private DataSource dataSource;

     ..

     private void dumpLockTable()
     {
         try ( Connection connection = dataSource.getConnection() )
Hi Mark,

By default, a JDBC connection starts out in autocommit mode. Is it 
possible that your application remains in this mode? In autocommit mode, 
it may be hard to catch your statements while they still hold locks. The 
following script shows the effect of autocommit mode on the lock table:

connect 'jdbc:derby:memory:db;create=true' as dbo;

create table t( a int );

-- empty lock table
select *from syscs_diag.lock_table;

connect 'jdbc:derby:memory:db' as other_user;

autocommit off;

insert into t( a ) values ( 1 );

set connection dbo;

-- now there are locks held by other_user
select *from syscs_diag.lock_table;

Hope this helps,
-Rick
         {
             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 );
         }
     }


I believe this is Derby 10.8.