[MSSQL] TRANSACTION ISOLATION LEVEL

Recently, while analyzing errors, I studied transaction isolation levels in more detail, and I summarized the contents.

First, what is the transaction isolation level?

This refers to determining whether the data referenced by a specific transaction can be viewed by other transactions when multiple transactions are processed.

In other words, it plays a role in determining whether other transactions can access and view the data that is being preempted first.

why do i need this

The isolation level is necessary to maintain data consistency and enable stable data recovery.

To put it simply, let's say we want to add 'apple' to the column called 'Fruit'. Here, if someone wants to know what kind of 'fruit' there are, they can decide whether to show data containing the value 'apple' or only the data up to that point.

MSSQL의 트랜잭션 격리수준

-- Syntax for SQL Server and Azure SQL Database
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}

In MSSQL, isolation levels are set as follows. Let's look at the meaning of each isolation level.

// The further you go, the lower the concurrency and the higher the isolation. READ UNCOMMITTED

Isolation level	Dirty Read	Non-Repeatable Read	Phantom read
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

READ UNCOMMITTED

This refers to the level at which contents that have not been committed by other transactions can be read. This can be viewed at the same level as setting NOLOCK in all SELECT statements. However, since uncommitted content is also read, consistency is low in that the accurately UPDATE value is not retrieved, and DIRTY READ occurs.

READ COMMITED

DIRTY READ can be prevented because only committed content is read. Basically, due to the isolation level applied in RDB, it is not possible to view details that have been updated and not committed by other transactions. Therefore, when performing a SELECT in another session, the record value of the existing UNDO area that has been committed is brought in.

However, in this case, even if the same query is executed, different values ​​are returned depending on whether other transactions have committed, which violates consistency.

REPEATABLE READ

Because it operates as a single snapshot version, it maintains the same value, unlike READ COMMITTED, where the value changes depending on whether or not the SELECT query has been committed when the SELECT query is re-executed. As the name suggests, it guarantees an isolation level that returns the same value even if read repeatedly.

Instead, UPDATE transactions cannot be performed until all READs are completed. Conversely, not being able to READ if UPDATE is in progress is similar to READ COMMITTED LEVEL.

SERIALIZABLE

It is similar to REPEATABLE READ, but all SELECT queries are automatically changed to SELECT ... FOR SHARE.

Because you won’t know unless you try

CREATE TABLE [dbo].[TestTab](
[idx] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Height] [int] NOT NULL,
[Age] [int] NOT NULL,
CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED
(
[idx] ASC
) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [TestTab] VALUES('하나',150,10)
INSERT INTO [TestTab] VALUES('두울',166,20)
INSERT INTO [TestTab] VALUES('세엣',163,30)
INSERT INTO [TestTab] VALUES('네엣',180,35)

Find the current Isolation Level

Here, the transaction_isolation_level value is given as a number, and its meaning is as shown in the table below.

SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

0	Unspecified
1	ReadUncommitted
2	ReadCommitted
3	Repeatable
4	Serializable
5	Snapshot

READ UNCOMMITTED

In Session 1, which changed the age of 'Hana' from 10 to 70, no commit was made with the READ UNCOMMITTED isolation level, but when viewed in Session 2, you could see that the age had changed to 70.

READ COMMITTED

When running with two windows open, you can see that the results of running up to No. 2 are returned as SELECT results without reflecting the change in the age of the person named 'Neet' in No. 1.

Afterwards, if you commit the left transaction and SELECT again from the right transaction, you can see that the result value has changed.

REPEATABLE READ

When I changed the age in the same way and executed the transaction on the right, I could see that the query was still being executed without being viewed. You can see that the SELECT operation is locked until the left commit is made.

Check whether it is locked or not

EXEC SP_LOCK
EXEC SP_WHO2

SERIALIZABLE

In the case of SERIALIZAVBLE, we did not capture it, but when we ran it in the same way as above, we were able to confirm that it was locked and could not be viewed.