Isolation level: READ UNCOMMITTED (SQL Server) vs READ COMMITTED (Oracle)

Back to Forums

Robert Chanphakeo

Question

Something interesting happened…

(Simplified example) - Inside DoBootStrap action we have the following workflow

#1 3 create actions are executed, to insert 3 item rows “X,Y,Z"

#2 An [action]^ is executed to retrieve ItemByCode, GetItemByCode(“X”) via aggregate widget

^Action: ItemByCode, this action is located inside the same eSpace, it is not an external web service.

In case #2 for SQL Server, expected result - the record value for Item X is to be returned, however in reality it is not returned - why? (no result returned is expected from Oracle but not SQL Server)

Isolation level: READ UNCOMMITTED (SQL Server), means the database should be able to read the database record without first committing the transaction.

Anyone test this? Is there some sort of delay? why SQL Server can not see record itemX?

07 Apr 2015

Robert Chanphakeo

If you have a look here https://www.outsystems.com/forums/discussion/2830/transactions-in-the-outsystems-platform/ It says SQL SERVER Isolation level is READ UNCOMMITTED

However in reality, our SQL SERVER behaves like the islotation level has been set to "READ COMMITTED"
(i.e you can not do dirty read without committing).

07 May 2015

Robert Chanphakeo

https://msdn.microsoft.com/en-us/library/ms173763.aspx

Furthermore as you can see from Microsoft website it that SQL Server islotation level is READ COMMITTED by default, however outsystems note says SQL SERVER is READ UNCOMMITTED (test shows it doesnt behave as READ UNCOMMITTED)

07 May 2015

2 replies

Last reply 14 Oct 2015

Show thread

Hide thread

Tiago Gafeira

Champion

Robert Chanphakeo wrote:

OutSystems recommends READ UNCOMMITED, but you can set the DB to another isolation level. Have you checked the DB settings?
Some platform functionalities force the isolation level inside a transaction.

11 May 2015

AcacioPN

Staff

Tiago Gafeira wrote:

Robert Chanphakeo wrote:

Hi

READ UNCOMMITTED for SQL Server is not a recommendation - it's mandatory and enforced by the OutSystems Platform.
Full explanation here, but the bit that matters (underline added by me):

SQL Server only supports one reader per transaction, and it has serious limitations on reading and writing in the same transaction. To hide this from the developers, the OutSystems Agile Platform uses multiple transactions in SQL Server. One transaction is dedicated for the writes to ensure commit and rollback atomicity. Additional transactions are opened for the reads, depending on what is needed by the current request. Because of this and because read accesses to uncommitted rows in the database block SQL Server in the default isolation level, transactions in SQL Server have an isolation level of "read uncommitted".

Hope this clarifies it.

Acácio

14 Oct 2015

João Heleno

MVP

Are we talking about the same transaction in #1 and #2?

You must take in account that any query within a transaction sees the transactions's own changes regardless of isolation level.

07 May 2015

João Rosado

Staff

Hi Robert,

Can you share a sample oml where you see it hapenning?
What you said is correct, the aggregate should see the created entries.

I have seen a "similar" behaviour once in a bootstrap.
The action that executed the "GetItemByCode" had cache set. So the bootstrap always crashed.

Like this:

GetItem() <- first usage, returns empy and goes to cache
If no item found
CreateItem()
GetItem() <- gets from cache instead of going to the database....so still empty

Regards,
João Rosado

07 May 2015

1 reply

08 May 2015

Show thread

Hide thread

Robert Chanphakeo

João Rosado wrote:

GetItem() <- first usage, returns empy and goes to cache
If no item found
CreateItem()
GetItem() <- gets from cache instead of going to the database....so still empty

Regards,
João Rosado

What still puzzles me is, in the real app "GetItem" was not first executed, yet it caused issues , how? it shouldnt cache. (I thought outsystems cached the first time the action is executed for period specified. am I wrong?)

08 May 2015

Robert Chanphakeo

Joao

I believe caching may have caused the unexpected behaviour!

Tested similar logic in an espace without caching and outsystems is working as expected (it can do dirty reads without first committing.)

TestMe.oml

08 May 2015

João Rosado

Staff

Hi,

Yes it cahes on the first execution.
Pretty sure you must have it somewhere on you application being called before that point to cause the issue.
Add some audits and check the logs.

Regards,
João Rosado

08 May 2015

Justin James

MVP

I have seen the exact same behavior as well. Very easy to reproduce:

1. Have an Action write data to the DB.
2. Have that Action call a Web Service before ending.
3. Have the Web Service read the changed record from the DB.

Result: Web Service reads the OLD values from the DB.

Despite the documentation, the system clearly is not reading "uncommitted" data in many circumstances.

J.Ja

08 May 2015

João Rosado

Staff

Hi Justin,

Did you test that just now?
Because having the web services make no difference here ... in sql server due to driver limitations with the number of readers over the same transaction even 2 consecutive queries in a flow may use different transactions.

Are you able to create a sample that replicates the behavior?

Regards,
João Rosado

08 May 2015

Justin James

MVP

Joao -

That's been the behavior for a long, long time. If you do not do a CommitTransaction before calling the Web Service, the Web Service will not see the changed data until the current call is finished and it auto-commits. So I know for a fact that it is not reading uncommitted data.

J.Ja

09 May 2015

João Rosado

Staff

Hi Justin,

I tried to replicate what you said but could not do it, everything worked as expected/documented.
I'm attaching my eSpace and a video of me using it and step by step with the debugger to see the flow.

https://screencast.com/t/7j8u4iry156H

Note: if you try my eSpace don't forget to change the web reference url to point to your server.

Regards,
João Rosado

TestReadUncommit.oml

09 May 2015

Robert Chanphakeo

joao, does it make a difference with referenced actions vs local actions? (I wouldn't think so).

09 May 2015

Robert Chanphakeo

I have seen the issue before, but can't figure out the cause of the issue, it's not the action logic.. (I've double checked this)

Maybe older version contained the issue? because I can not reproduce the issue on the latest version.

09 May 2015

Justin James

MVP

Joao -

If that is the current behavior, then something major has changed or been fixed. This was not always the behavior, and ever since I ran into this a few years ago I've been calling CommitTransaction before a Web Service call that needed to see the data.

J.Ja

09 May 2015

Tiago Gafeira

Champion

Hi Acácio,

When you say " enforced by the OutSystems Platform" does that mean that in every transaction/connection the platform overrides the configuration of the database?

Thanks,
Tiago.

14 Oct 2015

1 reply

14 Oct 2015

Show thread

Hide thread

AcacioPN

Staff

Tiago Gafeira wrote:

Hi Acácio,

When you say " enforced by the OutSystems Platform" does that mean that in every transaction/connection the platform overrides the configuration of the database?

Thanks,
Tiago.

When the platform opens a connection to the database, it changes the isolation level to the one appropriate of that stack. You may be able to change it in an individual Advanced Query (we have no way to avoid it in the platform) - but you're pushing for trouble if you forget to set it back...

I've seen my fair share of workarounds to linked server integrations that consist of Advanced Queries with patterns like:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
/* do something */
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

In theory this should work, but it's risky and can cause unexpected locks.

And still on the linked server integrations - OutSystems allows for direct Database Connections since 2008, so I can't really see the point in still using them today.

Cheers,
Acácio

14 Oct 2015

Rob Boek

Are there any thoughts on possible support for READ COMMITTED SNAPSHOT ISOLATION (RCSI) on SQL Server?

This would prevent readers from being blocked by writers, they'd just see the previous uncommitted value for the row. It would also prevent queries from picking up dirty data that may not be committed. RCSI makes SQL Server behave much more like Oracle.

I'm having a few issues on a busy system because of READ UNCOMMITTED:

Online index rebuild may never complete and may cause extensive blocking. This is because queries with READ UNCOMMITTED will jump to the front of the execution chain and I may never get a moment where there are queries not running.
READ UNCOMMITTED causes quite a few issues when working with LOB data, and doesn't perform as well. This is because SQL Server has to do more reads to make sure the LOB pages aren't being changed out from under it.

02 Nov 2015

PJ M

I know this is an old topic, but it's still unsettled and it's still causing issues that we cannot READ UNCOMMITTED. I'm even explicitly using:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Before I execute my read statement and I still get locked waiting on another transaction. In this case I and stating I want to see the dirty data, and in fact, I do want to see the dirty data, but OutSystems won't let me see it. I get Timeout errors on my reads as they are locked waiting on the writes.

How can I see my dirty data?

20 Sep 2018

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

See the full guidelines