I don't think this would work. In my understanding if two transactions start
at the same time, they would see a snapshot of the table as it existed at
the start of the transactions. Thus both could insert because the
Where clause on the insert would not see the other transactions changes.
Even with no isolation it would still be problem because the physical time
it takes for the server to do the select and then insert the record. Only
solution I see unfortunately is a table lock.

Andrew

-----Original Message-----
From: Rick DeBay [mailto:rdebay@...]
Sent: Wednesday, January 12, 2005 20:44
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] Re: Transaction concurrency



Don't forget the check constraint on the table so start is less than end.

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
To: Firebird-Java@yahoogroups.com
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
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] Re: Transaction concurrency

> 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







Yahoo! Groups Links










Yahoo! Groups Links










Yahoo! Groups Links