Hi Roman,

Thanks for the reply. Yes, the test case provided does work with
CONSISTENCY, but my test case with 2 threads now results in a deadlock,
even though I use WAIT, when I change to CONSISTENCY.

The reason I used CONCURRENCY was due to reading the Helen Borrie's The
Firebird Book pg 526 & 527: "using table reservation with SNAPSHOT or READ
COMMITTED isolation is recommended in preference to using SNAPSHOT TABLE
STABILITY when table-level locking is requuired....using it in combination
with SNAPSHOT TABLE STABILITY is not recommended".

My understanding is that isc_tpb_concurrency corresponds to SNAPSHOT, and
isc_tpb_consistency corresponds to SNAPSHOT TABLE STABILITY?

The book recommends RESERVATION because it locks all rows pessimistically
at the beginning of the transaction, rather than when first accessed by a
statement. This goes back to my earlier question - how do I start a
Transaction before issuing a statement using Jaybird?

I am sure you're very busy, but would you have a minute to run the attached
threaded testcase, or can you see why a deadlock would be the result even
if I am using WAIT? I would greatly appreciate it. As you can tell, I'm
fairly new to all this!

cheers,

David

public void testLockTable() throws Exception
{
//make sure table is created
FirebirdConnection connection =
(FirebirdConnection)getConnectionViaDriverManager();

try
{
Statement stmt = connection.createStatement();
try
{
stmt.execute("CREATE TABLE test_lock(col1 INTEGER)");
}
catch(SQLException ex)
{
// ignore if already exists
}
}
finally
{
connection.close();
}

//thread which try and read and write to test_lock within a
transaction.
DoItThread t1 = new DoItThread(),
t2 = new DoItThread();

t1.start();
t2.start();

//wait for threads to finish
while (t1.isAlive() || t2.isAlive())
{
//System.out.println("waiting for threads");
Thread.sleep(500);
}

System.out.println("finish");
}


class DoItThread extends Thread
{
public void run()
{
try
{
//get connection
FirebirdConnection fbConnection =
getConnectionViaDriverManager();
System.out.println(Thread.currentThread().getId() + "
connection obtained for this thread: " + fbConnection);

try
{
//turn autocommit off
fbConnection.setAutoCommit(false);
System.out.println(Thread.currentThread().getId() + "
fbconnection - autocommit set to false");

//set up transaction params
TransactionParameterBuffer tpb =
fbConnection.createTransactionParameterBuffer();

//SNAPSHOT Isolation level
tpb.addArgument(TransactionParameterBuffer.CONCURRENCY);
//tpb.addArgument(TransactionParameterBuffer.CONSISTENCY);
//READ WRITE access
tpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
tpb.addArgument(TransactionParameterBuffer.WAIT);
//PROTECTED WRITE Reservation on PrinterGroup table
tpb.addArgument(TransactionParameterBuffer.PROTECTED);
tpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

fbConnection.setTransactionParameters(tpb);

Statement stmt = fbConnection.createStatement();
try
{
String sql = "SELECT * FROM TEST_LOCK";
System.out.println(Thread.currentThread().getId() + "
about to execute sql: " + sql);
stmt.execute(sql);
System.out.println(Thread.currentThread().getId() + "
SHOULD HAVE TABLE LOCK - executed sql " + sql);
stmt.close();

System.out.println("isolation: " +
fbConnection.getTransactionIsolation());

//should have lock here - wait for a while to make any
other threads wait
Thread.sleep(2000);

stmt = fbConnection.createStatement();
sql = "INSERT INTO test_lock VALUES(1)";
System.out.println(Thread.currentThread().getId() + "
about to execute sql: " + sql);
stmt.execute(sql);
System.out.println(Thread.currentThread().getId() + "
executed sql " + sql);

fbConnection.commit();
System.out.println(Thread.currentThread().getId() + "
COMMIT PERFORMED");
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
System.out.println(Thread.currentThread().getId() + "
closing statement");
stmt.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
fbConnection.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}




|---------+----------------------------->
| | "Roman Rokytskyy" |
| | <rrokytskyy@...|
| | g> |
| | Sent by: |
| | Firebird-Java@yaho|
| | ogroups.com |
| | |
| | |
| | 01/27/2006 09:48 |
| | AM |
| | Please respond to |
| | Firebird-Java |
| | |
|---------+----------------------------->

>------------------------------------------------------------------------------------------------------------------------------|

| |
| To: <Firebird-Java@yahoogroups.com> |
| cc: |
| Subject: Re: [Firebird-Java] connection-connection vs connection-isql transaction locking wierdness |

>------------------------------------------------------------------------------------------------------------------------------|




> Does anyone have ANY idea what is causing this? Would anyone have a
> minute to run the test case and verify it?

Just did it - with CONCURRENCY test case fails, works with CONSISTENCY.

> Could someone confirm that these are the correct settings for what I'm
> trying to achieve?

Why can't you use CONSISTENCY?

> Any ideas what I'm missing when it applies to 2 different connections
> created by the same DriverManager - why would it work creating it in one
> thread and trying to access it via isql, and not with 2 threads?

If isql, for example, translates SNAPSHOT + PROTECTED WRITE into
isc_tpb_consistency. I really don't know, whether that is the case, but I
also did not see in documentation that isc_tpb_concurrency can be used to
lock tables.

Roman




Yahoo! Groups Links