Subject Re: [Firebird-Java] connection-connection vs connection-isql transaction locking wierdness
Author Roman Rokytskyy
> 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
> 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?


> 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

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