차단 및 잠금 설명

12분

잠금은 ACID 모델의 원자성, 일관성 및 격리 속성을 유지하는 데 필수적인 관계형 데이터베이스의 핵심 기능입니다. 모든 RDBMS는 데이터베이스 쓰기의 일관성과 격리성을 침해하는 작업을 차단합니다. SQL 프로그래머는 데이터 일관성을 보장하기 위해 올바른 지점에서 트랜잭션을 시작하고 종료해야 합니다. 데이터베이스 엔진은 영향을 받는 테이블의 논리적 일관성을 보호하기 위한 잠금 메커니즘을 제공하는데, 이는 관계형 모델의 기초가 됩니다.

SQL Server에서 한 프로세스가 특정 리소스(행, 페이지, 테이블, 데이터베이스)에 대한 잠금을 보유하고 두 번째 프로세스가 동일한 리소스에서 호환되지 않는 잠금 형식으로 잠금을 얻으려고 시도하는 경우 차단이 발생합니다. 일반적으로 잠금은 짧은 기간 동안 유지되며 잠금을 보유하고 있는 프로세스에서 잠금을 해제하면 차단된 프로세스에서 잠금을 획득하고 해당 트랜잭션을 완료할 수 있습니다.

SQL Server는 트랜잭션을 완료하는 데 필요한 최소한의 데이터만 잠그므로 최대 동시성이 가능합니다. 예를 들어, SQL Server가 단일 행을 잠그면 테이블의 다른 모든 행은 다른 프로세스에서 사용할 수 있으므로 동시 작업이 가능합니다. 그러나 각 잠금에는 메모리 리소스가 필요하므로 한 프로세스의 단일 테이블에 수천 개의 개별 잠금이 있는 것은 비용 효율적이지 않습니다. 동시성과 비용의 균형을 맞추기 위해 SQL Server에서는 잠금 에스컬레이션이라는 기술을 사용합니다. 단일 개체의 5,000개 이상의 행을 단일 문으로 잠가야 하는 경우 SQL Server는 여러 행 잠금을 단일 테이블 잠금으로 에스컬레이션합니다.

잠금은 정상적인 현상이며 하루 종일 자주 발생합니다. 신속하게 해결되지 않는 차단이 발생할 때만 문제가 됩니다. 차단으로 인해 발생하는 성능 문제에는 두 가지 형식이 있습니다.

프로세스는 리소스를 해제하기 전에 장기간 리소스에 대한 잠금을 유지하는데, 이로 인해 다른 프로세스가 차단되고 쿼리 성능과 동시성이 저하됩니다.
프로세스가 리소스 집합에 대한 잠금을 획득한 후 이를 해제하지 않아 문제를 해결하려면 관리자의 개입이 필요합니다.

교착 상태는 한 트랜잭션이 리소스에 대한 잠금을 보유하고 있고, 다른 트랜잭션이 다른 리소스에 대한 잠금을 보유하고 있을 때 발생하는 또 다른 차단 시나리오입니다. 각 트랜잭션은 현재 다른 트랜잭션에 의해 잠겨 있는 리소스에 대한 잠금을 획득하려고 시도하는데, 이로 인해 두 트랜잭션 모두 완료될 수 없어 무한 대기 상태가 됩니다. SQL Server 엔진은 이러한 시나리오를 검색하고 롤백이 필요한 작업량이 가장 적은 트랜잭션을 기준으로 트랜잭션 중 하나를 종료하여 교착 상태를 해결합니다. 종료된 트랜잭션 때문에 교착 상태가 발생하는 것으로 알려져 있습니다. 교착 상태는 기본적으로 사용으로 설정된 system_health 확장 이벤트 세션에 기록됩니다.

트랜잭션의 개념을 이해하는 것이 중요합니다. 자동 커밋은 SQL Server와 Azure SQL Database의 기본 모드입니다. 즉, 다음 문으로 변경된 내용은 데이터베이스의 트랜잭션 로그에 자동으로 기록됩니다.

INSERT INTO DemoTable (A) VALUES (1);

개발자가 애플리케이션 코드를 더 세부적으로 제어할 수 있도록 SQL Server를 사용하여 트랜잭션을 명시적으로 제어할 수도 있습니다. 다음 쿼리는 트랜잭션을 커밋하는 후속 명령이 추가될 때까지 해제되지 않는 DemoTable 테이블의 행에 대한 잠금을 사용합니다.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

다음 쿼리를 작성하는 올바른 방법은 다음과 같습니다.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

이 COMMIT TRANSACTION 명령은 트랜잭션 로그에 대한 변경 내용의 레코드를 명시적으로 커밋합니다. 변경된 데이터는 궁극적으로 데이터 파일에 비동기적으로 적용됩니다. 이러한 트랜잭션은 데이터베이스 엔진에 대한 작업 단위를 나타냅니다. 개발자가 COMMIT TRANSACTION 명령을 실행하는 것을 잊어버리면 트랜잭션은 계속 열려 있고 잠금이 해제되지 않습니다. 이것이 바로 장기 실행 트랜잭션의 주된 이유 중 하나입니다.

데이터베이스 엔진이 데이터베이스의 동시성을 지원하기 위해 사용하는 기타 메커니즘은 행 버전 관리입니다. 데이터베이스에 행 버전 관리 격리 수준이 사용하도록 설정되면 엔진은 TempDB에 수정된 각 행의 버전 관리를 유지 관리합니다. 이는 일반적으로 읽기 쿼리가 데이터베이스에 쓰는 쿼리를 차단하지 못하게 혼합 사용 워크로드에서 사용됩니다.

커밋 또는 롤백을 기다리는 열린 트랜잭션을 모니터링하려면 다음 쿼리를 실행합니다.

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

격리 수준

SQL Server에는 데이터에 대해 보장해야 하는 일관성과 정확성 수준을 정의할 수 있는 여러 격리 수준이 있습니다. 격리 수준을 사용하면 동시성 및 일관성 간의 균형을 확인할 수 있습니다. 격리 수준은 데이터 수정을 방지하기 위해 수행되는 잠금에 영향을 미치지 않습니다. 트랜잭션은 항상 수정 중인 데이터에 대한 배타적 잠금을 가져옵니다. 그러나 격리 수준은 잠금이 유지되는 기간에 영향을 줄 수 있습니다. 격리 수준이 낮을수록 동시에 데이터에 액세스하는 여러 사용자 프로세스의 기능이 향상되지만, 데이터 일관성 위험의 발생 가능성이 늘어납니다. SQL Server의 격리 수준은 다음과 같습니다.

커밋되지 않은 읽기 – 사용 가능한 가장 낮은 격리 수준입니다. 더티 읽기가 허용됩니다. 즉, 한 트랜잭션에서 아직 커밋되지 않은 다른 트랜잭션의 변경 내용을 볼 수 있습니다.
커밋된 읽기 - 첫 번째 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션에서 이전에 읽었지만 수정하지는 않은 데이터를 읽을 수 있습니다. 이 수준에서는 선택 작업을 수행하는 즉시 읽기 잠금도 해제합니다. 이 수준이 기본 SQL Server 수준입니다.
반복 가능한 읽기 – 이 수준은 트랜잭션이 끝날 때까지 선택한 데이터에 대해 획득한 읽기 및 쓰기 잠금을 유지합니다.
직렬화 가능 – 트랜잭션이 격리되는 가장 높은 수준의 격리입니다. 트랜잭션이 끝날 때까지 선택한 데이터에서 획득되는 읽기 잠금 및 쓰기 잠금이 해제되지 않습니다.

SQL Server에는 행 버전 관리를 포함하는 두 가지 격리 수준도 포함되어 있습니다.

커밋된 스냅샷 읽기 – 이 수준에서 읽기 작업은 행 또는 페이지 잠금을 사용하지 않으며 엔진은 쿼리 시작 시 존재했던 데이터의 일관된 스냅샷을 사용하여 각 작업을 표시합니다. 일반적으로 이 수준은 사용자가 OLTP 데이터베이스에 대해 자주 보고하는 쿼리를 실행하는 경우 읽기 작업에서 쓰기 작업을 차단하지 않도록 사용합니다.
스냅샷 – 이 수준은 행 버전 관리에서 트랜잭션 수준 읽기 일관성을 제공합니다. 이 수준은 업데이트 충돌에 취약합니다. 이 수준에서 실행 중인 트랜잭션이 다른 트랜잭션에서 수정한 데이터를 읽는 경우 스냅샷 트랜잭션을 통한 업데이트가 종료되고 롤백됩니다. 이는 커밋된 읽기 스냅샷 격리에는 문제가 되지 않습니다.

다음과 같이 T-SQL SET 명령을 사용하여 각 세션에 대해 격리 수준이 설정됩니다.

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

데이터베이스에서 실행되는 모든 쿼리 또는 특정 사용자가 실행하는 모든 쿼리에 대해 전체 격리 수준을 설정하는 방법은 없습니다. 세션 수준 설정입니다.

차단 문제 모니터링

차단 문제는 산발적으로 발생하기 때문에 이를 식별하는 것은 어려울 수 있습니다. DMV sys.dm_tran_locks는 sys.dm_exec_requests와 조인될 때 각 세션이 보유한 잠금에 대한 정보를 제공합니다. 차단 문제를 모니터링하는 더 효과적인 방법은 확장 이벤트 엔진을 지속적으로 사용하는 것입니다.

일반적으로 차단 문제는 다음 두 범주로 구분됩니다.

불량한 트랜잭션 디자인: 예를 들어, COMMIT TRANSACTION이 없는 트랜잭션은 결코 끝나지 않습니다. 단일 트랜잭션에서 너무 많은 작업을 수행하려고 하거나 연결된 서버 연결을 사용하여 분산 트랜잭션을 수행하면 예측할 수 없는 성능이 발생할 수 있습니다.
스키마 디자인으로 인해 장기 실행 트랜잭션이 발생합니다. 여기에는 인덱스가 없는 열에 대한 업데이트나 잘못 설계된 업데이트 쿼리가 포함되는 경우가 많습니다.

잠금 관련 성능 문제를 모니터링하면 잠금과 관련하여 성능 저하를 빠르게 식별할 수 있습니다.

차단을 모니터링하는 방법에 대한 자세한 내용은 SQL Server 차단 문제 이해 및 해결을 참조하세요.

피드백

이 페이지가 도움이 되었나요?