VCursor lock question

[Bart Pietercil]

Hi François,

(Ivan just responded with what I was thinking)

1) When talking to vServer we only use SQL
2) you should make it more clear what 'updating 5000 records' means. As long as it is 1 statement (Ivan -> atomic) you would just give the update statement and be done with it. No way another client can intervene.
3) if the update consists of more complex (multiple) statements you can write a stored procedure that will run , when called, as 1 statement.
4) if you don't want to move your business logic into a stored procedure you can write your own locking mechanism that every client will check before attempting to modify records (table properties could be used for that , or temp fields or …)

HTH

Bart
On 09 Oct 2013, at 08:21, Ivan Smahin <ivan_smahin at paradigmasoft.com> wrote:

> 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
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20131009/7430a335/attachment.html>