Subject RE: [Firebird-Java] Re: Transaction concurrency
Author Rick DeBay
Don't forget the check constraint on the table so start is less than

insert into reservations(id,starttime,endtime) values (_id,_start,_end)
where not exists (
select id from reservations
where (_start <= starttime and _end > starttime)
or (_start > starttime and _end < endtime)
or (_start < endtime and _end > starttime))

I'm pretty sure the where statement is wrong.

-----Original Message-----
From: Rick DeBay
Sent: Wednesday, January 12, 2005 11:30 AM
Subject: RE: [Firebird-Java] Re: Transaction concurrency

I should have thought of this before. In the Java code, use a NOT
EXISTS predicate in the insert statement. If the update count is zero,
then the insert failed due to the existence of another item.

This problem doesn't require table locks or exotic transaction types.

-----Original Message-----
From: nagypapi [mailto:nagypapi@...]
Sent: Tuesday, January 11, 2005 7:37 AM
Subject: [Firebird-Java] Re: Transaction concurrency

> What you need in Firebird, is serial execution of the transactions,
> that only one transaction is executed at a time. In this case your
> will work.
> Try setting the transaction isolation to TRANSACTION_SERIALIZABLE
and run
> your tests again. But please note that this transaction isolation is
> 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

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

What I found in the documentation:
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

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,

Yahoo! Groups Links

Yahoo! Groups Links