How to prevent leak of transaction isolation level in pooled connections?

Question

I am using System.Data.SqlClient (4.6.1) in a dot net core 2.2 project. SqlClient maintains a pool of connections, and it has been reported that it leaks transaction isolation level if the same pooled connection is used for the next sql command.

For example, this is explained in this stackoverflow answer: https://stackoverflow.com/a/25606151/1250853

I tried looking for the right way to prevent this leak, but couldn't find a satisfactory solution.

I am thinking to follow this pattern:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.

BEGIN TRANSACTION
SELECT * FROM MyTable;
-- removed complex statements for brevity. there are selects followed by insert.
COMMIT TRANSACTION

-- Set settings back to known defaults.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET XACT_ABORT OFF

Is this a good approach?

You mean, besides the fact that a single statement does not need an explicit transaction, and that a select statement can safely be execution with a `read commited` isolation level? — Zohar Peled, Aug 22 '19 at 06:34
BTW, In most scenarios, you rarely need to set the isolation level. When you do, you can always set it back to default once the statements are executed. Just make sure you reset the isolation level even if the statements fails with an error. — Zohar Peled, Aug 22 '19 at 06:38
Sorry, the single select statement was representing multiple statements. I edited and added a line to explain that. I understand most queries can be written without the need to set isolation levels, and I try to follow that as much as I can :) For resetting on error, do you mean I should add a try catch and reset inside the catch as well? — Turbo, Aug 22 '19 at 09:19
yes, I would probably do something like (pseudo code) `set isolation level; try /* all statements here*/ set isolation level back; end try begin catch /* roll back transaction */ set isolation level back; /* you might want to throw the error to the user here */ end catch` — Zohar Peled, Aug 22 '19 at 09:23
@ZoharPeled I posted an answer based on your comment. Would you mind taking a peek? Thanks! — Turbo, Aug 23 '19 at 20:21
Last 3 lines are not needed. If you are setting isloation level and xact_abort inside a stored proc, it's scoped to the stored proc only and you don't need to catch and turn everything off. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql — JJ_Coder4Hire, Jun 14 '21 at 20:58
Got it. The original question didn't assume it being inside a stored proc. — Turbo, Jun 15 '21 at 22:45

score 1 · Answer 1 · answered Aug 22 '19 at 09:42

I would normally use two separate connection strings that are different (e.g. using tweaked Application Name values). Use one connection string for normal connections, the other for connections where you need serializable.

Since the connection strings are different, they go into separate pools. You may want to adjust other pool related settings if you think this will cause issues (e.g. limit the pool for the serializable to a much lower maximum if using it is rare and to prevent 2x default maximum connections from possibly being created).

score 0 · Answer 2 · answered Aug 23 '19 at 20:29

I recommend never changing the transaction isolation level. If a transaction needs different locking behavior, use appropriate lock hints on selected queries.

The transaction isolation levels are blunt instruments, and often have surprising consequences.

SERIALIZABLE is especially problematic, as few people are prepared to handle the deadlocks it uses to enforce its isolation guarantees.

Also if you only change the transaction isolation level in stored procedure, SQL Server will automatically revert the session's isolation level after the procedure is complete.

Turbo · Accepted Answer · 2021-06-15T22:48:31.787

Answering my own question based on @Zohar Peled's suggestion in the comments:

BEGIN TRY
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.

    BEGIN TRANSACTION
    SELECT * FROM MyTable;
    -- removed complex statements for brevity. there are selects followed by multiple inserts.
    COMMIT TRANSACTION

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SET XACT_ABORT OFF
END TRY
BEGIN CATCH
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET XACT_ABORT OFF;
    THROW;
END CATCH

EDIT: If you are setting isloation level and xact_abort inside a stored proc, it's scoped to the stored proc only and you don't need to catch and turn everything off. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-ver15 .

If you are setting isloation level and xact_abort inside a stored proc, it's scoped to the stored proc only and you don't need to catch and turn everything off. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql — JJ_Coder4Hire, Jun 14 '21 at 20:54

How to prevent leak of transaction isolation level in pooled connections?

3 Answers3