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é

Hi,

I'am trying to apply the same ideia with Oracle but I'm getting an ORA-00933: SQL command not properly ended. 

Any ideas?

SyntaxEditor Code Snippet

UPDATE {Sequences} 
SET  {Sequences}.[LastSeq] = {Sequences}.[LastSeq]+ 1 
WHERE {Sequences}.[DatabaseID] = @DatabaseID 
returning {Sequences}.[LastSeq] into @seq; 
select @seq from dual; 



Daniel Batista wrote:

Hi,

I'am trying to apply the same ideia with Oracle but I'm getting an ORA-00933: SQL command not properly ended. 

Any ideas?

SyntaxEditor Code Snippet

UPDATE {Sequences} 
SET  {Sequences}.[LastSeq] = {Sequences}.[LastSeq]+ 1 
WHERE {Sequences}.[DatabaseID] = @DatabaseID 
returning {Sequences}.[LastSeq] into @seq; 
select @seq from dual; 



Hello Daniel, it might be a syntax error.
Please take a look at this: http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm

Best Regards


Paulo Zacarias wrote:

Daniel Batista wrote:

Hi,

I'am trying to apply the same ideia with Oracle but I'm getting an ORA-00933: SQL command not properly ended. 

Any ideas?

SyntaxEditor Code Snippet

UPDATE {Sequences} 
SET  {Sequences}.[LastSeq] = {Sequences}.[LastSeq]+ 1 
WHERE {Sequences}.[DatabaseID] = @DatabaseID 
returning {Sequences}.[LastSeq] into @seq; 
select @seq from dual; 



Hello Daniel, it might be a syntax error.
Please take a look at this: http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm

Best Regards


Hi Paulo,

I understand your point and I do not find anything wrong. If I split and individually validate the statements I do not get the error. The problem shows up when I put together both statements (update + select from dual...) and I need to have them in the same advanced query...

Any clue?

Best regards


Daniel Batista wrote:

Hi,

I'am trying to apply the same ideia with Oracle but I'm getting an ORA-00933: SQL command not properly ended. 

Any ideas?

SyntaxEditor Code Snippet

UPDATE {Sequences} 
SET  {Sequences}.[LastSeq] = {Sequences}.[LastSeq]+ 1 
WHERE {Sequences}.[DatabaseID] = @DatabaseID 
returning {Sequences}.[LastSeq] into @seq; 
select @seq from dual; 



Hello Daniel, try the following statment in an advanced query:

SyntaxEditor Code Snippet

IF EXISTS(select *
             from {SEQUENCES}
             where Databaseid=@Databaseid)
   update {SEQUENCES} set [LastSeq] =(select LastSeq+1 
             from {SEQUENCES}
             where Databaseid=@Databaseid
             )
             where Databaseid=@Databaseid
ELSE
   insert into {SEQUENCES} values(@Databaseid,1);

commit tran

   select {SEQUENCES}.[LastSeq], {SEQUENCES}.[Id] from {SEQUENCES} where Databaseid=@Databaseid;

Tell me if it works or not.

If not try this one:

SyntaxEditor Code Snippet

UPDATE {SEQUENCES} 
SET {SEQUENCES}.[LastSeq] = {SEQUENCES}.[LastSeq] + 1, 
   OUTPUT inserted.[LastSeq] 
WHERE {SEQUENCES}.[Databaseid] = @Databaseid

Dont forget that in both queries you should set an output parameter so you can get the updated value. BTW those queries are very concurrency friendly specially the second one if well handed.

Dont know if it will work in Oracle but give it a try,

Cheers,

VC