--- In firebird-support@yahoogroups.com, "Gustavo" <gusm@...> wrote:

>
> Hello:
>
> I use Delphi 5, FireBird and IBX and I have a problem with "Select

... with lock".

>
> The table EMPRESAS has two fields: CODIGO and NUMERO. It has the

following records:

>
> CODIGO NUMERO
> -------------------------------
> A 1
> A 2
> A 3
> A 4
> A 5
> A 6
> B 11
> B 12
> B 13
> B 14
> B 15
> B 16
> ..........
>
>
> Transaction Tr1 is read_commited and nowait. I start Tr1 and execute

the query

>
> SELECT CODIGO,NUMERO FROM EMPRESAS WHERE (NUMERO = 3) WITH LOCK
>
> Transaction Tr2 is read_commited and nowait. I start Tr2 and execute

the query

>
> SELECT CODIGO,NUMERO FROM EMPRESAS WHERE (CODIGO = 'A') WITH LOCK
>
> The intention of Tr2 was to lock every record with CODIGO = 'A' (six

records). As the record with CODIGO ='A' and NUMERO = 3 is locked by
Tr1, what I expected was an error (exception) because of lock
conflict. But instead of that, no error occured and what Tr2 did is to
lock only the first two records with CODIGO = 'A' (the ones with
NUMERO = 1 and 2) and it did not lock the other records (the ones with
NUMERO = 4, 5, 6 and of course the one with NUMERO = 3).

>
> Am I doing something wrong? Is there a way to do this and to get an

exception when Tr2 tryes to lock the six records with CODIGO = 'A'?

>

I haven't tried with IBX, but using 2 iSQL windows, running in each:

SET TRANSACTION NO WAIT READ COMMITTED;

With an equivalent logic query, I get a lock conflict as you would
expect. Better check for obvious errors (connecting to different
databases, transaction isolation means that one of the transactions
doesn't see the conflicting record, etc).

Adam