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.

RE: SYSCS_DIAG.LOCK_TABLE keeps coming up enpty

[Bergquist, Brett]

If this is using JPA, matbe you are not seeing any locking because the entity 
manager is flushing is requests to the database engine only when the entity 
bean methods is returned from and the transaction manager kicks in to persist 
the changes.   

Try adding a em.flush()  call on the entity manger which will force the 
database changes to occur and then maybe you might see the lock table populated.

Just a thought.

Brett



From: Mark Stephen Krueger [m...@the-kruegers.name]
Sent: Sunday, February 03, 2013 11:21 AM
To: derby-user@db.apache.org
Subject: SYSCS_DIAG.LOCK_TABLE keeps coming up enpty

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() )
{
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.