At 10:08 PM 22/04/2007, you wrote:

>On Sun, 22 Apr 2007, Helen Borrie wrote:
>
> > At 05:01 PM 22/04/2007, you wrote:
> >> Hello!
> >>
> >> I'm new to firebird but have experience with other databases (MySQL,
> >> PostgreSQL, Oracle, ...).
> >>
> >> Setup worked well but I'm having the following problems:
> >> 1.) Other session not updated:
> >> Session 1 with isql:
> >> INSERT & COMMIT
> >>
> >> Session 2 with isql after commit of session 1 (isql already open):
> >> SELECT COUNT(*) from tablename; -- Still 0, works only after quitting isql
> >> and restart it.
> >
> > All you actually need to do is COMMIT the transaction in Session 2
> > first! isql's transaction is by default in snapshot (concurrency)
> > isolation so your existing isql session won't see what other
> > transactions have done until after the commit. (isql will then open
> > a new transaction: no need to quit to achieve this!!)
> >
> > ./heLen
> >
>
>Ok, I'm doing a
>SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED;
>in both sessions.
>
>But now when I'm doing:
>Session 1:
>SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED;
>insert into ....
>-- sleep here, goto Session 2
>commit;
>
>Session 2:
>select count(*) from ...
>-- it hangs here until the commit
>
>PostgreSQL handles this situation without a lock of session 2 in the same
>isolation level.
>
>Firebird should handle this also very well because it has MVCC, right?

Yes. But Firebird does no "locking of transactions". Locking is at
record level. If, and only if, one transaction succeeds in posting
an update to a record, that record will be locked from writing an
update by any other transaction until the transaction that has the
lock either commits the transaction or rolls it back.

From your description, you have uncommitted work (insert or delete)
in Session 1.

Because of the read committed isolation, Session 2 cannot get a
count(*) if there is an insert or delete pending that occurred since
the Session 2 transaction began. Firebird does not support Dirty
Read, which means it disallows a "dirty count". It must WAIT
(because you told it to) until that pending insert or delete is
committed or rolled back, in order to get the correct result. If you
remove the WAIT attribute, you will get an exception instead.

You can commit the transaction in Session 2, before requesting the
count. Then, you will succeed in getting a count of all committed
records, whether Session 1 has committed its pending work, or not.

./heLen