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

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

  
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?

If you have a look here http://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).
 

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)
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.
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
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.)

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?)


 
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
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
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
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
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.

http://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
joao, does it make a difference with referenced actions vs local actions? (I wouldn't think so).
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.
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
Robert Chanphakeo wrote:
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)
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.
Tiago Gafeira wrote:
Robert Chanphakeo wrote:
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)
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.
 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

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.
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


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.