|Re: [Firebird-Java] Transaction concurrency
> What I want to do, is that noone can insert a record, whosereservations:
> timestamp isn't disjunct with all inserted timestamps (take table
> you can't reserve a table from 20 to 23 if that table is alreadyPlease note, that procedure running in one transaction does not see
> reserved from 18 to 20:30. These reservations are not disjunct, because
> they coincide from 20 to 20:30)
> My stored procedure is made of two parts:
> first part- checks if there are any conflicting records
> second part- throws exception if there are conflicting records,
> otherwise insert the given record
uncommitted changes done in another transaction.
> Now what I though was that these two parts are handled as ONE atomicThis is the issue with transaction isolation. I assume that you use default
> operation, but after some testing I may be wrong
transaction isolation TRANSACTION_READ_COMMITTED or
Procedures are handled as one atomic operation, however what you do there is
something different. You first read the reservation table without placing
any lock (pure read-only operation), then insert some record based on the
information you derive from a read-only operation. Other transaction
performs first phase (pure read-only operation). There is no conflict.
INSERT does not conflict with SELECT. Then you perform second insert which
also does not conflict with SELECT and INSERT from the first transaction.
> Just to check if the bug is in my program: does firebird handle a storedYes. Changes made by the procedure either succeed all or they are rejected
> procedure as ONE atomic operation?
if server detected an inconsistency in the database. However server cannot
know about logical inconsistency that results from the way your application
The best solution would be to define a table CHECK constraint, however check
constraints in Firebird are evaluated immediately and you still can solve
the described issue. Some RDBMSes support deffered constraint evaluation at
commit time. This would be the most elegant solution to the problem.
> (if not, inconsistency can occur when client A just finishes part 1What you need in Firebird, is serial execution of the transactions, i.e.
> of the procedure checking for confliscting records. Then client B
> finishes part 2 of the procedure before client A gets to it's second
> part. B just inserted a record , that conflicts with client A, but
> client A will not check again, it just inserts it's record after all
that only one transaction is executed at a time. In this case your logic
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