Timeout trying to lock table when using transactions

[Olaf van der Meer]

Hi,

A Timeout trying to lock table "SYSTEEM" error occurs in the next situation:


dbConnectionA is in transaction ( using setAutoCommit( false ) )
dbConnectionB is NOT in transaction

dbConnectionA is used to save some data (for instance an invoice )
dbConnectionB is used to create unique numbers (like invoice nr for instance)

saveInvoice()
{
dbConnectionA.startTransaction();
try
{
Invoice invoice = new Invoice();
invoice.setDueDate( getDueDate( dbConnectionA ) );

invoice.setNr( getNextInvNr( dbConnectionB ) ); // Timeout trying to lock table "SYSTEEM";
invoice.save(dbConnectionA);

//and save its details

dbConnectionA.commitTransaction();
}    
finally
{
if ( !dbConnectionA.isTransactionCommitted() )
dbConnectionA.rollBackTransaction();
}
}

public static synchronized getNextInvNr( dbCon ){
// Reads the next invoice number from 'SYSTEEM' and returns is
// It also increases this number in the table 'SYSTEEM' and save it to the database.
// I use a public static synchronized method to synchronize multiple users (Threads)
}

getDueDate(){
// reads due date from table 'SYSTEEM' and returns it
}



I discovered, that if I do read the getDueDate( dbConnectionA ) the error does not occur.
Is it logically reading from a table using a connection which is in transaction is creating a lock on the table and causes this error?

If I don't start a transaction the error does not occurs either.


I am using a bit old h2database version: 1.4.190.
And using this properties: ..\Data;FILE_LOCK=NO;MV_STORE=FALSE;MODE=REGULAR;CACHE_SIZE=16384;DB_CLOSE_DELAY=0;IGNORECASE=TRUE;DATABASE_TO_UPPER=TRUE;