[VXCMD] Record Locks

[Ken Ray]

On 8/23/04 3:46 PM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> On 8/23/04 11:07 PM, "Ken Ray" <kray at sonsothunder.com> wrote:
> 
>>>> (BTW: The main reason Bobby and I experienced this is that we have someone
>>>> from New York accessing a database in LA, and the latency caused by the
>>>> long-distance access caused our SQLSelectRecords call to tie up all the
>>>> records during that period.)
>>> 
>>> Aha! this is an answer.
>>> 
>>> So SqlSelectRecords() have lock records with READ LOCK,
>>> And other users could not access them for WRITE LOCK, right Ken?
>> 
>> Sorry, Ruslan, but no. ALL of our cursors that are done with SQLSelect() are
>> NoLock cursors. So we only have NoLock SQLSelect() cursors and
>> SQLSelectRecords() cursors (which are as you said ReadOnly and so run into
>> each other and cause the 363).
> 
> Hmm, then I do not see where from come 363 error.

Me either! That is the issue... we get record locks *only* on the *one*
client that we have that has offices in two states. The New York office
tries to connect to the server in the LA office, and in doing so, it causes
(almost consistenly) users who are logging in at that time to get a record
lock. 

> Somebody must set or try to set WRITE lock.

I don't see how... all of our database calls go through a central "wrapper"
handler that makes the actual calls to Valentina and returns the result
(assuming there's no error). All SQLSelect() calls use kNoLock, and since
you can't set a param for SQLSelectRecords, you said it uses kReadOnly.

> If all set READ LOCK and NO LOCK, then there is no conflict.

That's what I would have thought! But we're getting them nonetheless.

Is it possible if you could allow us to have a special build that uses
kNoLocks instead of kReadOnly for SQLSelectRecords so we could test the
theory? If it still fails, then we know it has nothing to do with the lock
status of SQLSelectRecords. If it stops failing, then it has something to do
with SQLSelectRecords.
 
>>> What you think now?
>>> 
>>> I think SqlSelectRecords() works now in the correct way.
>> 
>> I don't... personally since SQLSelectRecords() only retrieves data and does
>> not change it, I think you should use kNoLocks rather than kReadOnly.... I
>> don't see the advantage of using kReadOnly instead of kNoLocks with
>> SQLSelectRecords.
> 
> Ken, then this function will work not correctly if you will use WRITE LOCKS.

I don't use WRITE LOCKS, so that won't bother me.
 
> Again, right now it works 100% correctly by db theory.
> You want READ records, you must set READ LOCK on them at first.
> 
> Again the same example:
> 
> user1 do:
>         
>     SELECT * FROM T WHERE fld1 = 200
> 
> He find 100 records which have fld1 value equal to 200.
> If now user2 in the same will change 95th record fld1 to 350,
> Then user1 will read 350 value. You see?
> 
> So user1 get problem: non consistent data.

I don't understand why it wouldn't be consistent data... user1 is getting
the current values of the records in the table at the time the query hits
those records, so *yes*, he will get the changed 95th record, but I don't
understand why that is a problem if he's just *getting* the data, and not
*setting* it? And if he *really* wanted to lock it down, he could do
SQLSelect with a read-only cursor and do it that way.

Again, my comments are related to SQLSelectRecords, which AFAIK most other
DBMSes *don't* do - they make you create a cursor, fill the cursor, get the
data from the cursor, and dispose the cursor. SQLSelectRecords is a
wonderful thing, but I don't think that preventing people from being able to
open records that are being read from a list is of benefit to the DB
developer.

Perhaps this argument is moot if the problem really is a bug in
SQLSelectRecords where its "read only" is bumping against a "no lock" and
causing a record lock when it shouldn't. If this is true, and it is fixed so
that "read only" vs. "no lock" doesn't cause any problems, then I'm OK with
leaving SQLSelectRecords as "read only".

> But again, I do not see where from your get 363 error!

Me either!

Ken Ray
Sons of Thunder Software
Web site: http://www.sonsothunder.com/
Email: kray at sonsothunder.com