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