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.
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.
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?
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.
We just uncovered a non-intuitive behavior in our code with SQL Isolation levels. I don’t know if you’re experiencing the same thing but thought I would mention it.
In my code, I did have a few transactions that I explicitly created as Serializable. These were all writes to multiple tables. Our new DBA uncovered read-only transactions that were running as Serializable. What we discovered is that if a transaction changes the iso level for a connection, and that connection is returned to the connection pool, the next thing that picks it up will inherit that iso level.