VCursor lock question - 2d try

[Ivan Smahin]

Hi,

On Oct 8, 2013, at 7:38 PM, François Van Lerberghe <frvanlerberghe at gmail.com> wrote:

> As nobody answer my question, let me post it again :
> 
> I have a function (RealBasic/Xojo method) where I update every record of a
> table (5000 records). In order to do that, I make a VCursor with
> EVLokType.kReadWrite on the whole table.
> At the end of this function , I call another function to update a few
> records (50) of that same table. This update is in another function because
> it is called by some other places of the project. How must I deal with the
> records locking in this second Vcusor using the same table, but not the same
> records ?
> 
> 1) If I nil the first Vcursor (locking the whole table) before create the
> second, locking the needed records, I have a risk (very low I know, but that
> exist) that, just before I create my second Vcursor, another user lock some
> records of that table, reading or writing incorrect data and stopping me to
> complete the work.
> 
> 2) Must I keep the first Vcursor ensuring that nobody can modify the table
> and create the second with kNoLock ? Is it safe to update the records in
> this second Vcursor ?
> 
> 3) I see a third option : Keeping the first cursor. I pass this Vcursor as
> parameter to iterate, test each record and update the records that meet some
> condition. I don't like this option as
> - I prefer sending a second SQL query to retrieve the needed records rather
> than iterate and test each record
> - this second function is called by some other places of the project
> 
> I prefer the second option if it is safe. Could you be kind enough to give
> me your advice on this ?


All that ways are possible.
If you prefer the second way - no problem to use it.
The only thing is - when you use kReadWrite cursor - it means nobody else will be able to create another RW/RO cursor over the same record (sub)set.
But that records are still available for updating via NoLock cursors or directly (using I_Table interface).

I think you need something like "atomic" peace of your business logic - do few updates against some data without a chance to be interrupted by another client.
I would suggest to move it all into some stored procedure and call that sp from your app.


-- 
Best regards,
Ivan Smahin
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com