Subject Re: Transaction concurrency
Author nagypapi
> What you need in Firebird, is serial execution of the transactions, i.e.
> that only one transaction is executed at a time. In this case your logic
> will work.
>
> Try setting the transaction isolation to TRANSACTION_SERIALIZABLE
and run
> your tests again. But please note that this transaction isolation is
very
> restrictive (reads conflict with writes with table-level locks).
Please, use
> with care.

I just read a bit of the basics of transactions, and I see I have to
plan my database access a little wiser

What I need is an explicit write access lock (some kind of
table-mutex) on that specific table, that doesn't conflict with
read-only operations. And this lock must live through the whole
transaction. This lock must also disappear if client or server crashes
unexpectedly

//java pseudo code:
lock table
call stored proc
commit
unlock table

What I found in the documentation:
"
SNAPSHOT TABLE STABILITY
locks entire tables for write access, although concurrent reads by
other SNAPSHOT and
READ COMMITTED transactions are permitted.
"

My questions:
locking a table the way I want to is aquired by setting the
transaction isolation level (SET TRANSACTION ISOLATION LEVEL...)?

What's the difference between TRANSACTION_SERIALIZABLE and SNAPSHOT
TABLE STABILITY?

Can I set the transaction isolation level in the SP, or do I have to
turn autocommit off and set it in my application? (setting it in sp
would be wiser)

What colour is my hair?

Thank you for the help I already got,
John