Which SQL Transaction Isolation Level is used for Select

ServiceStack.OrmLite SQL Server
1

For a very basic query like this:

using var db = DbFactory.Open();
return db.Exists<vAllGroupAdminsActiveNoDuplicates>(g => g.GroupId == groupId && g.Userid == username);

Which transaction isolation level is used?

I have not set an explicit level anywhere I’m aware of, but I had some issue with the above code doing “dirty read” I think.

Because at the same time the above code is running, another thread is running modifications on that table inside a transaction, however it seems data was read “in the middle of” the transaction. I could not recreate this from two sql query windows, but I’m wondering what the defaults here are for OrmLite.

I’ve read that READ COMMITTED is the default isolation level for SQL Server.

2

Unless you explicitly create a transaction, OrmLite queries don’t create one, so the query isn’t executed in any transaction isolation level since there is no transaction.

But if you did create a transaction with OpenTransaction() then the default isolation for SQL Server is Read committed.

3

There are two SQL’s going on in parallel.

The “other SQL” that is running has a transaction around it. It is manual SQL with CREATE TRANS and COMMIT TRANS in a T-SQL procedure called by OrmLite.This is the SQL that modifies the table, and the changes are being done inside a transaction.

So what I’m wondering about is the isolation level of the Select. I guess it should default to read committed?

4

The default isolation level for SQL Server is Read committed, unless the default isolation level has been changed, or it’s executed within a transaction with a different isolation level, it should be using the default.