> What I want to do, is that noone can insert a record, whose
> timestamp isn't disjunct with all inserted timestamps (take table

reservations:

> you can't reserve a table from 20 to 23 if that table is already
> 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

Please note, that procedure running in one transaction does not see
uncommitted changes done in another transaction.

> Now what I though was that these two parts are handled as ONE atomic
> operation, but after some testing I may be wrong

This is the issue with transaction isolation. I assume that you use default
transaction isolation TRANSACTION_READ_COMMITTED or
TRANSACTION_REPEATABLE_READ.

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 stored
> procedure as ONE atomic operation?

Yes. Changes made by the procedure either succeed all or they are rejected
if server detected an inconsistency in the database. However server cannot
know about logical inconsistency that results from the way your application
work.

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 1
> 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
> this)

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.

Roman