Sql widget atomicity

Sql widget atomicity

  

Hi there,

If in sql widget there are two statements:

1. Update

2. Select

Is it guaranteed that both are executed atomically?

Thank you, 

Regards,

I

Hi "I",

What do you mean by both are executed atomically?... the entire request is a single transaction, so they would be executed in the context of the same transaction.

Given the two statements are in the same SQL, they will be sent to the DB engine in a single call.

You can learn a bit more about the way the platform handles transactions here.

hi gorge,

 I mean the potential of dirty read. I use mysql and try to update a table that has one column, and after increment that column value, i want to select it.

regards,

i

The common approach is using the GetForUpdate entity action, which retrieves and locks the row until the transaction is finished. The pattern would be: GetForUpdate > change record in memory > Update. You'd still have the record in memory after the update, so without more information, it seems to fit your use case.

You may also want to read this.

hi Paulo,

Thank you for the response, but i use mysql, not sql server (as in the link "this"). What do you mean by "change record in memory"?

regards,

I


If you're going for SQL engine mechanisms, then you should investigate what is the equivalent in mysql (which I don't know).

But if you can avoid the low level SQL and do it using the mentioned method above (using GetForUpdate), your code will probably be easier to understand and maintain.

By "change record in memory" I meant using an Assign node to change the values you need to have updated.

hi Paulo,

Suppose a web service is doing GetForUpdate, and before do the update, another web service is reaching the GerForUpdate, what is happening to the second web service? Is it waiting or throw an error?

Second, when is the lock for the update is released? Is it when we call entity action (update)- i am wondering as it is not committed yet- or else?

Thank you,

regards,

I

As stated in the documentation above, "the GetForUpdate action locks the record in the database until the transaction is finished, preventing other processes from accessing this record".

So, the 2nd transaction accessing the same row waits for the lock from the 1st transaction to be released - which happens implicitly when the transaction / request ends (web service, screen action, etc). Optionally, you can use the built-in CommitTransaction action to end the transaction sooner, otherwise it's committed when the request ends.

So, it is safe just to use this pattern in mysql?

i create an action in Outsystems:

GetForUpdate > change record in memory > Update > select the last update

and then webservice created in Outsystems call that action.

An external system will call that web service many times... first call will lock that record, and the rest calls will be waiting for releasing the lock -waiting for getting that lock..

regards,

I

Solution

Seems ok, yes. But you don't need the last select ("select the last update"), since at that point you'll have the record in memory already (so no need to retrieve it again). 

Solution

Thank you Sir,

You rock!

regards,

I