Hi Roman,

Ok, I've been playing around with this, and made some progress as to what
is going on...

The first thing my thread does is a read. If I change this to a write,
then I get the lock I expect.

However, that must be a bug, right, as I am using table reservation?

To quote Ms Borrie again: "The following conditions will always block the
other transaction from reading a table reserved by ThisTransaction...2) The
other transaction is configured to reserve the table PROTECTED WRITE".

So, am I missing something, or is this a bug? If it is, is it a driver bug
or a Firebird bug?

To summarise, if I create a transaction with the following parameters:

//SNAPSHOT TABLE STABILITY Isolation Level
tpb.addArgument(TransactionParameterBuffer.CONSISTENCY);
//READ WRITE access
tpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
tpb.addArgument(TransactionParameterBuffer.WAIT);
//PROTECTED WRITE Reservation on Test_Lock table
tpb.addArgument(TransactionParameterBuffer.PROTECTED);
tpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

and I start the transaction by doing a select statement, I should get a
lock on the table which prevents another transaction with the same
parameters from reading the table. I am NOT seeing this - this apparently
only happens if I start the transaction by doing an INSERT statement.

I would also expect that changing the Isolation level to SNAPSHOT ie
TransactionParameterBuffer.CONCURRENCYwould give me the same lock, as I am
getting the lock from table Reservation.

Your comments, at your earliest convenience, would be greatly appreciated!!
There's some simple code to test this below...run it from the command line
in 2 console windows.

cheers,

David

PS Is
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.firebirdsql.gds.TransactionParameterBuffer;
import org.firebirdsql.jdbc.FBDriver;
import org.firebirdsql.jdbc.FirebirdConnection;

/**
* Test isolation in Firebird extensions.
*
* @author dhay
*/
public class GetFirebirdLock
{

private static BufferedReader stdin = new BufferedReader(new
InputStreamReader( System.in ) );

public static void main(String args[])
{

GetFirebirdLock gfl = new GetFirebirdLock();
gfl.getLock();
System.out.println("finish");

}

private void getLock()
{
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 TABLE STABILITY Isolation Level
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";
//THIS DOES NOT GIVE THE RIGHT LOCK
String 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() + " SHOULD
HAVE TABLE LOCK - executed sql " + sql);
stmt.close();

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();
}
}

protected FirebirdConnection getConnectionViaDriverManager() throws
SQLException
{
try {
Class.forName(FBDriver.class.getName());
} catch(ClassNotFoundException ex) {
throw new SQLException("No suitable driver.");
}

return (FirebirdConnection) DriverManager.getConnection(getUrl(),
getDefaultPropertiesForConnection());
}

protected Properties getDefaultPropertiesForConnection()
{
final Properties returnValue = new Properties();

returnValue.setProperty("user", "framework");
returnValue.setProperty("password", "rivet");
returnValue.setProperty("lc_ctype",
System.getProperty("test.db.lc_ctype", "NONE"));

return returnValue;
}

protected String getUrl()
{
String url="jdbc:firebirdsql:localhost/3050:FRAMEWORK";
return url;
}
}




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

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

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

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




> 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.

Which is more or less how it is supposed to behave - it generates you
message about deadlock on transaction start. You can then wait and restart
it... I would however expect it to wait until it gets the lock, but I'm not
sure if that is designed so...

> 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".

Ok, I'm sure Helen knows more...

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

Right.

> 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?

Only by executing some dummy statement, for example "SELECT * FROM
rdb$database".

> 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?

Yup, it is quite surprising...

> I would greatly appreciate it. As you
> can tell, I'm fairly new to all this!

Me too :) Only that I coded that part...

Roman




Yahoo! Groups Links