VCursor lock question

[Bart Pietercil]

Stored Procedure (SP) seems the way to go.
Yes a SP is atomic (nothing can interfere). However be carefull as the execution time of the procedure will block any interaction (from other clients) for that time.

Also keep in mind that this is why transactions are invented. As the implementation of transactions is not yet complete in Valentina you have to make sure that ANY statement within your procedure will work without throwing an exception or you will have your database in a state that some records are updated and others are not updated.

regards

b.


On 09 Oct 2013, at 11:44, François Van Lerberghe <frvanlerberghe at gmail.com> wrote:

> Thank you Bart.
> 
> To update my first cursor, I need to get the value of some fields and put
> them in other fields. I don't think I can do that with an UPDATE statement.
> Next I need search for some records corresponding of several criteria and
> update them.
> As these records are from the same table of my first cursor, I cannot get
> them in Read and Write mode until the first cursor is destroyed.
> As I want nobody access this table in read or write mode, I don't think that
> 2 Update statement are secure. Indeed, with this way, after the first
> Update, nothing prevent another user to lock some records needed before my
> second update was executed.
> 
> The Stored procedure seems attractive, but as I never used it, I must
> discover this feature.
> 
> Is a stored procedure atomic or something (someone) could interact on the
> data during its execution ? I don't see anything in the doc about this.
> 
> François Van Lerberghe
> Rue Thier Monty, 15 A
> 4570 Marchin
> +32 (0)85 25 08 25
> 
> 
> le 9/10/13 9:19, Bart Pietercil <bart.pietercil at cognosis.be> a écrit :
> 
>> 
>> 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
>> 
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina