Transaction isolation level for a table in Microsoft SQL Server

Question

It's more a confirmation of what I thought rather than a question. Can one change the isolation level on a table-basis in SQL Server or can it only be defined/changed on a session level as by the statement below?

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

So there is nothing in Microsoft SQL Server like?

ALTER DATABASE SET DEFAULT TRANSACTION ISOLATION LEVEL SERIALIZABLE

or something like?

CREATE TABLE T(A INT) TRANSACTION ISOLATION LEVEL SERIALIZABLE

Related questions and answers which I have read:

well, yes and no. Some table hints are the logical equivalent of certain isolation levels - like the very abused NOLOCK hint. There is a SERIALIZABLE hint. Hints must be applied in every query you want to affect. Your question is very unusual and I suggest you think long and hard about taking this approach further. — SMor
– SMor, Commented Aug 3, 2020 at 2:28

Jason · Accepted Answer · 2020-08-03 00:05:30Z

You can change the isolation level for a connection but not for a table. You can however set the locking behavior on some joins however that can improve locking contention when executing certain queries.

Super Kai - Kazuya Ito · Accepted Answer · 2022-09-16 18:31:54Z

For SQL Server, transaction isolation level can be only set on a session(connection) level which means in between login and logout.

For example, after setting SERIALIZABLE for transaction isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO

Then, log out:

exit

Then, log in again:

sqlcmd /S DESKTOP-HN1U0ME\SQLEXPRESS /E

Then, transaction isolation level is reset to READ COMMITTED:

DBCC USEROPTIONS GO Set Option Value --------------------------- -------------- textsize 4096 language us_english dateformat mdy datefirst 7 lock_timeout -1 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level read committed <- Here

Collectives™ on Stack Overflow

Transaction isolation level for a table in Microsoft SQL Server

2 Answers 2

Comments

Comments

Linked

Hot Network Questions