SQL Server의 스냅샷 격리
스냅샷 격리는 OLTP 애플리케이션에 대한 동시성을 향상시킵니다.
스냅샷 격리 및 행 버전 관리에 대한 이해
스냅샷 격리를 사용하도록 설정하면 각 트랜잭션에 대해 업데이트된 행 버전을 유지 관리해야 합니다. SQL Server 2019 이전에는 이러한 버전이 tempdb저장되었습니다. SQL Server 2019에는 자체 행 버전 집합이 필요한 새로운 기능인 ADR(가속 데이터베이스 복구)이 도입되었습니다. 따라서 SQL Server 2019를 기준으로 ADR을 사용하지 않는 경우 행 버전은 항상 tempdb 유지됩니다. ADR을 사용하는 경우 스냅샷 격리 및 ADR과 관련된 모든 행 버전은 사용자가 지정하는 파일 그룹의 사용자 데이터베이스에 있는 ADR의 PVS(영구 버전 저장소)에 유지됩니다. 고유한 트랜잭션 시퀀스 번호는 각 트랜잭션을 식별하며 이러한 고유 번호는 각 행 버전에 대해 기록됩니다. 트랜잭션은 트랜잭션의 시퀀스 번호 앞에 시퀀스 번호가 있는 최신 행 버전에서 작동합니다. 트랜잭션이 시작된 후에 만든 최신 행 버전은 트랜잭션에서 무시됩니다.
"스냅샷"이라는 용어는 트랜잭션의 모든 쿼리가 트랜잭션이 시작되는 시점의 데이터베이스 상태에 따라 데이터베이스의 동일한 버전 또는 스냅샷을 볼 수 있다는 사실을 반영합니다. 스냅샷 트랜잭션의 기본 데이터 행 또는 데이터 페이지에서 잠금을 획득하지 않습니다. 따라서 이전에 완료되지 않은 트랜잭션에 의해 차단되지 않고 다른 트랜잭션을 실행할 수 있습니다. 데이터를 수정하는 트랜잭션은 데이터를 읽는 트랜잭션을 차단하지 않으며, 데이터를 읽는 트랜잭션은 일반적으로 SQL Server의 기본 READ COMMITTED 격리 수준 아래와 마찬가지로 데이터를 쓰는 트랜잭션을 차단하지 않습니다. 이 비차단 동작 덕분에 복잡한 트랜잭션에 대한 교착 상태의 가능성이 크게 줄어듭니다.
스냅샷 격리는 낙관적 동시성 모델을 사용합니다. 스냅샷 트랜잭션이 트랜잭션이 시작된 이후 변경된 데이터에 대한 수정 내용을 커밋하려고 하면 트랜잭션이 롤백되고 오류가 발생합니다. 수정할 데이터에 액세스하는 SELECT 문에 UPDLOCK 힌트를 사용하면 이를 방지할 수 있습니다. 자세한 내용은 힌트(Transact-SQL)를 참조하세요.
트랜잭션에 사용되기 전에 ALLOW_SNAPSHOT_ISOLATION ON 데이터베이스 옵션을 설정하여 스냅샷 격리를 사용하도록 설정해야 합니다. 이렇게 하면 임시 데이터베이스(tempdb)에 행 버전을 저장하는 메커니즘이 활성화됩니다. Transact-SQL ALTER DATABASE 문과 함께 사용하는 각 데이터베이스에서 스냅샷 격리를 사용하도록 설정해야 합니다. 이와 관련하여 스냅샷 격리는 구성이 필요하지 않은 READ COMMITTED, REPEATABLE READ, SERIALIZABLE 및 READ UNCOMMITTED의 기존 격리 수준과 다릅니다. 다음 문은 스냅샷 격리를 활성화하고 기본 READ COMMITTED 동작을 SNAPSHOT으로 바꿉니다.
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
READ_COMMITTED_SNAPSHOT ON 옵션을 설정하면 기본 READ COMMITTED 격리 수준에서 버전이 있는 행에 액세스할 수 있습니다. READ_COMMITTED_SNAPSHOT 옵션이 OFF로 설정된 경우 버전이 지정된 행에 액세스하려면 각 세션에 대한 스냅샷 격리 수준을 명시적으로 설정해야 합니다.
격리 수준을 활용한 동시성 관리
Transact-SQL 문이 실행되는 격리 수준에 따라 잠금 및 행 버전 관리 동작이 결정됩니다. 격리 수준에는 연결 수준 범위가 있으며 SET TRANSACTION ISOLATION LEVEL 문과의 연결에 대해 설정되면 연결이 닫혀 있거나 다른 격리 수준이 설정될 때까지 계속 적용됩니다. 연결이 닫혀 풀로 반환될 때, 마지막 SET TRANSACTION ISOLATION LEVEL 문에서 지정한 격리 수준이 유지됩니다. 풀링된 연결을 다시 사용하는 후속 연결은 연결이 풀링될 때 적용된 격리 수준을 사용합니다.
연결 내에서 발급된 개별 쿼리에는 단일 문 또는 트랜잭션에 대한 격리를 수정하는 잠금 힌트가 포함될 수 있지만 연결의 격리 수준에는 영향을 미치지 않습니다. 저장 프로시저 또는 함수에 설정된 격리 수준 또는 잠금 힌트는 이를 호출하는 연결의 격리 수준을 변경하지 않으며 저장 프로시저 또는 함수 호출 기간 동안만 적용됩니다.
SQL-92 표준에 정의된 네 가지 격리 수준은 초기 버전의 SQL Server에서 지원되었습니다.
READ UNCOMMITTED는 다른 트랜잭션에 의해 배치된 잠금을 무시하기 때문에 가장 제한적인 격리 수준입니다. READ UNCOMMITTED에서 실행되는 트랜잭션은 다른 트랜잭션에서 아직 커밋되지 않은 수정된 데이터 값을 읽을 수 있습니다. 이러한 읽기를 "더티" 읽기라고 합니다.
READ COMMITTED는 SQL Server의 기본 격리 수준입니다. 수정되었지만 다른 트랜잭션에서 아직 커밋되지 않은 데이터 값을 읽을 수 없도록 지정함으로써, 더티 읽기를 방지합니다. 다른 트랜잭션은 현재 트랜잭션 내에서 개별 문의 실행 간에 데이터를 수정, 삽입 또는 삭제할 수 있으므로 반복할 수 없는 읽기 또는 "가상" 데이터가 생성됩니다.
REPEATABLE READ는 READ COMMITTED보다 더 제한적인 격리 수준입니다. READ COMMITTED를 포함하며, 현재 트랜잭션이 커밋될 때까지 다른 트랜잭션이 현재 트랜잭션에서 읽은 데이터를 수정하거나 삭제할 수 없도록 지정합니다. 트랜잭션 기간 동안 읽기 데이터에 대한 공유 잠금이 유지되기 때문에, 각 문장이 끝날 때 릴리스되는 것과 달리 READ COMMITTED의 경우보다 동시성이 낮습니다.
SERIALIZABLE은 전체 키 범위를 잠그고 트랜잭션이 완료될 때까지 잠금을 보유하기 때문에 가장 제한적인 격리 수준입니다. REPEATABLE READ를 포함하며 트랜잭션이 완료될 때까지 트랜잭션에서 읽은 범위에 다른 트랜잭션이 새 행을 삽입할 수 없다는 제한을 추가합니다.
자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드참조하세요.
스냅샷 격리 수준 기능 확장
SQL Server는 SNAPSHOT 격리 수준 및 READ COMMITTED의 추가 구현을 도입하여 SQL-92 격리 수준에 대한 확장을 도입했습니다. READ_COMMITTED_SNAPSHOT 격리 수준은 모든 트랜잭션에 대해 READ COMMITTED를 투명하게 대체할 수 있습니다.
SNAPSHOT 격리는 트랜잭션 내에서 읽은 데이터가 다른 동시 트랜잭션의 변경 내용을 반영하지 않도록 지정합니다. 트랜잭션은 트랜잭션이 시작될 때 존재하는 데이터 행 버전을 사용합니다. 데이터를 읽을 때 잠금이 배치되지 않으므로 SNAPSHOT 트랜잭션은 다른 트랜잭션의 데이터 쓰기를 차단하지 않습니다. 데이터를 쓰는 트랜잭션은 스냅샷 트랜잭션이 데이터를 읽는 것을 차단하지 않습니다. ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 설정하여 스냅샷 격리를 사용하도록 설정해야 합니다.
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션은 데이터베이스에서 스냅샷 격리를 사용하는 경우 기본 READ COMMITTED 격리 수준의 동작을 결정합니다. READ_COMMITTED_SNAPSHOT을 명시적으로 지정하지 않으면 모든 암시적 트랜잭션에 READ COMMITTED가 적용됩니다. 이렇게 하면 READ_COMMITTED_SNAPSHOT을 OFF(기본값)로 설정하는 것과 동일한 동작이 생성됩니다. READ_COMMITTED_SNAPSHOT OFF가 적용되면 데이터베이스 엔진은 공유 잠금을 사용하여 기본 격리 수준을 적용합니다. READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하면 데이터베이스 엔진은 잠금을 사용하여 데이터를 보호하는 대신 행 버전 관리 및 스냅샷 격리를 기본값으로 사용합니다.
스냅샷 격리 및 행 버전 관리 작동 방식
SNAPSHOT 격리 수준을 사용하도록 설정하면 행이 업데이트될 때마다 SQL Server 데이터베이스 엔진은 원래 행의 복사본을 tempdb저장하고 행에 트랜잭션 시퀀스 번호를 추가합니다. 발생하는 이벤트 시퀀스는 다음과 같습니다.
새 트랜잭션이 시작되고 트랜잭션 시퀀스 번호가 할당됩니다.
데이터베이스 엔진은 트랜잭션 내의 행을 읽고 시퀀스 번호가 트랜잭션 시퀀스 번호와 가장 가깝고 낮은 tempdb 행 버전을 검색합니다.
데이터베이스 엔진은 스냅샷 트랜잭션이 시작될 때 트랜잭션 시퀀스 번호가 커밋되지 않은 트랜잭션의 트랜잭션 시퀀스 번호 목록에 없는지 확인합니다.
트랜잭션은 트랜잭션 시작 시 현재 tempdb 행의 버전을 읽습니다. 해당 시퀀스 번호 값이 트랜잭션 시퀀스 번호 값보다 높기 때문에 트랜잭션이 시작된 후에 삽입된 새 행은 표시되지 않습니다.
현재 트랜잭션은 트랜잭션이 시작된 후 삭제된 행을 볼 수 있습니다. tempdb 시퀀스 번호 값이 낮은 행 버전이 있기 때문입니다.
스냅샷 격리의 순 효과는 트랜잭션이 기본 테이블에 잠금을 적용하거나 배치하지 않고 트랜잭션 시작 시 존재했던 모든 데이터를 볼 수 있다는 것입니다. 이로 인해 경합이 있는 상황에서 성능이 향상될 수 있습니다.
스냅샷 트랜잭션은 항상 낙관적 동시성 제어를 사용하여 다른 트랜잭션이 행을 업데이트하지 못하도록 하는 잠금을 보류합니다. 스냅샷 트랜잭션이 트랜잭션이 시작된 후 변경된 행에 대한 업데이트를 커밋하려고 하면 트랜잭션이 롤백되고 오류가 발생합니다.
ADO.NET에서 스냅샷 격리 사용하기
ADO.NET에서는 SqlTransaction 클래스가 스냅샷 격리를 지원합니다. 데이터베이스가 스냅샷 격리를 사용하도록 설정되었지만 READ_COMMITTED_SNAPSHOT ON에 대해 구성되지 않은 경우 BeginTransaction 메서드를 호출할 때 IsolationLevel.Snapshot 열거형 값을 사용하여 SqlTransaction 시작해야 합니다. 이 코드 조각은 연결이 열려 있는 SqlConnection 개체라고 가정합니다.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
예시
다음 예제에서는 잠긴 데이터에 액세스하려고 시도하여 다양한 격리 수준이 작동하는 방식을 보여 줍니다. 프로덕션 코드에서는 사용되지 않습니다.
이 코드는 SQL Server의 AdventureWorks 샘플 데이터베이스에 연결하고 TestSnapshot 테이블을 만들고 한 행의 데이터를 삽입합니다. 이 코드는 ALTER DATABASE Transact-SQL 문을 사용하여 데이터베이스에 대한 스냅샷 격리를 설정하지만 READ_COMMITTED_SNAPSHOT 옵션을 설정하지 않으므로 기본 READ COMMITTED 격리 수준 동작이 적용됩니다. 그런 다음 코드는 다음 작업을 수행합니다.
SERIALIZABLE 격리 수준을 사용하여 업데이트 트랜잭션을 시작하는 sqlTransaction1이 시작되지만 완료되지는 않습니다. 이렇게 하면 테이블을 잠글 수 있습니다.
두 번째 연결을 열고 SNAPSHOT 격리 수준을 사용하여 두 번째 트랜잭션을 시작하여 TestSnapshot 테이블의 데이터를 읽습니다. 스냅샷 격리가 사용되므로 이 트랜잭션은 sqlTransaction1이 시작되기 전에 존재했던 데이터를 읽을 수 있습니다.
세 번째 연결을 열고 READ COMMITTED 격리 수준을 사용하여 트랜잭션을 시작하여 테이블의 데이터를 읽으려고 시도합니다. 이 경우 코드는 첫 번째 트랜잭션에서 테이블에 배치된 잠금을 지나서 읽을 수 없고 시간이 초과되므로 데이터를 읽을 수 없습니다. 이러한 격리 수준도 첫 번째 트랜잭션에 배치된 잠금을 지나서 읽을 수 없으므로 REPEATABLE READ 및 SERIALIZABLE 격리 수준을 사용한 경우에도 동일한 결과가 발생합니다.
새로운 네 번째 연결을 열고 READ UNCOMMITTED 격리 수준을 사용하여 트랜잭션을 시작하며, 이는 sqlTransaction1의 커밋되지 않은 값에 대한 더티 읽기를 수행합니다. 첫 번째 트랜잭션이 커밋되지 않은 경우 이 값은 데이터베이스에 실제로 존재하지 않을 수 있습니다.
첫 번째 트랜잭션을 롤백하고 TestSnapshot 테이블을 삭제하고 AdventureWorks 데이터베이스에 대한 스냅샷 격리를 해제하여 정리합니다.
비고
다음 예제에서는 연결 풀링이 해제된 동일한 연결 문자열을 사용합니다. 연결이 풀된 경우 격리 수준을 다시 설정해도 서버의 격리 수준이 다시 설정되지 않습니다. 결과적으로 동일한 풀된 내부 연결을 사용하는 후속 연결은 풀된 연결의 격리 수준으로 설정되기 시작합니다. 연결 풀링을 해제하는 대안은 각 연결에 대해 명시적으로 격리 수준을 설정하는 것입니다.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
// Drop the TestSnapshot table if it exists
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
command1.CommandText = "IF EXISTS "
+ "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
+ "DROP TABLE TestSnapshot";
try
{
command1.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Enable Snapshot isolation
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
command1.ExecuteNonQuery();
// Create a table named TestSnapshot and insert one row of data
command1.CommandText =
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
command1.ExecuteNonQuery();
command1.CommandText =
"INSERT INTO TestSnapshot VALUES (1,1)";
command1.ExecuteNonQuery();
// Begin, but do not complete, a transaction to update the data
// with the Serializable isolation level, which locks the table
// pending the commit or rollback of the update. The original
// value in valueCol was 1, the proposed new value is 22.
SqlTransaction transaction1 =
connection1.BeginTransaction(IsolationLevel.Serializable);
command1.Transaction = transaction1;
command1.CommandText =
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
command1.ExecuteNonQuery();
// Open a second connection to AdventureWorks
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
// Initiate a second transaction to read from TestSnapshot
// using Snapshot isolation. This will read the original
// value of 1 since transaction1 has not yet committed.
SqlCommand command2 = connection2.CreateCommand();
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.Snapshot);
command2.Transaction = transaction2;
command2.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
Console.WriteLine("Expected 1,1 Actual "
+ reader2.GetValue(0)
+ "," + reader2.GetValue(1));
}
transaction2.Commit();
}
// Open a third connection to AdventureWorks and
// initiate a third transaction to read from TestSnapshot
// using ReadCommitted isolation level. This transaction
// will not be able to view the data because of
// the locks placed on the table in transaction1
// and will time out after 4 seconds.
// You would see the same behavior with the
// RepeatableRead or Serializable isolation levels.
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
SqlTransaction transaction3 =
connection3.BeginTransaction(IsolationLevel.ReadCommitted);
command3.Transaction = transaction3;
command3.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
command3.CommandTimeout = 4;
try
{
SqlDataReader sqldatareader3 = command3.ExecuteReader();
while (sqldatareader3.Read())
{
Console.WriteLine("You should never hit this.");
}
transaction3.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Expected timeout expired exception: "
+ ex.Message);
transaction3.Rollback();
}
}
// Open a fourth connection to AdventureWorks and
// initiate a fourth transaction to read from TestSnapshot
// using the ReadUncommitted isolation level. ReadUncommitted
// will not hit the table lock, and will allow a dirty read
// of the proposed new value 22 for valueCol. If the first
// transaction rolls back, this value will never actually have
// existed in the database.
using (SqlConnection connection4 = new(connectionString))
{
connection4.Open();
SqlCommand command4 = connection4.CreateCommand();
SqlTransaction transaction4 =
connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
command4.Transaction = transaction4;
command4.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
SqlDataReader reader4 = command4.ExecuteReader();
while (reader4.Read())
{
Console.WriteLine("Expected 1,22 Actual "
+ reader4.GetValue(0)
+ "," + reader4.GetValue(1));
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(connectionString))
{
connection5.Open();
SqlCommand command5 = connection5.CreateCommand();
command5.CommandText = "DROP TABLE TestSnapshot";
SqlCommand command6 = connection5.CreateCommand();
command6.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command5.ExecuteNonQuery();
command6.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.WriteLine("Done!");
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Drop the TestSnapshot table if it exists
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = "IF EXISTS " & _
"(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
& "DROP TABLE TestSnapshot"
Try
command1.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Enable SNAPSHOT isolation
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
command1.ExecuteNonQuery()
' Create a table named TestSnapshot and insert one row of data
command1.CommandText = _
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
command1.ExecuteNonQuery()
command1.CommandText = _
"INSERT INTO TestSnapshot VALUES (1,1)"
command1.ExecuteNonQuery()
' Begin, but do not complete, a transaction to update the data
' with the Serializable isolation level, which locks the table
' pending the commit or rollback of the update. The original
' value in valueCol was 1, the proposed new value is 22.
Dim transaction1 As SqlTransaction = _
connection1.BeginTransaction(IsolationLevel.Serializable)
command1.Transaction = transaction1
command1.CommandText = _
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
command1.ExecuteNonQuery()
' Open a second connection to AdventureWorks
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
' Initiate a second transaction to read from TestSnapshot
' using Snapshot isolation. This will read the original
' value of 1 since transaction1 has not yet committed.
Dim command2 As SqlCommand = connection2.CreateCommand()
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.Snapshot)
command2.Transaction = transaction2
command2.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Dim reader2 As SqlDataReader = _
command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
transaction2.Commit()
End Using
' Open a third connection to AdventureWorks and
' initiate a third transaction to read from TestSnapshot
' using the ReadCommitted isolation level. This transaction
' will not be able to view the data because of
' the locks placed on the table in transaction1
' and will time out after 4 seconds.
' You would see the same behavior with the
' RepeatableRead or Serializable isolation levels.
Dim connection3 As SqlConnection = New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
Dim transaction3 As SqlTransaction = _
connection3.BeginTransaction(IsolationLevel.ReadCommitted)
command3.Transaction = transaction3
command3.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
command3.CommandTimeout = 4
Try
Dim reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
transaction3.Commit()
Catch ex As Exception
Console.WriteLine("Expected timeout expired exception: " _
& ex.Message)
transaction3.Rollback()
End Try
End Using
' Open a fourth connection to AdventureWorks and
' initiate a fourth transaction to read from TestSnapshot
' using the ReadUncommitted isolation level. ReadUncommitted
' will not hit the table lock, and will allow a dirty read
' of the proposed new value 22. If the first transaction
' transaction rolls back, this value will never actually have
' existed in the database.
Dim connection4 As SqlConnection = New SqlConnection(connectionString)
Using connection4
connection4.Open()
Dim command4 As SqlCommand = connection4.CreateCommand()
Dim transaction4 As SqlTransaction = _
connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
command4.Transaction = transaction4
command4.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Dim reader4 As SqlDataReader = _
command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
transaction4.Commit()
' Rollback transaction1
transaction1.Rollback()
End Using
End Using
' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
connection5.Open()
Dim command5 As SqlCommand = connection5.CreateCommand()
command5.CommandText = "DROP TABLE TestSnapshot"
Dim command6 As SqlCommand = connection5.CreateCommand()
command6.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command5.ExecuteNonQuery()
command6.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
예시
다음 예제에서는 데이터를 수정할 때 스냅샷 격리의 동작을 보여 줍니다. 코드는 다음 작업을 수행합니다.
AdventureWorks 샘플 데이터베이스에 연결하고 SNAPSHOT 격리를 사용하도록 설정합니다.
TestSnapshotUpdate 테이블을 만들고 샘플 데이터의 세 행을 삽입합니다.
SNAPSHOT 격리를 사용하여 sqlTransaction1을 시작하지만, 이를 완료하지 않습니다. 트랜잭션에서 세 개의 데이터 행이 선택됩니다.
AdventureWorks 두 번째 SqlConnection 만들고 sqlTransaction1에서 선택한 행 중 하나의 값을 업데이트하는 READ COMMITTED 격리 수준을 사용하여 두 번째 트랜잭션을 만듭니다.
sqlTransaction2를 커밋합니다.
sqlTransaction1로 돌아와 sqlTransaction1이 이미 커밋한 것과 동일한 행을 업데이트하려고 시도합니다. 오류 3960이 발생하고 sqlTransaction1이 자동으로 롤백됩니다. SqlException.Number 및 SqlException.Message 콘솔 창에 표시됩니다.
정리 코드를 실행하여 AdventureWorks 스냅샷 격리를 해제하고 TestSnapshotUpdate 테이블을 삭제합니다.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
// Enable Snapshot isolation in AdventureWorks
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
try
{
command1.ExecuteNonQuery();
Console.WriteLine(
"Snapshot Isolation turned on in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"ALLOW_SNAPSHOT_ISOLATION ON failed: {ex.Message}");
}
// Create a table
command1.CommandText =
"IF EXISTS "
+ "(SELECT * FROM sys.tables "
+ "WHERE name=N'TestSnapshotUpdate')"
+ " DROP TABLE TestSnapshotUpdate";
command1.ExecuteNonQuery();
command1.CommandText =
"CREATE TABLE TestSnapshotUpdate "
+ "(ID int primary key, CharCol nvarchar(100));";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("TestSnapshotUpdate table created.");
}
catch (Exception ex)
{
Console.WriteLine($"CREATE TABLE failed: {ex.Message}");
}
// Insert some data
command1.CommandText =
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
+ "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
+ "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("Data inserted TestSnapshotUpdate table.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Begin, but do not complete, a transaction
// using the Snapshot isolation level.
SqlTransaction transaction1 = default!;
try
{
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
command1.CommandText =
"SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
Console.WriteLine("Snapshot transaction1 started.");
// Open a second Connection/Transaction to update data
// using ReadCommitted. This transaction should succeed.
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
SqlCommand command2 = connection2.CreateCommand();
command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection2' WHERE ID=1";
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.ReadCommitted);
command2.Transaction = transaction2;
try
{
command2.ExecuteNonQuery();
transaction2.Commit();
Console.WriteLine(
"transaction2 has modified data and committed.");
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
transaction2.Rollback();
}
finally
{
transaction2.Dispose();
}
}
// Now try to update a row in Connection1/Transaction1.
// This transaction should fail because Transaction2
// succeeded in modifying the data.
command1.CommandText =
"UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection1' WHERE ID=1";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
transaction1.Commit();
Console.WriteLine("You should never see this.");
}
catch (SqlException ex)
{
Console.WriteLine("Expected failure for transaction1:");
Console.WriteLine($" {ex.Number}: {ex.Message}");
}
finally
{
transaction1.Dispose();
}
}
// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
command3.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command3.ExecuteNonQuery();
Console.WriteLine(
"CLEANUP: Snapshot isolation turned off in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Enable Snapshot isolation in AdventureWorks
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
Try
command1.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot Isolation turned on in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
End Try
' Create a table
command1.CommandText = _
"IF EXISTS (SELECT * FROM sys.databases " _
& "WHERE name=N'TestSnapshotUpdate') " _
& "DROP TABLE TestSnapshotUpdate"
command1.ExecuteNonQuery()
command1.CommandText = _
"CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
& "CharCol nvarchar(100));"
Try
command1.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table created.")
Catch ex As Exception
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
End Try
' Insert some data
command1.CommandText = _
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
& "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
& "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
Try
command1.ExecuteNonQuery()
Console.WriteLine("Data inserted TestSnapshotUpdate table.")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Begin, but do not complete, a transaction
' using the Snapshot isolation level
Dim transaction1 As SqlTransaction = Nothing
Try
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
command1.CommandText = _
"SELECT * FROM TestSnapshotUpdate WHERE ID " _
& "BETWEEN 1 AND 3"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
Console.WriteLine("Snapshot transaction1 started.")
' Open a second Connection/Transaction to update data
' using ReadCommitted. This transaction should succeed.
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
Dim command2 As SqlCommand = connection2.CreateCommand()
command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
& "CharCol=N'New value from Connection2' WHERE ID=1"
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.ReadCommitted)
command2.Transaction = transaction2
Try
command2.ExecuteNonQuery()
transaction2.Commit()
Console.WriteLine( _
"transaction2 has modified data and committed.")
Catch ex As SqlException
Console.WriteLine(ex.Message)
transaction2.Rollback()
Finally
transaction2.Dispose()
End Try
End Using
' Now try to update a row in Connection1/Transaction1.
' This transaction should fail because Transaction2
' succeeded in modifying the data.
command1.CommandText = _
"UPDATE TestSnapshotUpdate SET CharCol=" _
& "N'New value from Connection1' WHERE ID=1"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
transaction1.Commit()
Console.WriteLine("You should never see this.")
Catch ex As SqlException
Console.WriteLine("Expected failure for transaction1:")
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message)
Finally
transaction1.Dispose()
End Try
End Using
' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
command3.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command3.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot isolation turned off in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
command3.CommandText = "DROP TABLE TestSnapshotUpdate"
Try
command3.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table deleted.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
End Using
스냅샷 격리와 함께 잠금 힌트 사용
이전 예제에서 첫 번째 트랜잭션은 데이터를 선택하고 두 번째 트랜잭션은 첫 번째 트랜잭션이 완료되기 전에 데이터를 업데이트하므로 첫 번째 트랜잭션이 동일한 행을 업데이트하려고 할 때 업데이트 충돌이 발생합니다. 트랜잭션 시작 시 잠금 힌트를 제공하여 장기 실행 스냅샷 트랜잭션에서 업데이트 충돌 가능성을 줄일 수 있습니다. 다음 SELECT 문은 UPDLOCK 힌트를 사용하여 선택한 행을 잠급니다.
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
UPDLOCK 잠금 힌트를 사용하면 첫 번째 트랜잭션이 완료되기 전에 행을 업데이트하려는 행이 차단됩니다. 이렇게 하면 선택한 행이 트랜잭션의 뒷부분에서 업데이트될 때 충돌이 발생하지 않습니다. 자세한 내용은 힌트(Transact-SQL)를 참조하세요.
애플리케이션에 충돌이 많은 경우 스냅샷 격리가 최선의 선택이 아닐 수 있습니다. 힌트는 실제로 필요한 경우에만 사용해야 합니다. 애플리케이션이 지속적으로 잠금 힌트에 의존하도록 설계해서는 안 됩니다.