Lock a table during update to prevent selects

Lock a table during update to prevent selects

  

Hey guys,


I'm in an issue right now in which I must implement an action that will for sure be accessed by multiple users at the same time ( concurrency ),

I have a table with a counter.

Table: Control

Fields: ID, SecID and Counter

The case is the next:

1) User enter action,

2) Selects the Counter Field associated with his pretended ID

3) Saves the Counter Value to a variable

4) Updates the Counter field value in the table incrementing it by one


The problem is that if user B reaches 2) without the user A reaching 4) they both will get the same values.


So I need to lock the Table or better the Row  Before the Select and release it after the Update,


You guys know what is the best approach for doing this in OS? 

I am using SQL Server 2014 and OS Version 9.1.609


Thanks in advance

VC

Hello Vincent

I'm not sure I completely understood your issue, but did  you try using the GetTableForUpdate CRUD action?

It locks the the record to prevent this kind of concurrency problems

Regards,

Ângelo

Hi Vincent,

That seems to be the use case for the Get<Entity>ForUpdate entity action. You get the Id and locks that row until you do the update of the record.

Is that it?

Cheers,

José

Hello Vincent,

I think this is a typical behaviour of a ticket vendor site, for example.
What comes to my mind is: when switching from step2 to step3 and from step3 to step4, there's a server call to verify if the amount/quantity selected by the user is still available.

Best Regards

 

Hey

So i made this:

One button that calls the GetForUpdate action then I make a loop for 10 seconds and finally Update the record.

Another button that selects the record I am updating.


So I tried it and it has the following output:

If I use two tabs in the same computer / session it works!

If I try in two different computers / sessions it wont work!

Am I missing something? What am I doing wrong?


Cheers

VC

Hello Vincent

I don't think you need two buttons and a loop

I've never used the GetForUpdate action myself, but what I would do is:

1) create the screen action that does the Update

2) Use the GetForUpdate action

3) Develop the logic  you want for the update

The GetForUpdate does not need a loop (I think). What I think it does is a "Begin Transaction" so other users cannot access that specific record


Regards,

Ângelo

Hey,

the loop was only for test purposes.. cause I need to see it happening not just assuming it works! So I forced a long time in the action that does the update so I can really see that the select is waiting for the update to end you understand??

Its just a test.

And it worked perfectly when I tried in my computer.. But when I start the getforupdate and my partner tries to select the entity from a different computer it will select the data while im doing the update, and it should not be the case.

I can share the test OML to make it clearer if you need


Cheers

VC

Hey Vincent.

Please do

Regards,

Ângelo

Hi Vincent,

The GetForUpdate action does not prevent other users from reading the record. It only prevents them from updating/deleting said record. If you use your second button to try to update the record locked by GetForUpdate, you'll notice that that operation will "hang" until the first one is completed.

Hey,

so the GetForUpdate is not what I need cause my issue is actually that,

you have any workaround? maybe use pure SQL locks? Im kind in a dead end and am searching for a friendly solution,

thanks for spending your time helping guys,

waiting for some hints! :)


Cheers

VC

Hi Vincent,

Have you considered the use of the update and a select in the same Advanced Query, inverting the order?

UPDATE Control SET @counter = Counter, Counter = Counter + 1 WHERE Id = @Id;
select @counter;

You will need a "dummy" input parameter in the query, the @counter, and a structure with an integer attribute to use as the Output Structure.


As the Update blocks the reading, including dirty reads using NOLOCK (If I still remember how SQL Server work, don't know about Oracle), the UPDATE will first store the actual value in the counter variable, and than update it, and only after it will release the lock, and if someone is trying to read, will now see the new value...

I did a small test here: https://eduardojauch.outsystemscloud.com/UpdateTest/Controls.aspx

No concurrence in my test, just to see the code working :)

Hope this helps.

Cheers,
Eduardo Jauch

Hi Vincent,

Now I understand what you want... The GetForUpdate will not work.

The solution from Eduardo will work.

Cheers,

José