Finally I found the solution. I am not using a TIBQuery. I am using a TIBSQL
because it is faster, and this component doesn´t have the Unidirectional
property. What I did was a cycle while not EOF do Next; after the ExecQuery
with the SELECT ... WITH LOCK of Tr2 and then, when the cycle reaches the
record locked by Tr1, I get the lock conflict exception.

Thank you very much for your help.

Gustavo

----- Mensaje original -----
De: Helen Borrie
Para: firebird-support@yahoogroups.com
Enviado: Jueves, 24 de Agosto de 2006 01:26
Asunto: Re: [firebird-support] Select with lock does not lock


At 01:15 PM 24/08/2006, you wrote:

>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.

Because the "Cursor" tab of IB_SQL fetches
records one by one (it's a TIB_Cursor behind that utility).

A TIB_Cursor set is "opened" by calling the
First() method, which causes the first record to
be fetched from the buffer. Subsequent records
are returned one-by-one using Next, until EOF is returned.

A SELECT statement doesn't fetch any records
until your application asks it to. The locking
behaviour occurs one-by-one as the client fetches
records. This actually makes TIB_Cursor ideal
for processing a (small) pessimistically locked
set. However, this particular utility doesn't
give you access to the component's methods.

Interfaces that return multiple-record sets
actually do so by placing a FETCH call inside a
client-side loop. So, in contrast, multi-record
"bi-directional" classes like TIB_Query and
Borland's TIBQuery employ a FOR loop. The number
of records fetched in the loop depends on its end
condition, e.g. while not EOF or while SomeCounter <= SomeLimit.

Try your tests in isql - you should see the
results you expect there, since its interface
can't do anything but continue fetching records
until EOF. Flamerobin also seems to behave this way.

>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?

You can't know how many records there will be
(unless there are none!). It is up to your
interface whether it allows you to set
limits. However, if I understand what you want
to achieve, "a limiting number" isn't what you
actually need here but, rather, the ability to
fetch one record and move on to the next one
until you have fetched all and only the records
you want to have locked. I'm not very familiar
with IBX in its current state of evolution, but
you might be able to get this degree of control
by setting the query's Unidirectional property true...

./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









__________ Información de NOD32, revisión 1.1721 (20060823) __________

Este mensaje ha sido analizado con NOD32 antivirus system
http://www.nod32.com




[Non-text portions of this message have been removed]