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