| Subject | Re: [firebird-support] Select with lock does not lock |
|---|---|
| Author | Gustavo |
| Post date | 2006-08-24T03:15:15Z |
With the first SELECT ... WITH LOCK I don´t want to prevent other
transactions from SELECTing the record. I want to prevent other transactions
from LOCKing the record.
I know that the lock on a record is applied only when the client owning the
first transaction has fetched that record. And this is working right. If
instead of the second SELECT, I execute (inside Tr2) a SELECT similar to the
first one trying to lock again the same record (the one with CODIGO = 'A'
and NUMERO = 3) I get immediately a conflict lock message.
I mentioned I use IBX but it´s not important in this case.
I tried this with two IB_SQL windows and the result is the same. When I
execute the second SELECT ... WITH LOCK in the "Cursor" tab of IB_SQL, I
didn´t get any lock conflict and when I see the result at "Data" tab, I see
only the record with CODIGO = 'A' and NUMERO = 1. If I browse I see the
record with NUMERO = 2 and if I try to browse to the next record (wich
should be the one with NUMERO = 3) I get the lock conflict message. What I
want to know is why didn´t I get the lock conflict message inmediately after
executing the second SELECT ... WITH LOCK. I suppose that when this
statement tries to lock the six records with CODIGO = 'A' it should find
that one of them is already locked by another transaction and it should
fail.
Perhaps the problem is that when the second SELECT executes it only fetches
one record (instead of six) and then it locks (immediately after execution)
only one record. If this is the problem, how can I know how many records a
SELECT will fetch? Can I change the number of records that a SELECT
statement will fetch?
Gustavo
transactions from SELECTing the record. I want to prevent other transactions
from LOCKing the record.
I know that the lock on a record is applied only when the client owning the
first transaction has fetched that record. And this is working right. If
instead of the second SELECT, I execute (inside Tr2) a SELECT similar to the
first one trying to lock again the same record (the one with CODIGO = 'A'
and NUMERO = 3) I get immediately a conflict lock message.
I mentioned I use IBX but it´s not important in this case.
I tried this with two IB_SQL windows and the result is the same. When I
execute the second SELECT ... WITH LOCK in the "Cursor" tab of IB_SQL, I
didn´t get any lock conflict and when I see the result at "Data" tab, I see
only the record with CODIGO = 'A' and NUMERO = 1. If I browse I see the
record with NUMERO = 2 and if I try to browse to the next record (wich
should be the one with NUMERO = 3) I get the lock conflict message. What I
want to know is why didn´t I get the lock conflict message inmediately after
executing the second SELECT ... WITH LOCK. I suppose that when this
statement tries to lock the six records with CODIGO = 'A' it should find
that one of them is already locked by another transaction and it should
fail.
Perhaps the problem is that when the second SELECT executes it only fetches
one record (instead of six) and then it locks (immediately after execution)
only one record. If this is the problem, how can I know how many records a
SELECT will fetch? Can I change the number of records that a SELECT
statement will fetch?
Gustavo
----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, August 23, 2006 9:37 PM
Subject: Re: [firebird-support] Select with lock does not lock
> 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
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
> __________ Informacisn de NOD32, revisisn 1.1720 (20060822) __________
>
> Este mensaje ha sido analizado con NOD32 antivirus system
> http://www.nod32.com
>
>