Concurrency and Locking in Outsystems

Greg Berger

Question

Hi - we're a relatively new customer of Outsystems and are building our first app. I'd really like to know how other customers are dealing with concurrency/locking within their apps. Maybe in my old age I'm completely missing something as to how this works with Outsystems. Here's a simple scenario:

Step 1 - User 1 reads client Acme with account balance $100

Step 2 - User 2 reads client Acme with account balance $100

Both now have a "cached" copy of client Acme - right?

Step 3 - User 1 subtracts $20 from Acme's account balance and saves

Step 4 - User 2 adds $50 to Acme's account balance and saves

User 2 gets no indication that he had "dirty" data, and it appears everything saves fine for both users, but User 1's change is lost and the account balance is now $150 instead of $130.

In an standard optimistic locking scenario, I would have a timestamp on the Acme client record that reflects the last update datetime. Both users would have the same timestamp on their "cached" copy when read. When User 1 updates Acme in Step 3, the Where clause of the Update sql statement would compare the timestamp User 1 has in the "cached" copy to the timestamp on the database - in this case they are the same and update would succeed (including updating the timestamp). When User 2 updates in Step 4, the timestamps would not be equal and User 2 notified that he needs to redo his update. Hence, optimistic locking and clean data.

Since the platform does not allow you to add a condition to the where clause on the generated update action, it seems my only choice to implement optimistic locking is to read client Acme again just before the update and ensure the timestamp is still the same. That kinda works, but leaves an opportunity for another update to occur between the read and update in my action (plus it's not very efficient).

Which now leaves me with using Get For Update actions. Since Get For Update does not prevent reads, both users in Steps 1 and 2 still can have copies of the same data. So, what am I doing in the Save action to prevent User 2 from updating with dirty data in Step 4? I don't understand how Get For Update will prevent this situation. I'm confused...

Gotta say - although I knew this coming in, I'm still a bit in shock that there is no support for optimistic locking within the platform. In 30 odd years of enterprise development on many platforms, I have only seen a handfull of times where actual database locking was used, and those were in mainframe and/or client/server apps where dirty reads could be controlled, not in stateless web applications.

Please help me understand this...we cannot end up with an app that allows corrupted data, not matter how infrequent.

Thanks...Greg

13 Sep 2012

Rebecca Hall

Once someone opens up a web screen it is cached for the users sessions (i.e. in the EditRecord object). However, whoever is saving the data has the possibility of saving data on top of OR in addition to the data being saved by someone else. There is a component that allows you to do screen locking to only allow one person to edit a record at a time. https://www.outsystems.com/NetworkSolutions/ProjectDetail.aspx?ProjectId=50

13 Sep 2012

Rodrigo Catarino

Hi,

I use the read before the update or in more sensitive cases I use and advancedquery to update data.

You can also vote on my idea ;)

https://www.outsystems.com/ideas/541/

13 Sep 2012

2 replies

Last reply 07 Sep 2017

Show thread

Hide thread

Rajendra Singh

Rodrigo Catarino wrote:

Hi,

I use the read before the update or in more sensitive cases I use and advancedquery to update data.

You can also vote on my idea ;)

https://www.outsystems.com/ideas/541/

Hi Rodrigo , sorry but i am not able to understand, that the advantage to us the advancedquery to update the data.

21 Aug 2017

Rodrigo Catarino

Rajendra koranga wrote:

Rodrigo Catarino wrote:

Hi,

I use the read before the update or in more sensitive cases I use and advancedquery to update data.

You can also vote on my idea ;)

https://www.outsystems.com/ideas/541/

Hi Rodrigo , sorry but i am not able to understand, that the advantage to us the advancedquery to update the data.

Hi,

The simple update doesn't allow to have another variable in the where clause, so in order to do that I use an advancequery like mentioned earlier by Greg

Update table set Somecolumn = @NewValue, LastUpdateDateTime = @CurrentDateTime where Id = @RowId and LastUpdateDateTime = @OriginalDateTime from row when read.

07 Sep 2017

MVP

Hi,

Interesting thoughts which we also happen to have at our Company :)

First of all, I am still not sure if optimistic locking should be made available by the platform itself.
I have no idea how often it will be used and how to prevent overhead for apps who don't care about it.

Our solution so far:
We normally add userid/datetime columns to the tables ourselves.
We don't directly use the create/update/delete but wrap it inside actions so we can add extra checks or custom-logging

14 Sep 2012

André Vieira

Staff

Greg Berger wrote:

Step 1 - User 1 reads client Acme with account balance $100

Step 2 - User 2 reads client Acme with account balance $100

Both now have a "cached" copy of client Acme - right?

Step 3 - User 1 subtracts $20 from Acme's account balance and saves

Step 4 - User 2 adds $50 to Acme's account balance and saves

User 2 gets no indication that he had "dirty" data, and it appears everything saves fine for both users, but User 1's change is lost and the account balance is now $150 instead of $130.

Please help me understand this...we cannot end up with an app that allows corrupted data, not matter how infrequent.

Thanks...Greg

Hi Greg,

First of all welcome to our community.

As Rebecca explained once you fetch the data from the database and you send it to the client you have a "cache" version of the data, better saying you have a snapshot taken at that time.
In the scenario where you're having concurrent updates of the same record you have two options:

Either you don't care about concurrency and last update is the maste - this is the scenario you described
Or you really care about updates and you want to control them

For this second situation you can do the following:
Let's say you have the following data model:

Every time you create an operation you want to update the balance in the Account entity.
In order to do this you need to have mutual exclulsion. In the Agile Platform to accomplish this you should use a database lock through the API for the entities,i.e. using Get<Entity>ForUpdate. So somewhere where you are creating the operation you would need to update the balance account and you'd do something like:

If both User 1 and User 2 get here only one will be able to fetch the account from the database the other one will wait on the database lock. This logic should de done in the Save action because of what you've seen before about the "cache" or snapshot.

I hope this helped you.

Cheers,
André

14 Sep 2012

Greg Berger

Good to see that there is an active community of helpful people here. I'll respond to each of you below:

Rebecca - thanks for pointing out that component, I'll look into it further. Am REALLY curious as to how the internals of that works...

Rodrigo - your idea is exactly what I am looking for, I gave you a vote and added some comments to your idea.

Joost - I'm unclear on your solution, please expand how it works. As to your concerns on application overhead, I would venture that there is none - you are ultimately adding an additional condition to the WHERE clause on updates to ensure that your local copy of the data is not stale. The only additional coding is to handle the not found condition that occurs when you are working with stale data, but you have to handle that anyway for the case where someone has deleted the row you are updating.

Andre - a couple comments:

First, you said "Either you don't care about concurrency and last update is the master" - assuming this is a multi-user application, I have a hard time believing that anyone who is aware that this can happen would simply choose to ignore it. Certainly from a software engineering standpoint, ignoring a known deficiency that allows data corruption when there are (usually) simple, effective solutions to prevent it is negligent in my opinion, and would certainly not be looked upon positively in any software development firms I've worked with.

Second, to your example - I'm still unclear on how the Get<Entity>ForUpdate solves my issue. Let's walk thru my example again:

Step 1 - User 1 reads client Acme with account balance $100

Step 2 - User 2 reads client Acme with account balance $100

Both users now have the same data. No Get<Entity>ForUpdate has been issued to this point, since that does not prevent reads anyway - right?

Step 3 - User 1 subtracts $20 from Acme's account balance and saves

Ok, from what I understand, you are proposing that I do a Get<Entity>ForUpdate in the Save action. So, User 1 now has 2 cached copies right? The original one he read and updated thru the screen, and the one he just read for update. So, now no one else can update it but how do I know that no one else has updated that record since I read it? Do I have to compare the account balance on the record I read for update with the original account balance of the record I'm updating? If they are the same, thats ok but what if they are different? That means someone else has already updated that record and I have stale data. Let's just assume the account balance was the expected $100 and go ahead with update to $80.

Step 4 - User 2 adds $50 to Acme's account balance and saves

Now, I for sure have the issue I just described in step 3. When I do Get<Entity>ForUpdate again, the Get<Entity>ForUpdate record shows $80 but User 2 is still attempting to update to $150 instead of $130, and has no idea he has stale data.

Unless I'm completely missing something, Get<Entity>ForUpdate does nothing to deal with stale data, it only deals with concurrent updates. In my example, step 3 and 4 are not neccessarily happening at the same time - User 2 has a problem anytime after User 1 updates. In order to detect a stale update, I would have to detect what fields I've changed in my record and compare the original values of them with the corresponding fields in the Get<Entity>ForUpdate record, and throw an exception if they are not equal. Now multiply that code x every table in your application....yuck.

Please clarify for me...

14 Sep 2012

1 reply

18 Sep 2012

Show thread

Hide thread

André Vieira

Staff

Greg Berger wrote:

Good to see that there is an active community of helpful people here. I'll respond to each of you below:

Rebecca - thanks for pointing out that component, I'll look into it further. Am REALLY curious as to how the internals of that works...

Rodrigo - your idea is exactly what I am looking for, I gave you a vote and added some comments to your idea.

Andre - a couple comments:

Second, to your example - I'm still unclear on how the Get<Entity>ForUpdate solves my issue. Let's walk thru my example again:

Step 1 - User 1 reads client Acme with account balance $100

Step 2 - User 2 reads client Acme with account balance $100

Both users now have the same data. No Get<Entity>ForUpdate has been issued to this point, since that does not prevent reads anyway - right?

Step 3 - User 1 subtracts $20 from Acme's account balance and saves

Step 4 - User 2 adds $50 to Acme's account balance and saves

Please clarify for me...

Hi Greg,

I agree with you that for 99% of the times you don’t want to have this scenario but I don’t agree that this will lead to an inevitable data corruption.

Absolutely, It applies for the scenario you illustrated and it was for that one that I aimed my example as well.
I think you agree with me that no one manipulates directly the balance of an account. This is the result of a set of operations that happen on that object. In this scenario the GetAccountForUpdate will prevent that two concurrent requests update the account’s balance at the same time, thus preventing the data corruption.

Another option you have is to use the component that Rebecca mentioned that should prevent that more than one user is updating an object at a time, but actually this isn’t optimistic locking also it is pessimistic although not implement with long lasting transactions.

Finally to implement optimistic locking the option you have is to add a Date Time attribute to your entities and validate it when you update but you should also use the Get<Entity>ForUpdate in this scenario to make sure that when you read that value and you update it no one else is also updating it.
You can read more about transactions in the Agile Platform in this post

Hope this helps you out.
Cheers,
André

18 Sep 2012

Greg Berger

Hey Andre - thanks for the reply, although I'm not sure I still have a resolution to my issue. It seems like we may be talking about two different things, so perhaps I should have phrased my initial question like this:

In Outsystems, how can I prevent updates with stale data?

I'm really not interested in locking out other users from reading the data (which is what you have to do to prevent stale data), but I am absolutely interested in knowing whether I have stale data when updating and/or deleting rows. By stale data, I mean that some other process has updated the row(s) I'm trying to update since the time that I read it. And I'm not limiting this question to concurrent updates, as I may have read the data 15 minutes ago but the only other process in contention updated it 10 minutes ago.

As I mentioned, I've seen and used optimistic locking techniques countless times in many organizations over my career. Just to be 100% clear, that generally involves updating a timestamp (or sequence if you prefer) as you mentioned. You end up with update statements that look like this:

Update table

set Somecolumn = @NewValue,

LastUpdateDateTime = @CurrentDateTime

where Id = @RowId

and LastUpdateDateTime = @OriginalDateTime from row when read

Obviously in this case, if someone has updated the row since I read it the timestamp will have changed and I'll get a not found condition on the update and am able to communicate the issue to my users. But, since the platform does not allow me to control the where clause, I cannot include that last update check and therefore do not know I've updated with stale data.

So, from what I've gathered, my choices are to either use the ScreenLock component the Rebecca identified (not sure what side effects that has and am not a fan of locking) or to add a select statement to check for update prior to update, like this:

select *

from table

where Id = @RowId

and LastUpdateDateTime = @OriginalDateTime from row when read

if not found

throw exception

Update table

set Somecolumn = @NewValue,

LastUpdateDateTime = @currentDateTime

where Id = @RowId

Is doable, but leaves open a timegap (however slight) between select and update that allows another process to update the row and make my data stale. Is also inefficient, as it requires an additional query and additional coding everywhere. But may be required as I am not willing to tolerate any preventable exposure to data corruption in my app.

Sure seems to me (in my pea sized brain at least) that this could be addressed easily thru the platform. As Rodrigo suggested above, optional parameters on Update<Entity> , CreateOrUpdate<Entity>, and Delete<Entity> that allow you to specify the optimistic locking field (in my case LastUpdateDateTime) and the value (which could be gotten from the original row read) and that would include the additional Where clause on Update or Delete would seemingly work. If you're not concerned with this issue, don't provide the parameters but would be a clean, simple, efficient way of dealing with this for the rest of us. But perhaps I'm missing something???

Andre - hope that's clearer. I'm still not sure how Get<Entity>ForUpdate really comes into play here. Please explain...

Thanks...Greg

18 Sep 2012

MVP

Imho,

you steps is roughly the same as
1. GetForUpdate(Id)
2. check for date, different dates, throw exception
(2b. audit the action into the audit-table)
3. update record with new stuff

the getforupdate means that the record is not able to be updated by another process because it is locked, for that specific timegap (from 1 -3)

otherwise you just use an advanced query like
Update table
set stuff
where lastupdatetime = originaldate

I understand you want to have it by default in the platform.

1. The problem lies in the fact you want it used as an optional parameter.
the implicit effect is, that any coder is able to corrupt the data anyhow by simply forgetting the optional parameter.
So you need to wrap the update/delete with a custom action anyhow and make that action public, while the entity itself will be private.

2. Or you need to ask Outsystems to implement the rule as a property of the entity, so it will not be optional anymore, but as a mandatory parameter.
It also means that you get more exception from the database, which will be more inefficient than the 2 queries suggested.
You still keep the issue of a developer using advanced queries, so you can bypass the platform-optimistic locking.

Imho the solution of making optimistic locking through the platform will be tough to implement. Not to mention there is always means to bypass it, either with advanced queries, extension or whatever. So they need to be able to guarantee such a thing 100% otherwise nobody trusts it anyhow and will make their own solution anyhow.

Therefor the responsibility should be totally for the developer with a simple solution of making the entities completely private or read-only and wrap the create/update/delete with public actions where you take care of the locking yourself.

19 Sep 2012

Keith Matthews

I would prefer Greg and Rodrigo's idea rather than using the encapsulation pattern as it's less work on the Agile platform. I think it would be easy to implement. Sure everything can be bypassed somehow, thats why we need good developers and good testers.

19 Dec 2014

Lennart Kraak

Champion

Apart from this issue, it's good practise to use the encapsulation as described by J. for the CRUD-actions of entities. It's also convenient to set the CreatedOn/-By and UpdatedOn/-By values there.

For this specific case you can compare the record you want to save ('Balance') with the record in te database, which you get using GetForUpdate. The you can compare the ModifiedOn dates of both records. If these differ, you can let the action fail and provide feedback to the user that the record has been changed in the meantime.

08 Sep 2017

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines