23
Views
1
Comments
 How to deal with read uncommitted in SQL Server

Hi.

I'm building a system using Azure SQL Server.

This system shares a DB with applications other than Outsystems, Then this application reads the DB frequent.

Outsystems applications occasionally write to the DB.

According to the official reference, SQL Server is Read Uncommitted.

Therefore, I'm considering whether to implement measures such as optimistic locking.

If you were considering a similar issue, please tell me how you addressed it.

https://success.outsystems.com/ja-jp/Documentation/11/Reference/OutSystems_Language/Data/Database_Reference/Handling_Transactions

mvp_badge
MVP
Rank: #45

Hi Yori,

I've developed and maintained OutSystems applications that shared a common database model with applications in other development stacks. Most of the concerns could be boiled down to two points:

 - the OutSystems application had to react to data being created or changed by external applications. We usually solved this by either creating an Entity and reacting to new data with BPTs, or if immediate access/updates weren't necessary, periodically checking the data with Timers;

  - managing data conflicts and simultaneous updates. This is very application/business specific, but a reasonable way to make it database agnostic would be to ensure that every application commits as soon as it completes data operations, and that data is checked before it is updated - so if a specific record has been changed by an external system and immediately commited, an OutSystems application can query the database and compare the record with what is about to be updated. If a change is detected, then that means another user has gotten there first.

There's probably a lot I haven't covered. Do you have any specific concerns or use cases you might be wondering about?