At 02:06 AM 24/08/2006, you 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'?

WITH LOCK doesn't stop other transactions from selecting the same
records. Use FOR UPDATE WITH LOCK on both transactions if you want to
prevent the second transaction from SELECTing the record.

Also realise that the lock on a record is applied only when the
client owning the first transaction has fetched that record.

Preemptive locking is not designed as a magic workaround for
developers who don't understand how Firebird's optimistic locking
works...it seems you need to study the details in the Fb 1.5 release
notes more closely.

./heLen