search

bill-ramos-rmoswi / babelfish_postgresql_towel

Bill Ramos's knowledge base for tips, tricks, and best practices for migrating SQL Server solutions to Babelfish for PostgreSQL and the open-source WiltonDB for Windows
Apache License 2.0
1 stars 1 forks source link

Read Uncommited transaction Not working in EntityFramework #12

Closed JayakumarEMIS closed 3 months ago

JayakumarEMIS commented 4 months ago

Hi @bill-ramos-rmoswi, I am getting the below error in my application code, in this code I want to achieve dirty read for my some of the queries but it was breaking here and it throws message 'RESETCONSKIPTRAN is not supported'.

image

image

staticlibs commented 4 months ago

@JayakumarEMIS , I have not verified this, but I believe it is not possible to achieve dirty reads in Postgres, citing from Transaction Isolation:

PostgreSQL's Read Uncommitted mode behaves like Read Committed.

See more details in Aurora Babelfish docs (note that REPEATABLE READ and SERIALIZABLE are not supported in WiltonDB).

JayakumarEMIS commented 4 months ago

Hi @staticlibs I checked read un committed in babelfish it working different from t-sql, in t-sql by read committed by default it will block the result if any transaction is ongoing so we set read uncommitted without blocking the table but cause dirty read, in other hand babelfish read committed and uncommitted behaves same, it won't block the result, instead of returned last committed transaction, in our case will this behavior is fine for me, but avoiding the exception in babelfish we modified the scope level required to suppress, so it will execute as non transactional work, for just select query result it won't make any impact, kindly share your opinion on this

using (var scope = new TransactionScope( TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) { List toReturn = query.ToList(); scope.Complete(); return toReturn; }

staticlibs commented 4 months ago

@JayakumarEMIS , if dirty reads are not required, then I think that default Postgres behaviour (MVCC, so less blocking) should work for SELECT queries without any additional transactional setup.