트랜잭션
트랜잭션은 SQL Server가 변경 사항을 COMMIT하기 전에 얼마나 많은 작업을 수행할 것인지를 다룬다. COMMIT TRANSACTION 문이 실행되기 전에는 다중 문 트랜잭션이 데이터베이스의 내용을 영구적으로 변경하지 않는다. 또한, 다중 문 트랜잭션은 ROLLBACK TRANSACTION문이 실행되었을 경우에 변경 사항을 원래 상태로 되돌릴 수 있다.
네개의 일괄처리를 포함하는 한 개의 트랜잭션
BEGIN TRAN
INSERT authors VALUES(etc.)
Go
SELECT * FROM authors
Go
UPDATE publishers SET pub_id = (etc.)
GO
COMMIT TRAN
GO
두개의 트랜잭션을 포함하는 한 개의 일괄처리
BEGIN TRAN
INSERT authors VALUES(etc.)
SELECT * FROM authors
COMMIT TRAN
BEGIN TRAN
UPDATE publishers SET pub_id = (etc.)
INSERT publishers VALUES(etc.)
COMMIT TRAN
Go
BEGIN TRAN과 COMMIT TRAN문 사이에 있는 명령어들은 하나의 단위로 수행된다.
BEGIN TRAN
INSERT authors VALUES(etc.)
SELECT * FROM authors
UPDATE publishers SET pub_id = (etc.)
COMMIT TRAN
Go
SQL Server에서 트랜잭션을 사용하면 트랜잭션 내에 있는 모든 명령어들은 항상 한 개의 작업 단위로 수행된다. 이런한 트랜잭션은 ACID(원자성, 일관성, 격리성, 영속성) 속성을 갖는다.
명시적 트랜잭션과 암시적 트랜잭션
기본적으로 SQL Server는 각 트랜잭션들을 독립적으로 취급하고 즉시 커밋한다.(트랜잭션이 개별적으로 전달되었든지 일괄 처리의 일부로 전달되었든지 간에 상관없다.) 논리적으로 커밋될 수 있는 문은 데이터베이스에서 무언가를 변경하는문뿐이다. 따라서, SELECT문을 커밋한다고 말하는 것은 실제로 아무런 의미도 없다. 우리가 트랜잭션에 대해 말할 때는 일반적으로 데이터 변경문(INSERT, UPDATE, DELETE)에 대해 말하는 것이다. 따라서 개별적으로 수행되는 데이터 변경문들은 그 자체로서 “암시적 트랜잭션”이다. 아무리 많은 행들이 변경되더라도, 이 행들은 모두가 변경되거나 아무 것도 변경되지 않을 것이다. 백만 행으로 된 테이블이 업데이트 되고 있는 동안 시스템 고장이 일어난다면, SQL Server가 복구할 때 이미 디스크에 기록된 업데이트 부분을 롤백할 것이고, 데이터베이스의 상태는 업데이트가 일어나지 않았을 때와 동일하게 유지될 것이다.
만일 트랜잭션에 여러 문들을 포함시키고 싶다면, BEGIN TRANSACTION과 COMMIT TRANSACTION 또는 ROLLBACK TRANSACTION 문 사이에 문들을 넣어야 한다. 이를 명시적 트랜잭션이라 한다.
SET IMPLICIT_TRANSACTION ON을 사용함으로써 또는 sp_configure ‘user options’,2를 사용하여 옵션을 전역을 설정함으로써 SQL Server가 암시적으로 트랜잭션을 시작하도록 설정할 수도 있다. 암시적 트랜잭션이 활성화되어 있다면 모든 문들은 트랜잭션의 일부로 간주되고 명시적 COMMIT TRAN이 실행될 때까지 어떤 작업도 커밋되지 않는다. 이것은 일괄처리에 있는 모든 문들이 실행되었을 때에도 마찬가지이다. 다음 일괄 처리에서 COMMIT TRANSACTION을 실행시켜야 한다.
트랜잭션에서 에러 검사하기
개발자들이 SQL Sever에서 저지를 수 있는 가장 일반적인 실수들 중의 하나는 트랜잭션 내에서 일어나는 에러로 인해 트랜잭션이 자동으로 롤백될 것이라고 생각하는 것이다. 우리가 항상 트랜잭션이 원자성을 갖는다고 들어왔기 때문에 이런 오해가 있을 수 있다. 그러나 우리가 트랜잭션 롤백을 일으키는 실패에 대해 말할 때 일반적으로 시스템 고장을 말하고 있는 것이다. 시스템 전체의 기능이 멈추면 SQL Server가 재시작되어야 하고, 원자성을 갖는 복구 과정이 완료되지 않은 트랜잭션들을 롤백시킬 것이다. 그러나 트랜잭션에서 발생한 많은 에러들에 있어서 현재 문만이 중지될 것이고, 트랜잭션에 있는 나머지 문들은 계속해서 처리되고 커밋될 수 있다.
USE Pubs
BEGIN TRAN
UPDATE authors
SET state = ‘FL’
WHERE state = ‘KS’
UPDATE jobs
SET min_lvl = min_lvl -10
COMMIT TRAN
SELECT * FROM authors
WHERE state = ‘FL’
트랜잭션은 두개의 UPDATE문을 포함하고 있다. 첫번째 UPDATE문은 한 행에 있는 state값을 ‘FL’로 바꾼다. 두번째 UPDATE문은 jobs테이블에 있는 모든 min_lvl 값들에서 10을 빼려고 시도하지만, min_lvl이 10보다 크거나 같아야 한다는 check 조건이 min_lvl칼럼에 있다. Jobs 테이블에서 min_lvl에서 10을 빼면 제약 조건이 위배되는 컬럼이 있다. 이 한 행 때문에 문 전체가 중지되고 jobs에 있는 어떤 행도 업데이트되지 않지만, 트랜잭션은 롤백되지 않는다. COMMIT이후에 SELECT 문은 state값이 ‘FL’인 저자가 있는 것을 보여준다.
다중문 트랜잭션은 각 문들 이후에 @@ERROR 값을 선택함으로써 에러 발생 여부를 검사해야 한다. 치명적이지 않은 에러가 발생했고 여러분이 이것에 대해 아무런 조치도 하지 않는다면 계속해서 다음 문이 처리된다. 치명적인 에러만이 일괄처리를 자동으로 중지하게 하고 트랜잭션이 롤백되게 만든다.
USE Pubs
BEGIN TRAN
UPDATE authors
SET state = ‘FL’
WHERE state = ‘KS’
IF @@ERROR <> 0 BEGIN
ROLLBACK TRAN
GOTO ON_ERROR
END
UPDATE jobs
SET min_lvl = min_lvl - 10
IF @@ERROR <> 0 BEGIN
ROLLBACK TRAN
GOTO ON_ERROR
END
COMMIT TRAN
ON_ERROR:
SELECT * FROM authors WHERE state = ‘FL’
이 일괄 처리에서 우리는 각 데이터 변경 문들 이후에 에러의 발생 여부를 검사했다. 그러나 에러가 있을 때 단순히 트랜잭션을 롤백시키는 것은 효과적이지 못하다. 왜냐하면, 대부분의 경우에 ROLLBACK TRAN은 문 실행에서 제어 흐름을 바꾸자 않기 때문이다. ROLLBACK TRAN은 현재 트랜잭션에서 완료된 변경 사항들을 되돌리고 트랜잭션 로그에 적절한 항목을 만들 것이다. 그러나 프로그램 흐름은 계속해서 다음 라인으로 이동할 것이다. 이 경우에 GOTO 키워드가 사용되지 않는다면 첫번째 UPDATE에서 에러가 발생했을 때 여전히 계속해서 두번째 UPDATE로 이동할 것이다. 두번째 UPDATE에서 에러가 발생해도 여전히 계속해서 COMMIT TRAN을 실행시키려고 할 것이다. 이 시점에 이미 ROLLBACK TRAN을 실행시켰을 것이기 때문에 더 이상 열려진 트랜잭션이 없을 것이고 COMMIT TRAN 문에서 에러가 발생할 것이다.
구문 에러가 있으면 실행이 시작하기도 전에 항상 일괄 처리 전체가 중지되고, 존재하지 않는 개체에 대한 참조가 있으면 일괄 처리가 해당 시점에서 실행을 멈춘다. 그러나 일괄 처리가 중지되었다고 해서 트랜잭션이 롤백된 것은 아니다. 트랜잭션에 여러 일괄 처리들이 들어 있을 수 있다는 것을 보았다. 에러가 발생했는데 트랜잭션이 롤백되었는지의 여부를 정확히 알수 없다면 시스템 함수 @@TRANCOUNT를 사용하여 트랜잭션의 상태를 검사할 수 있다. @@TRANCOUNT 값이 0보다 크면 활성 트랜잭션이 있는 것이고 여러분이 ROLLBACK TRAN을 실행시킬 수 있다.
동적으로 생성되고 EXECUTE(‘string’)을 사용하여 실행된 일괄 처리의 구문 에러는 실행 시까지 감지되지 않을 것이다. 이 EXECUTE(‘string’)를 일괄 처리들을 중첩시키는 방법으로 생각할 수 있다. 문자열이 여러 문들로 구성되어 있다면 구문이나 개체 참조 에러가 내부 일괄 처리만을 중지시킬 것이다. 외부 일괄 처리는 계속해서 다음 문으로 이동한다.
DECLARE @tablename sysname
SET @tablename = ‘authors’
EXECUTE (‘USE pbus SELECT * FROM ‘ + @tablename + ‘ PRINT “Innser Batch Done” ‘)
PRINT ‘Outer Batch Done’
이 일괄처리의 분석 시에 에러가 감지되지 않을 것이다. 따라서 SQL Server는 이 일괄처리를 실행시키려고 시도할 것이다. DECLARE와 SET은 성공적으로 실행되겠지만, EXECUTE 내에 중첩된 일괄 처리는 문제를 일으킬 것이다. 테이블 이름 authors가 pubs 데이터베이스에 존재하지 않기 때문에 SELECT문에 에러가 있다. 안쪽 일괄 처리에 있는 세번째 문(Print)이 실행되지 않을 것이다. 그러나 바깥쪽 일괄 처리는 계속해서 진행될 것이다.
제품 환경에서 발생한 에러는 일반적으로 리소스 에러이다. 아마도 이러한 에러를 거의 만나지 못할 것이다. 특히 구성 설정과 환경이 적절한지 확인하기 위해 충분히 테스트했다면 이 에러는 거의 발생하지 않을 것이다. 리소스 부족 에러는 데이터베이스 파일이 꽉 찼을 때나, 프로시저를 충분한 메모리가 없을 때나, 데이터베이스에 연결할 수 없을 때 발생한다. 이런 종류의 치명적 에러가 발생하면 일괄처리가 자동으로 중지된다.
다음은 일반적으로 관심을 가질만한 에러이다.
개체에 대한 권한 부족
제약 조건위배
행을 업데이트 하거나 삽입하려고 하는 동인 일어난 복제
다른 사용자와 상호 교착 상태
NOT NULL위배
현재 데이터 형식에 적합하지 않은 값
다음은 세개의 테이블을 생성하는 예제이다.
CREATE TABLE a(a char(1) primary key)
CREATE TABLE b(b char(1) references a)
CREATE TABLE c(c char(1)
Go
CREATE PROC test as
BEGIN TRANSACTION
INSERT c VALUES(‘X’)
IF(@@ERROR <> 0 ) GOTO on_error
INSERT b VALUES(‘X’) --참조 실패
IF(@@ERROR <> 0 ) GOTO on_error
COMMIT TRANSACTION
RETURN(0)
On_error:
ROLLBACK TRANSACTION
RETURN(1)
이 간단한 프로시져는 T-SQL의 강력함을 잘 보여준다. 각 문들 이후에 시스템 함수 @@ERROR는 연결에 대한 값을 반환할 수 있다. @@ERROR 값 0은 에러가 없다는 것을 의미한다. 사용자 제공 데이터가 있을 때 프로시져 테스트가 수행되면 테이블 b로 삽입하려는 INSERT동작은 외래키 위배로 인해 실패할 것이다.(에러 547발생)
따라서 INSERT 문 이후에 @@ERROR는 547로 설정될 것이다. IF(@@ERROR<>0)문은 TRUE로 평가되고, 실행 흐름은 on_error: 레이블로 간다. 여기서 트랜잭션이 롤백된다. RETURN(1)문이 사용되었기 때문에 반환 코드 값이 1이 되면서 프로시져가 종료된다.
대부분의 다른 프로그래밍 언어에서와 마찬가지로 T-SQL에서도 성공이나 실패 여부를 나타내기 위해 프로시져에서 상태 코드를 반환할 수 있다. 반환된 상태 코드는 호출한 루틴에서 검사된다. 그러나 프로시져를 실행시킨 후 상태를 검사하는 것을 잊지 않아야 한다. 단순히 EXEC test를 사용하는 것은 프로시져가 예상대로 수행되었는지에 관한 정보를 직접적으로 제공하지 않을 것이다. 더 좋은 방법은 이 프로시져의 반환 코드를 조사하기 위해 지역 변수를 사용하는 것이다.
DECLARE @retcode int
EXEC @retcode = test
에러가 발생했을 때 강제적으로 일괄 처리를 종료시키기 위해 SQL Server에 SET XACT_ABORT 옵션이 추가되었다. 다음은 에러 발생시 아무것도 커밋되지 않게한다.
CREATE PROC test AS
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT c VALUES(‘X’)
INSERT b VALUES(‘X’) --참조실패
COMMIT TRANSACTION
GO
EXEC test
GO
SELECT * FROM c
--아무 행도 없음..
XACT_ABORT 옵션의 이름은 약간 잘못 지어진 것 같다. 에러가 발생시에 트랜잭션이 아니라 현재 일괄처리가 즉시 중지되기 때문이다. 이것은 치명적인 리소스 에러 발생시와 비슷하다. 한 일괄 처리 내에서 두개의 트랜잭션을 실행시켰다면 두번째 트랜잭션이 실행될 기회를 얻기 전에 일괄 처리가 중지될 것이기 때문에 두번째 트랜잭션은 결코 실행되지 않을 것이다. 우리가 좋은 프로그래밍 습관을 사용하고 앞 프로시져의 반환 상태를 검사하고 싶다고 가정하자.(프로시져는 명시적으로 RETURN문을 사용하지 않더라도 모든 프로시져들은 기본적으로 반환상태 값을 갖는다. 반환 상태 값 0은 SUCCESS를 나타낸다.)
우리는 다음과 같은 일괄 처리를 작성할 수 있다.
DECLARE @retcode int
EXEC @retcode = test
SELECT @retcode
여기서 프로시저에서 에러가 발생하면 SELECT @RETCODE문이 결코 실행되지 않을 것이다.(SET XACT_ABORT 문 때문에 일괄 처리 전체가 중지된다.) 이런 이유 때문에 SET XACT_ABORT를 사용하는 대신 @@ERROR를 검사할 것을 권한다. 각 문들 이후에 @@ERROR를 검사하는 것은 지루한 일이 되지만, 프로시저에서 실행을 더 잘 제어할 수 있게 해준다. 불행히도, SQL Server 2000의 에러 핸들링은 일관되지 못하고 산만해질 수 있다. 예를 들면, “에러가 발생했을 경우에 이것을 수행하라” 의미를 지닌 루틴을 설치할 방법이 없다. 대신 @@ERROR를 검사한 후 GOTO를 사용하는 앞 예제 방식을 사용해야 한다.
또한, 현재 일괄 처리가 중지될 수 있도록 어떤 에러들이 치명적일 것인지를 미리 알아내거나, 다음 문이 실행될 수 있도록 어떤 에러들이 치명적이지 않은지를 미리 알아내는 쉬운 방법이 없다. 대부분의 경우에 severity 수준 값이 16이나 이 이상의 값을 갖는 에러는 치명적이고, 이 에러가 발생했을 경우에 일괄 처리는 중지될 것이다. 존재하지 않는 함수를 참조하는 구문은 severity 수준 값이 15인 에러이지만, 일괄 처리는 여전히 중지된다. @@SERVERITY 같은 함수는 없다. 대신, 마지막 에러의 serverity 수준을 보기 위해 sysmessage 테이블에서 정보를 얻어야 한다. Serverity 수준 값이 16인 일부 에러들(예:첫번째 에러 검사 예제에서 발생한 제약 조건 위배 에러)은 치명적이지 않다.
트랜잭션 격리 수준
트랜잭션이 실행되는 격리 수준은 다른 사용자의 변경에 대한 여러분 애플리케이션의 민감도를 결정한다. 따라서, 격리 수준은 다른 사용자에 의한 변경으로부터 보호하기 위해 트랜잭션이 얼마나 오랫동안 잠금을 유지할 필요가 있는지 결정한다. SQL Server는 네가지 격리 수준을 제공한다.
READ UNCOMMITTED(dirty read)
READ COMMITTED(디폴트)
REPEATABLE READ
SERIALIZABLE
어떤 격리 수준이 설정되어 있느냐에 따라 트랜잭션은 다르게 동작한다.
READ UNCOMMITTED
READ UNCOMMITTED를 지정하는 것은 트랜잭션 안에서 참조되는 모든 테이블에 대해 NOLOCK 힌트를 사용하는 것과 같다. 이것은 SQL Server의 네가지 트랜잭션 고립화 수준 중에서 가장 엄격하지 못한 수준이다. 이 고립화 수준에서는 Dirty Read(다른 트랜잭션에 의해 수정된, 아직 커밋되지 않은 데이터를 읽는 것)가 허용되고, 반복적이지 않은 읽기(트랜잭션 안에서 읽어 들일 때마다 달라지는 데이터)도 허용된다. 다음 두개의 쿼리를 동시에 실행시켜보자.
Query1
SELECT TOP 5 title_id, qty FROM sales ORDER BY title_id, stor_id
BEGIN TRAN
UPDATE sales SET qty = 0
SELECT TOP 5 title_id, qty FROM sales ORDER BY title_id, stor_id
WAITFOR DELAY ’00:00:05’
ROLLBACK TRAN
SELECT TOP 5 title_id, qty FROM sales ORDER BY title_id, stor_id
Query2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT ‘Now you see it..’
SELECT TOP 5 title_id, qty FROM Sales
WHERE qty = 0 ORDER BY title_id, stor_id
IF @@ROWCOUNT > 0 BEGIN
WAITFOR DELAY ’00:00:05’
PRINT ‘…not you don’t’
SELECT TOP 5 title_id, qty FROM sales WHERE qty = 0 ORDER BY title_id, stor_id
END
첫 번째 쿼리가 실행 중인 동안 두 번째 쿼리를 시작해보면, 첫 번째 쿼리에 의해 수정된 아직 커밋되지 않은 데이터들도 액세스할 수 있음을 알 수 있다. 그 후에, 두 번째 쿼리는 첫 번째 트랜잭션이 종료할 때가지 기다렸다가 같은 데이터들을 다시 읽어들이려 하는데, 첫 번째 트랜잭션에 의한 수정 작업이 이미 롤백 되었기 때문에, 읽어들이려는 데이터는 이미 존재하지 않으며, 따라서 두 번째 쿼리는 비반복적인 읽기를 하게 된다.
READ COMMITTED
READ COMMITTED는 SQL Server의 디폴트 트랜잭션 고립화 수준으로서, 개발자가 다른 것을 지정하지 않으면 READ COMMITTED 방식으로 동작한다. READ COMMITTED는 액세스하는 데이터들에 공유 락을 설정하여 Dirty Read를 금지하지만, 트랜잭션 안에서 원본 데이터에 대한 수정은 허용한다. 따라서 반복적인지 않은 읽기 혹은 팬텀 읽기(내가 읽어온 데이터를 보고 있을 때 다른 사용자가 로우를 입력하거나 삭제하여, 다음에 같은 데이터를 읽어보면 보지 못했던 로우가 들어있거나 있던 로우가 없어지는 것)가 허용된다.
Query1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
PRINT ‘Now you see it…’
SELECT TOP 5 title_id, qty FROM sales ORDER BY title_id, stor_id
WAITFOR DELAY ’00:00:05’
PRINT ‘…now you don’t’
SELECT TOP 5 title_id, qty FROM sales ORDER BY title_id, stor_id
GO
ROLLBACK TRAN
Query2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE sales SET Qty = 6 where Qty = 5
이 예제에서 sales테이블의 첫 번째 로우에서 qty컬럼값이 첫 번째 쿼리의 읽기 작업마다 달라지는데 이것이 고전적인 비반복적인 읽기다.
REPEATABLE READ
REPEATABLE READ는 한 트랜잭션이 액세스하는 데이터를 다른 사용자가 수정하지 못하게 락을 설정하지만, 새로운 로우들의 입력은 허용한다. 따라서 트랜잭션 안에서 읽기 작업을 다시 수행하면 팬텀 데이터가 나타날 수 있다.
Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
PRINT ‘Nothing up my sleeve…’
SELECT TOP 5 title_id, qty FROM sales ORDER BY qty
WAITFOR DELAY ’00:00:05’
PRINT ‘…except this rabbit’
SELECT TOP 5 title_id, qty FROM sales ORDER BY qty
GO
ROLLBACK TRAN
Query2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
INSERT sales VALUES(6380,9999999,GETDATE(),2,’USG-Whenever’,’PS2091’)
실행 결과를 보게 되면, sales테이블은 첫번째 읽을 때 없었던 데이터가 두 번째 읽을 때에는 나타난다. REPETABLE READ는 트랜잭션이 이미 액세스하고 있는 데이터에 대한 수정은 금지하지만, 새로운 로우의 추가는 허용하므로 팬텀 데이터가 발생할 수 있다.
SERIALIZABLE
SERIALIZABLE은 트랜잭션이 액세스하는 데이터들의 일정 범위에 대해 락을 설정하여 Dirty Read와 팬텀 데이터를 금지시킨다. 이 모드는 SQL Server의 네 가지 트랜잭션 고립화 수준 중에서 가장 엄격한 모드이며, 트랜잭션이 참조하는 모든 테이블에 대해 HOLDLOCK힌트를 사용하는 것과 같다.
Query1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
PRINT ‘Nothin up my sleeve…’
SELECT TOP 5 title_id, qty FROM sales ORDER BY qty
WAITFOR DELAY ’00:00:05’
PRINT ‘… or in my hat’
SELECT TOP 5 title_id, qty FROM sales ORDER BY qty
ROLLBACK TRAN
Query2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT sales VALUES(6380,9999999,GETDATE(),2,’USG_Whenever’,’PS2091’)
ROLLBACK TRAN
이 예제에서 SERIALIZABLE 고립화 수준에 의해 설정된 락에 의해 두 번째 쿼리는 첫 번째 쿼리가 끝나기 전에는 실행되지 않는다. 이런 방법은 물샐틈없는 데이터 일관성을 보장해주지만, 그만큼 동시 사용성을 떨어뜨린다.
4가지 고립화 수준의 정리
Ÿ READ UNCOMMITTED : Dirty Read를 허용(커밋되지 않은 데이터를 읽는다.), 반복적이지 않은 읽기 발생(읽을때마다 다른 데이터가 발생)
Ÿ READ COMMITTED : Dirty Read는 발생되지 않지만, 다른 사용자가 읽고 있는 데이터에 수정이 가능하므로 반복적이지 않은 읽기나, 팬텀 데이터가 발생된다.
Ÿ REPEATABLE READ : 다른 트랜잭션에서 수정은 불가능하게 락을 설정하지만, 새로운 데이터의 삽입은 가능하므로 팬텀 데이터가 발생된다.
Ÿ SERIALIZABLE : 다른 트랜잭션에서는 해당 트랜잭션이 끝날때까지 데이터를 건들수 없다.
트랜잭션의 다른 특성들
첫재, 여러 행들에 영향을 주는 한 개의 UPDATE나 DELETE나 INSERT/SELECT문은 항상 원자성을 갖는 동작이다. 즉, 이것은 에러 없이 완료되어야 하고, 그렇지 않으면 자동으로 롤백된다. 예를 들어, 모든 행들을 업데이트 하는 한 개의 UPDATE문을 수행했지만, 한 행이 제약 조건을 위배했다면, 아무 행도 업데이트되지 않고 동작이 종료될 것이다. 이러한 문 내에서 각 행들에 대해 에러를 검사할 방법이 없기 때문에 어떤 에러든지 문을 롤백시킨다. 그러나 일괄 처리는 중지되지 않는다. 일괄처리에서 실행은 다음 문으로 계속 진행한다. 이런 일은 한 SELECT문에 기반을 둔 INSERT 동작들(INSERT/SELECT 또는 SELECT INTO)에서도 일어날 것이다.
또는, SQL Server와 관련되지 않은 시스템 고장 때문에 수백 개의 행들을 업데이트하는 한 개의 UPDATE문이 완료되지 않을 수 있다. SQL Server가 한 개의 UPDATE를 원자성을 갖는 작업 단위라고 생각하기 때문에 SQL Server가 다시 시작하고 데이터베이스에 대해 복구 작업을 수행할 때 UPDATE가 전혀 일어나지 않은 것처럼 만들 것이다. 트랜잭션이 커밋되었다는 것을 SQL Server가 확실하게 알고 있지 않다면 트랜잭션이 롤백될것이다. 고장 전에 백만 개의 행들 중에서 999,999,개의 행이 업데이트 되었을 지라도 트랜잭션이 “모두 처리되지 않는다면, 하나도 처리되지 않는(all-or-nothing)” 동작 방식을 따르기 때문에 복구 후에 아무 것도 업데이트되지 않은 상태로 있을 것이다.
트리거에 의한 변경은 항상 데이터 베이스 변경문과 하나의 단위로 취급된다. 예를 들어, 업데이트 트리거가 데이터를 변경하려고 하지만 실패한다면 데이터베이스 변경 동작이 롤백된다. 이 두 동작이 모두 성공해야 하고 그렇지 않으면 아무 것도 성공하지 않는다.
중첩된 트랜잭션 블록
구문 상으로 볼 때, BEGIN TRANSACTION과 COMMIT TRAN(또는, ROLLBACK TRAN)으로 구성된 블록들이 다른 블록 내에 중첩 될 수 있다. 중첩된 저장 프로시저들을 호출하여 이런 종류의 중첩이 존재하게 할 수도 있다. 그러나 예상과는 다르게 우리가 원하는데로 트랜잭션들은 진정으로 중첩되지 않는다. 그러나 우리가 이 동작을 이해할 수 있고 예측할 수 있다. ROLLBACK TRAN은 내부 블록뿐 아니라 모든 수준의 트랜잭션들을 롤백시킨다. 문이 가장 바깥쪽 블록의 일부가 아니라면 COMMIT TRAN은 아무 일도 하지 않는다. COMMIT TRAN이 가장 바깥쪽 블록의 일부라면 이것이 모든 수준의 트랜잭션들을 커밋한다. 따라서, 트랜잭션 블록들이 중첩될 때 COMMIT이나 ROLLBACK의 동작은 일반적인 중첩 방식을 따르지 않는다. 가장 바깥쪽 COMMIT만이 트랜잭션을 커밋할 수 있지만, ROLLBACK은 수준에 관계없이 트랜잭션 전체를 롤백시킬 것이다. 만일 이런식으로 동작하지 않는다면 데이터가 이미 커밋되었을 것이기 때문에 바깥쪽에 있는 트랜잭션의 ROLLBACK이 자신의 작업을 할 수 없을 것이다. 이 동작 방식은 트랜잭션 상태를 검사하지 않아도 저장 프로시저와 트리거가 예측 가능한 방식으로 자동으로 실행될 수 있게 해준다. ROLLBACK TRAN을 수행하는 중첩된 저장 프로시저는 최상위 프로그래밍에 의해 수행된 작업을 포함하여 트랜잭션 전체를 롤백시킬 것이다.
또한, BEGIN TRAN과 COMMIT TRAN(또는 ROLLBACK TRAN) 블록은 실제로 실행되는 것에 의해서만 결정되고, 일괄 처리에 존재하는 것에 의해 결정되지 않는다. 조건부 분기가 일어나고 문들 중의 하나가 실행되지 않는다면 이 문은 블록의 일부가 아니다.
ROLLBACK TRAN에 대한 일반적인 오해는 이것이 제어 흐름을 바꾸어서 저장 프로시저나 일괄 처리에서 즉시 return하게 만든다고 생각하는 것이다, 그러나 제어 흐름은 계속해서 다음 문으로 이동한다. ROLLBACK은 실제 데이터에만 영향을 준다. 이것은 지역 변수나 SET 문에 영향을 주지 않는다. 트랜잭션 동안에 지역 변수가 바뀐다거나 SET 문이 실행된다면 이 변수와 옵션들은 트랜잭션 시작 전의 값으로 되돌아가지 않는다. 변수(테이블 변수 포함)와 SET 옵션들은 트랜잭션 제어의 일부가 아니다.
시스템 함수 @@TRANCOUNT는 실행된 BEGIN TRAN 블록의 깊이를 반환할 것이다. 이런 식으로 COMMIT TRAN과 ROLLBACK TRAN의 동작을 생각할 수 있다. @@TRANCOUNT가 1이거나 1보다 클 때마다 ROLLBACK TRAN의 동작을 생각할 수 있다. @@TRANCOUNT가 1이거나 1보다 클 때마다 ROLLBACK TRAN은 모든 수준의 트랜잭션 블록에 대해 자신의 일을 수행한다. COMMIT TRAN은 @@TRANCOUNT가 1일 때만 변경사항을 커밋한다.
BEGIN TRAN문을 실행시키면 항상 @@TRANCOUNT가 증가한다. 활성화된 트랜잭션이 없다면, @@TRANCOUNT가 0이다. COMMIT TRAN 문을 실행시키면 @@TRANCOUNT값이 줄어든다. ROLLBACK TRAN문을 실행시키면 모든 트랜잭션들이 롤백되고 @@TRANCOUNT가 0으로 설정된다. 열려 있는 트랜잭션이 없을 때 @@TRANCOUNT가 0일 때 COMMIT TRAN이나 ROLLBACK TRAN을 실행시키면 3902 에러나 3903 에러가 발생한다. 이 에러들은 COMMIT이나 ROLLBACK 요청에 대응하는 BEGIN TRANSACTION이 없다는 것을 나타낸다. 이 경우에 @@TRANCOUNT가 감소되지 않기 때문에 0 값 밑으로 내려 갈 수 없다.
사용자에 의한 에러가 아닌 다른 에러들도 치명적일 수 있다. 예를 들어 메모리가 부족하거나, 트랜잭션 로그가 꽉 찼거나 또는, 교착 상태로 인해 종료 되는 일이 발생할 수 있다. 이러한 에러들은 열려 있는 트랜잭션을 자동으로 롤백시킨다. 만일 이런 일이 발생한다면 @@TRANCOUNT는 0을 반환함으로써 열려 있는 트랜잭션이 존재하지 않는다고 알릴 것이다.
교착상태 조건을 처리하기 위해 클라이언트 애플리케이션에서 재시도 로직을 작성하는 방법이 있을 수 있다. 다른 에러 조건들에 대해서는 여러분이 디폴트 에러 동작을 받아들이기로 결정할지 모른다. 시스템 관리가 올바로 이루어지는 상황에서 애플리케이션이 배포된다면 이러한 에러는 존재하지 않거나 거의 발생하지 않아야 한다. 트랜잭션이 열려 있는지 확인하기 위해 ROLLBACK TRAN이나 COMMIT TRAN을 실행시키기 전에 쉽게 @@TRANCOUNT를 검사할 수도 있다.
ROLLBACK 문에서 트랜잭션의 이름이 지정되지 않는 것은 트랜잭션 블록 관련 문제들 때문이다. 만일 롤백에서 최상위 트랜잭션이 아닌 다른 트래잭션의 이름이 지정되면 6401에러가 발생할 것이다. 그러나 BEGIN TRAN 블록에서 트랜잭션의 이름을 지정하는 것은 상관없다. COMMIT에도 이름이 지정될 수 있고, 이 경우에 이것이 기본적으로 NO-OP이기 때문에 최상위에 있는 것과 짝을 이루지 않아도 에러가 발생하지 않는다.
BEGIN TRAN문에 이름을 지정하는 한가지 이유는 COMMIT와 ROLLBACK 문에 주석을 다는 효과를 주기 위한 것이다. ROLLBACK TRAN은 트랜잭션 이름을 필요로 하지 않지만, 트랜잭션 전체를 롤백하고 있다는 것을 강조하기 위한 방법으로 이름을 포함시킬 수 있다.
저장점(Savepoints)
이따금 사용자들이 트랜잭션 블록들을 중첩시켰을 때 결과 동작이 원하는 것과 다를 수 있다. 사용자가 실제로 원하는 것은 트랜잭션에서 저장점이 일어나는 것이다. 저장점은 작업이 부분적으로 롤백되거나 취소될 수 있는 트랜잭션 내의 지점을 나타낸다. 좀더 정확히 말하면, 이것을 “롤백 지점”이라고 부를 수 있다. 저장점은 변경 사항을 데이터베이스에 저장하거나 커밋하지 않는다. COMMIT문만이 이것을 할 수 있다.
SQL Server에서 SAVE TRAN문을 통해 저장점을 사용할 수 있다. 이 문은 @@TRANCOUNT값에 영향을 주지 않는다. 저장점 위치로 롤백해도(트랜잭션 롤백이 아님) @@TRANCOUNT 값에는 영향이 없다. 그러나, 롤백 시에 저장점 이름을 명시적으로 지정해야 한다. 특정 이름을 지정하지 않고 ROLLBACK TRAN을 사용하면 항상 트랜잭션 전체가 롤백될 것이다.
BEGIN TRAN A
SELECT @@TRANCOUNT
SAVE TRAN B
SELECT @@TRANCOUNT
ROLLBACK TRAN B
SELECT @@TRANCOUNT
ROLLBACK TRAN A
SELECT @@TRANCOUNT
락
잠금은 SQL Server를 포함한 다중 사용자 데이터베이스 시스템에서 매우 중요한 기능을 수행한다. SQL Server는 동시에 여러 사용자들을 관리하고, 모든 트랜잭션들이 지정된 격리 수준의 특성들을 지키게 한다. 가장 높은 격리 수준인 Serializable에서 SQL Server는 다중 사용자 시스템이 단일 사용자 시스템과 동일한 결과를 만들어내도록 해야 한다. SQL Server는 동일한 시간에 동일한 데이터베이스에 대해 한 사용자의 변경으로 인해 다른 사용자의 작업이 영향을 받는 일이 없도록 자동으로 데이터를 잠금으로써 이것을 가능하게 한다.
잠금 관리자
SQL Server는 여러 가지 모드를 사용하여 데이터를 잠글 수 있다. 예를 들면, 읽기 동작은 공유 잠금을 얻고, 쓰기 동작은 단독 잠금을 얻는다. 업데이트 잠금은 업데이트 동작의 시작 부분에 데이터가 읽힐 때 얻어진다. SQL Server 잠금 관리자는 이 잠금들을 얻고 해제한다. 또한, SQL Server 잠금 관리자는 잠금 모드들 간의 호환성을 관리하고, 교착 상태를 해결하고, 필요할 경우에 잠금 수준을 올린다. 잠금 관리자는 테이블에 있는 잠금과 테이블의 페이지에 있는 잠금과 인덱스 키에 있는 잠금과 데이터의 개별 행들에 있는 잠금을 제어한다. 잠금은 페이지 헤더나 인덱스와 같은 시스템 데이터에도 유지될 수 있다.
잠금 관리자는 두 가지 잠금 시스템을 제공한다. 첫 번째 시스템은 완전하게 공유된 모든 데이터에 영향을 주고, 테이블과 데이터 페이지와 텍스트 페이지와 잎수준 인덱스 페이지에 행 잠금과 페이지 잠금과 테이블 잠금 기능을 모두 제공한다. 두 번째 시스템은 내부적으로 인덱스 동시성 제어와 내부 데이터 구조에 대한 엑세스 제어와 데이터 페이지에 있는 개별 행들의 추출을 위해 사용된다. 이 두 번째 시스템은 래치를 사용하는데, 이것은 잠금보다 리소스를 적게 사용하고 성능을 최적화한다. 모든 잠금 동작을 위해 완전한 기능의 잠금을 사용할 수 있겠지만, 이 잠금들이 복잡하기 때문에 모든 내부적 동작을 위해 이러한 잠금이 사용된다면 시스템이 느려질 것이다. Sp_lock 시스템 저장 프로시저나 syslockinfo테이블로부터 정보를 얻어내는 메커니즘을 사용하여 잠금들을 조사해보면 래치는 볼 수 없고, 완전하게 공유된 데이터에 대한 잠금들의 정보만을 볼 것이다.
잠금은 데이터의 논리적 일관성을 보장하는 반면, 래치는 물리적 일관성을 보장한다. 물리적으로 페이지에 행을 넣거나 데이터를 이동시킬 때 래치가 사용된다. SQL Server는 이 데이터 이동이 간섭 없이 일어날 수 있도록 보장해야 한다.
잠금 관리자와 격리 수준
SQL Server는 ANSI와 ISO에서 지정된 네 가지 트랜잭션 격리 수준을 모두 지원한다. Serializable 격리 수준을 얻기 위해서는 팬텀을 방지해야 한다. 왜냐하면, 트랜잭션의 동작은 트랜잭션이 단일 사용자 시스템에서 동작할 때와 동일해야 하기 때문이다. Serializable 격리 수준을 지원하기 위해 “키 범위 잠금(key-range lock)”이라고 불리는 특별한 형식의 잠금을 사용하여 인덱스 범위를 잠근다. 이런 잠금은 팬텀을 방지하기 위해 트랜잭션 끝까지 유지된다. 어떤 인덱스도 존재하지 않는다면 잠금 관리자가 테이블 잠금을 사용하여 Serializable을 기능을 보장한다.
잠금 관리자는 2단계 잠금 서비스를 제공한다. 2단계 잠금(2PL)과 2단계 커밋(2PC) 프로토콜은 직접적으로 관련되어 있지 않다. 그러나 2PC가 2PL 서비스를 사용해야 한다는 사실은 확실하다. 2PL에는 트랜잭션이 “성장하는” 단계와 “줄어드는” 단계가 있다. 성장하는 단계 동안에는 트랜잭션이 잠금을 얻고, 줄어드는 단계 동안에는 잠금을 해제한다. Serializable 기능을 얻기 위해, 모든 잠금들은 트랜잭션 끝까지 유지된 후 한 번에 모두 제거된다.
COMMITTED READ와 같이 더 낮은 격리 수준에서는 잠금이 더 빨리 해제될 수 있다. 즉 객체 사용이 완료되었을 때 해제될 수 있다. 예를 들어, 테이블에서 데이터 범위가 쿼리되고 있다면 아마도 공유 잠금이 있을 것이다. COMMITTED READ 격리 수준에서 스캔 동작이 한 데이터 항목에서 다음 데이터 항목으로 이동하자마자 공유 잠금이 해제된다. 반면 단독 잠금은 필요할 때 트랜잭션이 롤백될 수 있도록 트랜잭션 끝까지 항상 유지된다.
SERIALIZABLE이나 REPEATABLE READ 격리 수준에서 읽힌 데이터가 변경되지 않는다는 것을 보장하기 위해 또는 트랜잭션이 진행 중인 동안 쿼리 조건을 만족하는 새 행들이 추가될 수 없다는 것을 보장하기 위해 공유 잠금이 트랜잭션 끝까지 유지되어야 한다. 공유 잠금과 마찬가지로 래치도 트랜잭션의 경계에 묶여 있지 않다. 왜냐하면, 래치는 데이터를 직접 잠그기 위해 사용되지 않고 상호 배타성 기능을 제공하기 위해 사용되기 때문이다. 예를 들면, 클러스터된 인덱스가 있는 테이블에 행을 삽입하는 동안 다른 삽입 동작으로 인해 충돌하는 일이 없도록 가까운 인덱스 페이지가 래치된다. 래치는 긴 시간 동안 상호 배타성 기능을 위해서만 필요하다.
스핀잠금
단기적 잠금이 필요한 경우에 SQL Server는 스핀 잠금(spinlock)으로 구현된 래치를 사용하여 상호 배타적 기능을 얻는다. 스핀 잠금은 순수하게 상호 배타성 기능을 위해서만 사용되고, 결코 사용자 데이터를 잠그지 않는다. 스핀 잠금은 래치보다 훨씬 가볍다.(그리고 래치는 데이터 페이지와 인덱스 잎 페이지에 사용되는 오나전한 잠금보다 더 가볍다.) 스핀 잠금은 SQL Server에만 있는 기능이고, 이 프로세서 형식을 구현하기 위해 프로세서 관련 어셈블리 언어가 사용된다. 스핀 잠금은 각 프로세서 형식과 관련된 몇 라인의 어셈블리 언어로 구현된다. 스핀 잠금을 요청한 개체는 이 잠금이 즉시 사용 가능하게 될 때까지 반복적으로 자신의 요청을 전달한다. 스핀 잠금은 일상적으로 자주 사용되지 않는 리소스에 대해 SQL Server내에서 종종 mutex로 사용된다. 리소스가 자주 사용된다면, 기다리다가 운영체제에 의해 할당되는 것보다 반복 요청이 더 좋을 정도로 스핀 잠금 시간이 충분히 짧다. 스핀 잠금은 리소스 대기 시간이 짧다고 예상되는 경우에 사용된다.
교착상태
두 프로세스가 리소스를 기다리고 있는데 상대 프로세스가 리소스를 얻을 수 없게 하기 때문에 어떤 프로세스도 진행할 수 없을 때, 교착 상태(DeadLock)가 일어난다. 진정한 교착 상태는 외부 간섭이 없어도 어떤 프로세스도 진행할 수 없는 상태이다. 교착 상태가 일어나면 자동으로 SQL Server가 개입하여 문제를 해결한다.
SQL Server에서 크게 두 가지 형식의 교착 상태가 발생할 수 있다. 하나는 순환 교착 상태(cycle deadlock)이고, 다른 하나는 변환 교착 상태(conversion deadlock)이다. A 프로세스가 트랜잭션을 시작하고, authors 테이블에서 단독 테이블 잠금을 얻고, publishers 테이블에 대해 단독 테이블 잠금을 요청한다. 동시에 B 프로세스도 트랜잭션을 시작하고, publishers 테이블에서 단독 잠금을 얻고, authors테이블에 대해 단독 잠금을 요청한다. 두 프로세스는 교착 상태가 된다. 각 프로세스들은 상대 프로세스가 필요로 하는 리소스를 유지하고 있다. 외부 개입이 없다면 어떤 프로세스도 진행할 수 없고 영원히 교착 상태에 빠져 있을 것이다.
SQL Server는 자동으로 교착 상태를 감지하고 잠금관리자를 통해 개입한다. SQL Server 2000에서 교착 상태는 잠금이 아닌 다른 리소스를 포함할 수도 있다. 예를 들어, A프로세스가 Table1에서 잠금을 유지하고 있고 여유 메모리가 생기기를 기다리고 있고, B프로세스가 Table1에서 잠금을 얻을 때가지 해제할 수 없는 메모리를 갖고 있다면 프로세스들이 교착 상태에 빠질 것이다. 스레드와 통신 버퍼도 교착 상태와 관련될 수 있다. 래치는 교착 상태 감지와 관련이 엇다. 왜냐하면, SQL Server는 래치를 얻을 때 교착 상태가 발생하지 않는 알고리즘을 사용하기 때문이다. SQL Server는 교착 상태를 감지할 때 한 프로세스의 일괄 처리를 종료시키고, 활성 트랜잭션을 롤백시키고, 교착 상태르르 해결하기 위해 이 프로세스의 잠금을 해제한다.
SQL Server 2000에서 LOCK_MONITOR라고 불리는 스레드가 매 5초마다 시스템에서 교착 상태 발생 여부를 검사한다. 잠금 모니터는 “교착 상태 감지 카운터” 라고 불리는 내부 카운터를 사용하여 시스템에서 더 자주 교착 상태를 검사할 것인지 결정한다. 교착 상태 감지 카운터는 값 3에서 시작하고, 교착 상태가 발생하면 다시 3으로 설정된다. LOCK_MONITOR 스레드가 5초 간격으로 검사할 때 교착 상태가 없으면 교착 상태 감지 카운터 값을 줄인다. 카운터 값이 0보다 크고 한 프로세스가 잠금 리소스를 요청했는데 블로킹 되었다면, 잠금 관리자가 잠금 모니터에게 모든 잠금들에서 교착 상태가 발생하지 않았는지 검사하라고 요청한다. 20초 동안 교착 상태가 발견되지 않으면, 교착 상태 감지 카운터가 0이 되고, 잠금 관리자가 프로세스가 블로킹될 때마다 있었다 교착 상태 감지 요청을 중지한다. 대부분의 시간 동안에 교착 상태 감지 카운터는 0으로 되어 있고 교착 상태 검사는 잠금 모니터의 5초 간격으로만 수행된다.
LOCK_MONITOR 스레드는 순환 대기 잠금들의 목록을 조사함으로써 교착 상태를 검사한다. 이것은 잠금을 유지하고 있는 프로세스와 잠금을 기다리고 있는 프로세스들간의 순환관계를 나타낸다. SQL Server는 이미 수행된 작업량을 고려하여 가장 롤백하기 쉬운 프로세스를 희생 프로세스로 선택한다. 그러나 어떤 동작들은 교착 상태 희생자로 선택될 수 없다. 예를 들면, 트랜잭션을 롤백시키는 것과 관련된 프로세스는 교착 상태 희생자로 선택될 수 없다. 롤백되고 있는 변경 사항들이 중간 상태로 남아 있어서 데이터를 손상시킬 수 있기 때문이다.
SET DEADLOCK_PRIORTY LOW|NORMAL 문을 사용함으로써 교착 상태가 감지되었을 때 특정 프로세스를 희생 프로세스로 만들 수 있다. 프로세스가 LOW 교착 상태 우선 순위를 갖고 있다면 이 프로세스가 루프를 닫은 프로세스가 아니더라도 교착 상태가 감지되었을 때 이 프로세스가 종료된다. 그러나 교착 상태 우선 순위를 HIGH로 설정하는 SET옵션은 없다. 프로세스가 교착 상태 상황에서 항상 살아남을 수 있게 하고 싶을지 모르지만, 이런 기능은 SQL Server에서 아직 구현되어 있지 않다. 프로세스들이 테이블 액세스 순서를 미리 정해 놓았다면 순환 교착 상태를 피할 수 있었을 것이다. 예를 들어, 항상 authors 테이블을 먼저 액세스하고 publishers 테이블을 두번째로 액세스하기로 결정했다면, 한 프로세스가 먼저 테이블에서 단독 잠금을 기다리고 있는 프로세스는 정상적이고 자연스럽다. 기다리는 것은 교착 상태가 아니라는 것을 기억하기 바란다.
잠금모드
SQL Server는 공유 잠금, 단독 잠금, 업데이트 잠금, 내재된 잠금을 포함한 여러가지 잠금 모드를 사용한다.
공유 잠금
프로세스는 데이터를 읽을 때 자동으로 공유 잠금을 얻는다. 공유 잠금은 테이블이나 페이지나 인덱스 키나 개별 행들에 유지될 수 있다. 많은 프로세스들이 동일한 데이터에 대해 공유 잠금을 유지할 수 있지만, 어떤 프로세스도 공유 잠금이 있는 데이터에서 단독 잠금을 얻을 수 없다. 일반적으로 데이터를 읽자 마자 공유 잠금이 해제되지만, 쿼리 힌트나 다른 트랜잭션 격리 수준을 사용함으로써 이 방식을 바꿀 수 있다.
단독 잠금
데이터가 삽입이나 업데이트나 삭제 동작에 의해 변경될 때 SQL Server가 이 데이터에 대해 자동으로 단독 잠금을 얻는다. 특정 데이터 리소스에 대해서 한 번에 한 프로세스만이 단독 잠금을 유지할 수 있다. 실제로, 한 프로세스가 데이터 리소스에 대해 잠금을 요청했을 때 다른 프로세스가 이미 이 데이터에 대해 단독 잠금을 유지하고 있다면 나중에 요청한 프로세스가 어떤 종류의 잠금도 얻을 수 없다. 잠금 호환성에 대해 설명할 때 이것에 대해서도 알아보겠다. 단독 잠금은 트랜잭션 끝까지 유지된다. 따라서, 현재 트랜잭션이 커밋되거나 롤백될 때까지 다른 프로세스가 변경된 데이터를 사용할 수 없다. 힌트를 사용함으로써 다른 프로세스들은 단독 잠금이 유지된 데이터를 읽기로 결정할 수 있다.
업데이트 잠금
업데이트 잠금은 단지 공유 잠금과 단독 잠금의 하이브리드 형태이다. SQL Server가 데이터 변경 동작을 수행하지만 먼저 변경될 리소스를 찾기 위해 테이블을 검색할 필요가 있을 때 이 잠금이 얻어진다. 쿼리 힌트를 사용함으로써 프로세스는 특별히 업데이트 잠금을 요청할 수 있고, 이 경우에 업데이트 잠금은 변환 교착상태를 방지한다. 업데이트 잠금은 현재 데이터를 읽고 있는 다른 프로세스들과 호환될 수 있게 하고, 데이터를 읽은 이후로 데이터가 변경되지 않았다는 것을 확신하면서 나중에 데이터를 변경할 수 있게 해준다. 데이터를 변경하기에는 업데이트 잠금으로 충분치 않다. 변경 시에는 항상 변경되고 있는 데이터 리소스가 단독 잠금을 갖고 있어야 한다. 업데이트 잠금은 나중에 단독 잠금을 요청할 수 있게 하는 관문 역할을 한다.(많은 프로세스들이 리소스에 대해 공유 잠금을 유지할 수 있지만, 한 프로세스만이 업데이트 잠금을 유지할 수 있다.) 프로세스가 리소스에 대해 업데이트 잠금을 유지하고 있는 한, 다른 어떤 프로세스도 이 리소스에 대해 업데이트 잠금이나 단독 잠금을 얻을 수 없다(대신, 동일한 리소스에 대해 업데이트 잠금이나 단독 잠금을 요청하는 다른 프로세스는 기다려야 한다.) 업데이트 잠금을 유지하고 있는 프로세스는 이 리소스에 대해 단독 잠금을 얻을 수 이있다. 업데이트 잠금을 “업데이트하려는” 잠금으로 생각할 수 있다. 실제로, 이것이 이 잠금이 하는 역할이다. 업데이트 잠금 혼자로는 데이터를 업데이트하기에 충분치 않다. 실제로 데이터를 변경하기 위해서는 단독 잠금이 필요하다. 바로 단독 잠금을 얻을 수 있기 때문에 변환 교착 상태를 피할 수 있다.
“업데이트 잠금”이란 이름 때문에 이 잠금의 정확한 역할을 혼동하는 일이 없기 바란다. 업데이트 잠금은 단지 업데이트 동작만을 위한 것이 아니다. SQL Server는 실제 데이터 변경 전에 데이터를 검색해야 하는 데이터 변경 동작을 위해 업데이트 잠금을 사용한다. 이러한 동작에는 조건에 따른 업데이트 및 삭제와 클러스터된 인덱스가 있는 테이블로 삽입하는 것 등이 포함된다. 후자의 경우에 SQL Server는 새 행이 들어갈 올바른 위치를 찾기 위해 먼저 클러스터된 인덱스를 사용하여 데이터를 검색해야 한다. SQL Server는 검색만을 수행하고 있는 동안 데이터를 보호하기 위해 업데이트 잠금을 사용한다. 올바른 위치를 찾고 삽입 동작을 시작한 이후에야 업데이트 잠금이 단독 잠금으로 된다.
내재된 잠금
내재된 잠금(Intent Lock)은 실제로 특별한 잠금 모드가 아니다. 이것은 앞에서 설명된 모드에 대한 수식어이다. 즉, 내재된 공유 잠금과 내재된 단독 잠금과 내재된 업데이트 잠금을 가질 수 있다. SQL Server가 서로 다른 수준의 단위에서 잠금을 얻을 수 있기 때문에 리소스의 구성 요소가 이미 잠겨있다는 것을 가리키는 매커니즘이 필요하다. 예를 들어, 한 프로세스가 테이블을 잠그려고 한다면 이 테이블의 행(또는 페이지)이 이미 잠겨 있는지 알아내는 방법이 필요하다. 내재된 잠금은 이 목적에 사용된다.
특별한 잠금 모드
SQL Server는 세가지 잠금 모드를 추가로 제공한다. 이것들은 스키마 안정성 잠금과 스키마 수정 잠금 그리고 대량 업데이트 잠금이다. 쿼리가 컴파일될 때, 스키마 안정성 잠금은 다른 프로세스들이 스키마 수정 잠금을 얻지 못하게 한다.(스키마 수정 잠금은 테이블의 구조가 변경되고 있을 때 얻어진다.) BULK INSERT명령어가 실행될 때 대량 업데이트 잠금이 얻어진다. 또한 복사 동작은 TABLOCK힌트를 사용함으로써 특별한 이 잠금을 요청해야 한다. 또는 테이블이 table lock on bulk load이라고 불리는 테이블 옵션을 TRUE로 설정할 수 있고, 이렇게 한 후 대량 복사 IN이나 BULK INSERT 동작이 자동으로 대량 업데이트 잠금을 요청할 것이다. 여러 연결들이 대량 업데이트 잠금을 요청했고 얻었다면 이 연결들이 병렬로 동일한 테이블에 로드할 수 있다.
SIX 잠금이란 것도 있다. 이 모드는 결코 잠금 관리자에 의해 직접 요청되지 않고, 단지 변환의 결과이다. 트랜잭션이 리소스에 대해 공유(s) 잠금을 유지하고 있고 나중에 내재된 단독(IX)잠금이 필요하다면 잠금모드가 SIX로 표현 될 것이다.
SET TRANSACTION ISOLATION LEVEL REPETABLE READ
BEGIN TRAN
SELECT * FROM bigtable
UPDATE bigtable
SET col = 0
WHERE keycolumn = 100
테이블이 크다고 가정하면 SELECT문이 공유 테이블 잠금을 얻을 것이다.(테이블이 작을 경우는 개별적으로 행 잠금이나 키 잠금을 얻는다.) 이렇게 한 후 UPDATE문은 한 행의 업데이트를 수행하기 위해 한 개의 단독 키 잠금을 얻을 것이고, 키 수준에서 단독(X) 잠금이 있다는 것은 페이지 수준이나 테이블 수준에서 내재된 단독(IX) 잠금이 있다는 것을 의미한다. 테이블은 sp_lock을 통해 SIX를 보여줄 것이다.
SQL Server의 잠금 모드들
축약어 잠금모드 내부 코드 설명
S 공유잠금 4 다른 프로세스들이 잠겨진 리소스를 읽을 수 있도록 허용하지만, 이 리소스를 변경하는 것을 허용하지 않는다.
X 단독잠금 6 다른 프로세스들이 잠겨진 리소스에 있는 데이터를 읽거나 변경할 수 없게 방지한다.(단, 프로세스가 Read Uncommitted 격리 수준으로 설정되어 있지 않을 경우에.)
U 업데이트잠금 5 다른 프로세스들이 업데이트 잠금이나 단독 잠금을 얻지 못하도록 방지한다.
IS 내재된공유잠금 7 이 리소스의 구성 요소가 공유 잠금으로 잠겨 있다는 것을 나타낸다. 이 잠금은 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
IU 내재된업데이트잠금 8 이 리소스의 구성 요소가 업데이트 잠금으로 잠겨 있다는 것을 나타낸다. 이 잠금은 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
IX 내재된단독잠금 9 이 리소스의 구성 요소가 단독 잠금으로 잠겨 있다는 것을 나타낸다. 이 잠금은 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
SIX 공유및내재된 단독잠금 11 공유 잠금을 유지하고 있는 리소스가 단독 잠금으로 잠겨진 구성 요소도 갖고 있다는 것을 나타낸다.
Sch-S 스키마안정성잠금 2 이 테이블을 사용하고 있는 쿼리가 컴파일되고 있다는 것을 나타낸다.
Sch-M 스키마수정잠금 3 테이블의 구조가 변경되고 있다는 것을 나타낸다.
BU 대량업데이트잠금 13 대량 복사 동작이 데이터를 테이블로 복사하고 있고 TABLOCK힌트가 적용되고 있을 때 사용된다.
“키 범위 잠금”이라고 불리는 잠금 모드는 Serializable 격리 수준에서 데이터 범위를 잠그려고 할 때만 얻어진다. 키 범위 잠금에는 9가지 형식이 있고, 각 형식들은 두 부분으로 된 이름을 갖는다. 첫 번째 부분은 연속 인덱스 키들 사이의 데이터 범위에 있는 잠금 형식을 나타내고, 두 번째 부분은 키 자체에 있는 잠금 형식을 나타낸다.
SQL Server의 키범위 잠금
축약어 잠금모드의 내부코드 설명
RangeS-S 14 키 사이의 범위에 있는 공유 잠금 범위 끝의 키에 있는 공유 잠금
RangeS-U 15 키 사이의 범위에 있는 공유 잠금 범위 끝의 키에 있는 업데이트 잠금
Rangeln-Null 16 키 사이의 범위에서 삽입을 방지하는 단독 잠금 키 자체에는 어떤 잠금도 없다.
RangeX-X 22 키 사이의 범위에 있는 단독 잠금 범위 끝의 키에 있는 단독 잠금
Rangeln-S 17 S잠금과 Rangeln_NULL잠금에 의해 만들어진 변환 잠금
Rangeln-U 18 U잠금과 Rangeln_NULL잠금에 의해 만들어진 변환 잠금
Rangeln-X 19 X잠금과 Rangeln_NULL잠금의 변환
Rangeln-U 20 Rangeln_NULL잠금과 RangeS_S잠금의 변환
RangeX-U 21 Rangeln_NULL잠금과 RangeS_U잠금의 변환
잠금 단위(Granularity)
SQL Server는 테이블 수준이나 페이지 수준이나 행 수준에서 사용자 데이터 리소스를 잠글 수 있다. SQL Server는 인덱스 키와 인덱스 키의 범위를 잠글 수 있다. SQL Server는 인덱스 키와 인덱스 키의 범위를 잠글 수도 있다. 테이블이 클러스터된 인덱스를 갖고 있다면 데이터 행들이 클러스터된 인덱스의 잎 수준에 있고, 이것들이 행 잠금 대신 키 잠금을 통해 잠긴다.
Syslockinfo 테이블은 잠긴 리소스의 형식(행, 키, 페이지 등), 잠금 모드, 특정 리소스의 식별자를 저장함으로써 각 잠금들을 추적한다. 프로세스가 잠금을 요청할 때 SQL Server는 요청된 잠금과 syslockinfo 테이블에 이미 있는 리소스를 비교하는 리소스 형식과 식별자에서 정확히 일치하는 것을 찾는다(정확히 일치하는 것을 찾기 위해 잠금 모드가 같을 필요는 없다.) 그러나 한 프로세스가 authors 테이블에서 단독 잠금 행을 갖고 있을 때 다른 프로세스가 authors 테이블 전체에 대해 잠금을 얻으려고 할지도 모른다. 이것들이 서로 다른 리소스이기 깨문에 syslockinfo에 이미 추가로 정보가 저장되어 있지 않는 한 SQL Server는 정확히 일치하는 것을 찾지 못한다. 이런 상황을 위해 내재된 잠금이 존재한다. Authors테이블의 행에서 단독 잠금을 가진 프로세스는 이 행을 포함하고 있는 “페이지”에 대해서 내재된 단독 잠금을 갖고, 행을 포함하고 있는 “테이블”에 대해서도 내재된 단독 잠금을 갖는다. 두번째 프로세스가 테이블에 대해 단독 잠금을 얻으려고 시도할 때, 동일한 잠금 리소스(authors 테이블)에 대해 syslockinfo 테이블에서 충돌하는 행이 있다는 것을 발견한다. 이미 잠겨 있는 리소스에 대한 모든 잠금 요청이 충돌을 일으키는 것은 아니다. 호환되지 않은 잠금 모드로 한 프로세스에 의해 이미 잠겨 있는 리소스에 대해 다른 프로세스가 잠금을 요청할 때, 충돌이 일어난다. 예를 들면, 공유 잠금은 서로 호환되기 때문에 두 프로세스가 동일한 리소스에 대해 공유 잠금을 얻을 수 있다.
키 잠금 : SQL Server 2000은 두 가지 종류의 키 잠금을 지원하고, 이들의 사용은 현재 트랜잭션의 격리 수준에 따라 달라진다. 격리 수준이 Read Committed나 Repeatable Read이면, SQL Server가 쿼리를 처리하는 동안 액세스된 실제 인덱스 키들을 잠그려고 한다. 클러스터된 인덱스가 있는 테이블에서 데이터 행들은 인덱스의 잎 수준에 있고, 얻어진 키 잠금을 볼 것이다. 테이블이 힙이면 클러스터되지 않은 인덱스에 대해 키 잠금을 보고 실제 데이터에 대해 행 잠금을 볼 것이다.
격리 수준이 Serilizable이면 특별한 상황이 발생한다. 우리는 팬텀을 방지하고 싶다. 즉, 우리가 트랜잭션 내에서 데이터 범위를 스캔했다면, 어느 누구도 스캔된 범위로 값을 삽입할 수 없도록 테이블을 충분히 잠글 필요가 있다.
BEGIN TRAN
SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 50000
명시적 트랜잭션에서 위와 같이 실행하면 트랜잭션이 끝나기 전에 30000-50000사이의 salary값을 가진 새 행이 삽입되지 않도록 잠금을 얻어야 한다. 7.0이전에는 SQL Server가 페이지 전체나 테이블 전체를 잠금으로써 이것을 보장했다. 그러나 많은 겨우에 이것은 너무 지나친 제한이었다. 실제 WHERE절에서 지정된 것보다 더 많은 데이터가 잠겼다. SQL Server 2000은 키 범위 잠금을 사용한다. 이것은 인덱스에 있는 특정 키 값과 연관되어 있고, 이 키와 인덱스의 이전 키 사이에 있는 모든 값들이 잠기다는 것을 나타낸다.
Employee 테이블의 lastname 필드에 인덱스가 있다고 가정하고 프로세스의 고립화 수준이 Seriializable에서 다음을 실행해 보자
SELECT *
FROM Employee WHERE last_name BETWEEN ‘Delaney’ AND ‘Dulaney’
Dalls, Donvam Dulut가 테이블에서 순차적으로 있는 잎 수준 인덱스 키들이라면, Donovan과 Dullas가 키 범위 잠금을 얻는다.(그러나 Donovan에 해당하는 한 행만이 결과 집합에서 반환된다.) 키 범위 잠금은 두 키 범위 잠금으로 끝나는 범위에 어떤 것도 삽입되지 않게 방지한다. Dallas보다 크고 Donovan보다 작거나 같은 어떤 값도 삽입될 수 없고, Donovan보다 크고 Duluth보다 작거나 같은 어떤 값도 삽입될 수 없다 키 범위 잠금에서 간격을 결정할 때 이전 순차적 키에서 시작하는 점은 열린 상태이고 잠금이 있는 키에서 끝나는 점은 닫힌 상태이다. 키 범위 잠금은 완전하지 않지만, 페이지나 테이블 전체를 잠그는 것보다 훨씬 더 큰 동시성을 제공한다.
잠금 리소스들
잠금은 “익스텐트”에서도 수행된다. 테이블이나 인덱스가 성장할 필요가 있고 새 익스텐트가 할당되어야 할 때 이 잠금이 자동으로 일어난다. 익스텐트 잠금을 또 다른 형식의 특수 목적 래치로 생각 할 수 있지만, 이것은 sp_lock 프로시저의 출력 결과에 나타난다. 익스텐트는 공유 익스텐트 잠금과 단독 익스텐트 잠금을 모두 갖는다.
Sp_lock의 출력 결과를 조사할 때 대부분의 프로세스들이 적어도 한 데이터베이스에서 잠금을 유지하고 있다는 것에 주목하기 바란다. 실제로 master나 tempdb가 아닌 다른 데이터베이스에서 대해 DB잠금을 가질 것이다. 이것은 항상 공유 잠금이고, 데이터베이스에 대가 언제 사용중인지 알아내기 위해 SQL Server에 의해 사용된다. SQL Server는 데이터베이스가 제거되거나 복원되거나 닫힐 수 있는지 결정할 때 DB잠금을 검색한다. Master와 tempdb는 제거되거나 닫힐 수 없기 때문에 이 데이터베이스들에서는 DB잠금이 필요하지 않다. 또한, tempdb는 결코 복원되지 않고, master 데이터베이스를 복원하기 위해서는 서버 전체가 단일 사용자 모드로 시작되어야 하기 때문에 DB잠금이 불필요하다. 일반적으로 여러분이 익스텐트 잠금과 데이터베이스 잠금에 대해 관심을 가질 필요가 없지만, sp_lock을 실행시키거나 syslockinfo를 조사하면 이것들을 볼 수도 있을 것이다.
애플 리케이션 잠금
잠금에 관한 정보를 저장하고 호환되지 않는 잠금들을 검사하기 위해 SQL Server가 사용하는 방법은 매우 간단하고 확장 가능하다. 여러분도 알고 있듯이 SQL Server 잠금 관리자는 잠긴 개체에 대해 아무 것도 모른다. 잠금 관리자는 항목의 실제 구조를 알지 않고 단지 리소스를 나타내는 문자열만을 다룬다. 두 프로세스가 동일한 리소스에 대해 호환되지 않은 잠금을 얻으려고 한다면 블로킹이 일어날 것이다.
SQL Server 개발자들이 행과 페이지와 테이블뿐만 아니라 컬럼들도 개별적으로 잠글 수 있게 했다면 이들은 컬럼 잠금들에 대한 내부 코드 번호를 결정한 후 이것을 리소스 목록에 추가했을 것이다.
SQL Server 2000은 새 잠금 리소스를 추가하는 대신, 잠겨질 수 있는 리소스를 확장하게 한다. “블로킹을 감지하고 교착 상태를 만들기 위해 제공된 메커니즘”을 이용할 수 있고, 원하는 것을 잠글 수 있다. 이 잠금 리소스들은 “애플리케이션 잠금”이라고 불린다. 애플리케이션 잠금을 정의하기 위해 잠그고 있는 리소스의 이름과 모드와 소유자와 만료시간을 지정한다.
두 리소스들이 동일한 데이터베이스에서 동일한 이름과 동일한 소유자를 갖는다면 이들은 동일한 리소스로 간주되고 블로킹되기 쉽다. “잠금 소유자(lock owner)”는 세션이나 트랜잭션이나 커서를 의미한다는 것을 기억하기 바란다. 애플리케이션 잠금에서 가능한 소유자는 트랜잭션과 세션뿐이다. 요청된 잠금들의 모드가 서로 호환된다면 동일한 리소스에 대해 요청된 잠금들이 모두 허용될 수 있다. 예를 들어 한 번에 한 사용자만이 실행시켜야 하는 저장 프로시저가 있다고 가정하자. 특별한 잠금을 얻는 sp_getapplock 프로시저를 사용함으로써 이 프로시저를 잠글 수 있다. 이 잠금은 누군가가 이 프로시저를 사용하고 있다는 것을 의미 한다. 프로시저가 완료되었을 때, sp_releaseapplock을 사용하여 이 잠금을 해제할 수 있다.
EXEC sp_getapplock ‘ProcLock’, ‘Exclusive’, ‘session’
EXEC MySpecialProc <매개변수 목록>
EXEC sp_releaseapplock ‘ProcLock’, ‘session’
모든 세션이 규약을 따르고 sp_getapplock을 실행시키려고 하기 전에 이 프로시저에 대한 권한을 요청하기 위애 sp_getapplock을 사용한다면 sp_releaseapplock을 사용하여 잠금이 해제될 때까지 또는 세션이 종료될 때까지 어떤 세션도 이 프로시저를 실행 시킬 수 없다. SQL Server는 ProcLock 리소스가 무엇을 의미하는지 모른다. SQL Server는 단지 요청된 다른 잠금들과 비교하기 위해 사용될 행을 syslockinfo 테이블에 추가한다. 프로시저 자체는 실제로 잠겨지지 않는다는 것에 주목하기 바란다. 다른 사용자나 애플리케이션이 이것이 특별한 프로시저라는 것을 모르고 애플리케이션 잠금을 얻지 않고서 MySpecialProd을 실행시키려고 한다면, SQL Server는 세션이 이 프로시저를 실행시키지 못하도록 막지 않을 것이다.
이 프로시저들에서 사용된 리소스 이름은 255 문자 길이 한도 내에서 어떤 식별자이든지 될 수 있다. 동일한 리소스에 대해 다른 요청들과의 호환성을 검사하기 위해 사용될 수 있는 잠금 모드들은 Shared, Update, Exclusive, IntentExclusive, IntentShared이다. 디폴트 모드는 없다. 세번째 매개 변수인 잠금 소유자로 사용할 수 있는 값들은 transaction(디폴트)과 session이다. 소유자가 transaction인 잠금은 사용자 정의 트랜잭션에서 얻어져야 하고, 이것은 sp_releaseapplock을 호출할 필요 없이 트랜잭션이 끝날 때 자동으로 해제될 것이다. 소유자가 session인 잠금은 세션이 종료될 때에만 자동으로 해제될 것이다.
잠금 리소스 식별하기
잠금 관리자가 요청된 잠금이 허용될 수 있는지를 알아내려고 할 때, 상충되는 모드의 잠금이 이미 존재하는지 알아내기 위해 syslockinfo테이블을 검사한다. 잠금 관리자는 데이터베이스 ID(dbid)와 개체 ID(objid)와 잠긴 리소스의 형식과 잠금에 의해 참조되는 특정 리소스에 대한 설명을 봄으로써 잠금을 비교한다. 잠금 관리자는 리소스 설명의 의미에 대해 아무 것도 모른다. 잠금 관리자는 단순히 일치하는 것을 찾기 위해 잠금 리소스에 관한 문자열을 비교한다. 일치하는 것을 찾으면 리소스가 이미 존재한다는 것을 알 수 있다.
SQL Server에서 잠글 수 있는 리소스들
리소스 축약어 리소스의 내부코드 리소스 기술 예
데이터 베이스 DB 2 없다. 잠긴 모든 리소스에 대해서 데이터베이스는 항상 dbid컬럼에 나타난다.
테이블 TAB 5 테이블ID 261575970 (sp_lock은 리소스 기술 컬럼에 있는 테이블 ID를 보여주는 것이 아니라, 자신의 컬럼에 있는 테이블 ID를 보여준다.
익스텐트 EXT 8 익스텐트의 첫번째 페이지의 파일번호:페이지번호 1:96
페이지 PAG 6 실제 테이블이나 인덱스 페이지의 파일번호:페이지번호 1:104
인덱스키 KEY 7 모든 키 구성 요소들과 위치 지정자로부터 유도된 해싱 값. C1컬럼과 C2컬럼에 인덱스가 있는 클러스터되지 않은 인덱스에 대해서 해싱 값은 c1,c2,RID로부터 만들어진 값을 포함할것이다. Ac0001a10a00
인덱스의 키범위 KEY 7 인덱스 키와 동일 Ac0001a10a00
행 RID 9 실제 행의 파일번호:페잊번호:슬롯번호 1:161:3
애플리케이션 APP 10 잠금 이름으로부터 유도된 해싱 값 MyPr8adaea5f
잠금 지속 기간
잠금이 유지되는 시간의 길이는 주로 잠금 모드와 사용중인 트랜잭션 격리 수준에 따라 달라진다. SQL Server의 디폴트 격리 수준은 Read Committed이다. 이 격리 수준에서 SQL Server가 공유 잠금으로 잠긴 데이터를 읽고 처리하자마자 이 공유 잠금이 해제된다. 단독 잠금은 트랜잭션 끝까지 유지된다. 업데이트 잠금도 트랜잭션 끝까지 유지된다. 트랜잭션 격리 수준이 Repeatable Read나 Serializable이면 공유 잠금의 지속 기간은 단독 잠금과 동일하다. 즉, 트랜잭션이 끝날 때가지 공유 잠금이 해제되지 않는다. Repeatable Read나 Serializable이면 공유 잠금의 지속 기간은 단독 잠금은 동일하다. 즉, 트랜잭션이 끝날 때까지 공유 잠금이 해제되지 않는다.
트랜잭션 격리 수준을 변경할 뿐 아니라 쿼리 힌트를 사용함으로써 잠금 지속 기간을 제어할 수 있다.
잠금 소유권
잠금 지속 기간은 잠금 소유권에 의해 영향을 받을 수 있다. 세 가지 형식의 잠금 소유자가 있다. 이것들은 트랜잭션, 커서, 세션이다. Syslockinfo 테이블의 req_ownertype컬럼을 통해 이것들을 볼 수 있다.(sp_lock에는 나타나지 않는다) req_ownertype값이 1이면 트랜잭션이 잠금을 소유한 것이고 이 잠금의 지속 기간은 앞 섹션에서 설명된 것과 같다. 실제로, 잠금에 대해 설명한 대부분의 내용은 트랜잭션이 소유한 잠금들에 관한 것이다. Req_ownertype 값이 2이면 커서가 잠금을 소유한 것이다. SCROLL_LOCK잠금 모드를 사용하여 커서를 열었다면, 다음 행을 불러오거나 커서가 닫힐 때까지 모든 행에서 커서 잠금이 유지된다. 다음 FETCH 전에 트랜잭션이 커밋되더라도, 커서 잠금은 해제되지 않는다.
Req_ownertype값이 3이면 세션이 잠금을 소유한 것이다. 세션 잠금은 트랜잭션의 범위 바깥에 있는 프로세스를 위해 얻어지는 것이다.
잠금 보기
현재 시스템에 존재하는 잠금과 대기중인 잠금들을 보려면 syslockinfo 시스템에 테이블을 조사하거나 sp_lock 시스템 저장 프로시저를 실행시킨다. Syslockinfo 테이블은 실제로 시스템 테이블이 아니다. 잠금이 디스크에 유지되지 않기 때문에 syslockinfo 테이블도 디스크에 유지되지 않는다. 대신, syslockinfo가 쿼리될 때마다 이것은 잠금 관리자의 현재 잠금 카운트에 근거한 테이블 형식으로 존재한다. 잠금 활동을 보는 다른 방법은 SQL Server 엔터프라이즈 관리자의 그래픽 환경에서 잠금 상태를 보는 것이다.
어떤 경우에는 sp_lock의 출력 결과량이 매우 많을 수 있다. 한두 프로세스 ID값을 지정함으로써 이 출력 결과를 줄일 수 있다. 이렇게 하면 sp_lock은 이 프로세스들에 의해 유지되는 잠금들만을 보여줄 것이다. @@spid 시스템 함수를 사용함으로써 특정 연결에 대한 프로세스 ID를 알수 있다. Sp_lock을 실행시키고 현재 연결만을 지정할 수 있다.
EXEC sp_lock @@spid
그러나 출력 결과를 현재 연결의 잠금들로 제한하더라도, 이따금 원하는 것 이상의 출력 결과들이 얻어질 수 있다. SQL Server는 이 변환을 수행하기 위해 master 데이터베이스의 spt_values 테이블을 조회 테이블로 사용한다. 여러분이 serializable 격리 수준에 있다면 tempdb에 있는 임시 테이블뿐 아니라 master 데이터베이스에 있는 이 테이블에서도 잠금이 유지될 수 있다. 추가로 있는 이 잠금들로 인해 데이터에 대한 잠금을 이해하는 데 어려움이 있을 수 있다.
READ COMMITTED SELECT 잠금보기
USE PUBS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM authors
WHERE au_lname = 'Ringer'
EXEC sp_lock
COMMIT TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
55 5 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
dbid가 1인 것은 master DB인데, 앞으로는 생략될 것이다.
여기서 authors의 dbid는 5이다. DB에 대해 공유잠금만 걸려 있는 것을 볼수 있다.
기본적으로 공유 잠금은 데이터를 읽자마자 해제되기 때문에 sp_lock이 실행될 시점에는 이 잠금이 더 이상 유지되지 않는다.
REPEATABLE READ SELECT 잠금보기
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM authors
WHERE au_lname = 'Ringer'
EXEC sp_lock
COMMIT TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
….
55 5 1977058079 2 KEY (62039d7395e8) S GRANT
55 5 1977058079 1 KEY (04015bb61919) S GRANT
55 5 1977058079 1 PAG 1:179 IS GRANT
55 5 1977058079 2 PAG 1:195 IS GRANT
55 5 1977058079 1 KEY (1201b4159b48) S GRANT
55 5 1977058079 2 KEY (6e021955d8e9) S GRANT
55 5 1977058079 0 TAB IS GRANT
…
authors 테이블이 클러스터된 인덱스를 갖고 있기 때문에 데이터 행들이 모두 잎 수준에 있는 인덱스 행들이다. 각 행들에 있는 잠금들은 행 잠금 대신 키 잠금으로 표시된다. 이것들은 클러스터되지 않은 인덱스의 잎 수준에 있는 키 잠금들이기도 하다. Authors 테이블에서 클러스터되지 않은 인덱스는 au_lname 컬럼에 있고, 이것은 지정된 행들을 찾기 위해 탐색되고 있는 인덱스이다. Indid 값이 1인 경우는 데이터 행이고, 클러스터되지 않은 인덱스 행의 Indid 값은 2이다. 트랜잭션 격리 수준이 Repeatable read이기 때문에 트랜잭션이 끝날때까지 공유 잠금이 유지된다. 두 개의 행들과 두 개의 인덱스 행들은 공유(S)잠금을 갖고, 데이터 및 인덱스 페이지와 테이블 자체는 내재된 공유(IS)잠금을 갖는다.
Serializable SELECT 잠금보기
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM authors
WHERE au_lname = 'Ringer'
EXEC sp_lock
COMMIT TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
55 5 1977058079 2 KEY (62039d7395e8) RangeS-S GRANT
55 5 1977058079 1 KEY (04015bb61919) S GRANT
55 5 1977058079 1 PAG 1:179 IS GRANT
55 5 1977058079 2 PAG 1:195 IS GRANT
55 5 1977058079 1 KEY (1201b4159b48) S GRANT
55 5 1977058079 2 KEY (6e021955d8e9) RangeS-S GRANT
55 5 1977058079 0 TAB IS GRANT
55 5 1977058079 2 KEY (4903312f82bf) RangeS-S GRANT
Serilizable 격리 수준에서 유지되는 잠금은 Repetable Read 격리 수준에서 유지되는 잠금과 거의 동일하다. 주된 차이점은 잠금의 모드이다. RangeS-S는 키 자체에 있는 잠금뿐 아니라 키 범위 잠금을 나타낸다. 앞 부분 RangeS는 잠금을 유지하고 있는 키와 이전 키 사이의 키 범위들에 있는 잠금이다. 키 범위 잠금은 다른 트랜잭션들이 이 쿼리 조건을 만족하는 새 행들을 테이블에 삽입할 수 없게 방지한다. 즉, last name이 Ringger인 어떤 새 행도 삽입될 수 없다. 키 범위 잠금은 au_lname, au_fname(Indid=2)에 있는 클러스터 되지 않은 인덱스의 범위에서 유지된다. 왜냐하면, 이것이 조건에 맞는 행들을 찾기 위해 사용된 인덱스이기 때문이다. 클러스터되지 않은 인덱스에서 서로 다른 세 가지 범위가 잠길 필요가 있기 때문에 이 인덱스에서 세 가지 키 잠금들이 있다. SQL Server는 인덱스의 첫번째 Ringer 이전의 키에서부터 첫번째 Ringer 이전의 키에서부터 첫번째 Ringer까지의 범위를 잠가야 하고, Ringer의 두 인스턴스 사이에 있는 범위를 잠가야 하고, 두번째 Ringer와 다음 키 사이의 범위를 잠가야 한다.(실제로, Ringer와 이전 키 Panteley 사이 그리고 Ringer 다음 키 Smith 사이에서 어떤 것도 삽입될 수 없다. 예를 들면 name이 Pike나 Singh인 저자를 삽입할 수 없다.)
READ COMMITTED UPDATE 잠금보기
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE authors
SET contract = 0
WHERE au_lname = 'Ringer'
EXEC sp_lock
ROLLBACK TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
55 5 1977058079 1 KEY (04015bb61919) X GRANT
55 5 1977058079 1 PAG 1:179 IX GRANT
55 5 0 0 DB S GRANT
55 5 1977058079 1 KEY (1201b4159b48) X GRANT
55 5 1977058079 0 TAB IX GRANT
클러스터된 인덱스의 잎 수준에 있는 두 행들은 X잠금(단독잠금)으로 잠기고, 페이지와 테이블은 IX잠금으로 잠긴다. SQL Server는 업데이트될 행들을 찾는동안 실제로 업데이트 잠금을 얻는다. 그러나 실제 업데이이트가 수행될 때 이 잠금들이 X잠금으로 변환되고, sp_lock이 실행되는 시점에는 업데이트 잠금이 사라진다. 실제로 쿼리 힌트를 사용하여 업데이트 잠금을 강제로 유지시키지 않는 한 sp_lock의 출력 결과에서 이것들을 볼 수는 없다.
Serilizable 수준에서 인덱스를 사용한 UPDATE 잠금보기
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE authors
SET contract = 0
WHERE au_lname = 'Ringer'
EXEC sp_lock
ROLLBACK TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
55 5 1977058079 2 KEY (62039d7395e8) RangeS-U GRANT
55 5 1977058079 1 KEY (04015bb61919) X GRANT
55 5 1977058079 1 PAG 1:179 IX GRANT
55 5 0 0 DB S GRANT
55 5 1977058079 2 PAG 1:195 IU GRANT
55 5 1977058079 1 KEY (1201b4159b48) X GRANT
55 5 1977058079 2 KEY (6e021955d8e9) RangeS-U GRANT
55 5 1977058079 0 TAB IX GRANT
55 5 1977058079 2 KEY (4903312f82bf) RangeS-U GRANT
여기서도 키 범위 잠금이 “관련 행들을 찾기 위해 사용된 클러스터되지 않은 인덱스”에 있다는 것에 주목해야 한다. 범위 간격 자체는 삽입을 방지하기 위해 공유잠금만을 필요로 하지만, 다른 프로세스들이 검색된 키들을 업데이트할 수 없도록 검색된 키들은 U 잠금을 갖는다. 테이블 자체(Indid = 1 클러스터인덱스)에 있는 키들은 실제 변경 작업이 수행될 때 단독 잠금을 얻는다.
Serilizable 수준에서 인덱스를 사용하지 않고 UPDATE 잠금보기
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE authors
SET contract = 0
WHERE state = 'UT'
EXEC sp_lock
ROLLBACK TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
55 5 1977058079 1 KEY (04015bb61919) RangeX-X GRANT
55 5 1977058079 1 KEY (02010b688383) RangeS-U GRANT
55 5 0 0 DB S GRANT
55 5 1977058079 1 KEY (1001898f02b5) RangeS-U GRANT
55 5 1977058079 1 KEY (0801c4f7a625) RangeS-U GRANT
55 5 1977058079 1 PAG 1:179 IX GRANT
55 5 1977058079 1 KEY (0e01733a43ca) RangeS-U GRANT
55 5 1977058079 1 KEY (02014f0bec4e) RangeS-U GRANT
55 5 1977058079 1 KEY (0501dc9d152f) RangeS-U GRANT
55 5 1977058079 1 KEY (0b018636f9dc) RangeS-U GRANT
55 5 1977058079 1 KEY (1201b4159b48) RangeX-X GRANT
55 5 1977058079 1 KEY (0401c963b692) RangeS-U GRANT
55 5 1977058079 1 KEY (0c01f170d106) RangeS-U GRANT
55 5 1977058079 1 KEY (0601d808a263) RangeS-U GRANT
55 5 1977058079 1 KEY (070199a50c2d) RangeS-U GRANT
55 5 1977058079 1 KEY (0701a9d28094) RangeS-U GRANT
55 5 1977058079 1 KEY (10018e6baefb) RangeS-U GRANT
55 5 1977058079 1 KEY (100198c3f985) RangeS-U GRANT
55 5 1977058079 1 KEY (ffffffffffff) RangeS-U GRANT
55 5 1977058079 0 TAB IX GRANT
55 5 1977058079 1 KEY (0c019fc272ba) RangeS-U GRANT
55 5 1977058079 1 KEY (100125852812) RangeS-U GRANT
55 5 1977058079 1 KEY (06013d38d450) RangeS-U GRANT
55 5 1977058079 1 KEY (0d01ac7ad21d) RangeS-U GRANT
55 5 1977058079 1 KEY (0101aedb232b) RangeS-U GRANT
55 5 1977058079 1 KEY (0601989a35e6) RangeS-U GRANT
55 5 1977058079 1 KEY (0901ce1ba5f0) RangeS-U GRANT
여기에 있는 잠금은 앞 예제에 있는 것과 비슷한다. 단, 모든 잠금들이 테이블 자체(Indid = 1)에 있다는 것이 다르다. 테이블 전체에 있는 클러스터된 인덱스를 스캔해야 하기 때문에 모든 키들이 초기에 RangeS-U잠금을 얻고, 두 행이 변경 되었을 때 이 키들에 있는 잠금들이 RangeX-X잠금으로 변환되었다.
테이블 생성시 잠금
테이블이 생성될 때, SQL Server는 이 테이블에 관한 정보를 기록하기 위해 sysobjects와 syscolumns에서 잠금을 얻는다. 또한, 익스텐트(EXT) 잠금과 새 테이블에 있는 스키마 수정 잠금도 나타난다. 익스텐트 ID는 1:192형시으로 나타나느데 이것은 1번 파일에 있는 192번 페이지가 익스텐트의 첫번째 페이지라는 것을 의미하고, 테이블이 생성되고 있는 동안 이 익스텐트에 있는 7개 페이지(193-199)가 모두 단독 잠금 상태로 된다.
잠금 호환성
동일한 개체에 대해 한 잠금이 유지되고 있는 동안 다른 잠금이 허용될 수 있다면 이 두 잠금들은 호환된다. 반면, 개체에 대해 요청된 잠금이 현재 유지되고 있는 잠금과 호환되지 않으면, 요청한 연결이 잠금을 기다려야 한다. 예를 들어, 페이지에 공유 페이지 잠금이 존재한다면, 동일한 페이지에 대해 공유 페이지 잠금을 요청하는 다른 프로세스가 잠금을 얻을 수 있다. 이것은 두 잠금 형식이 호환되기 때문이다. 그러나 동일한 페이지에 대해 단독 잠금을 요청하는 프로세스는 잠금을 얻지 못할 것이다. 이것은 단독 잠금이 “이미 유지되고 있는 공유 잠금”과 호환되지 않기 때문이다.
SQL Server의 잠금호환성(O는 호환가능, X는 호환불가)
요청 모드 기존허용모드
IS S U IX SIX X Sch-S Sch-M BU
IS O O O O O X O X X
S O O O X X X O X X
U O O X X X X O X X
IX O X X X X X O X X
SIX O X X X X X O X X
X X X X X X X O X X
Sch-S O O O O O O O X O
Sch-M X X X X X X X X X
BU X X X X X X O X O
잠금 호환성은 서로 다른 리소스들의 잠금(예:테이블 잠금과 페이지 잠금) 사이에도 관여한다. 테이블이 여러 페이지들로 구성되어 있기 때문에 테이블과 페이지는 분명히 암시적으로 계층 구조를 갖는다. 테이블의 한 페이지에 단독 페이지 잠금이 유지되면 다른 프로세스가 이 테이블에 대해 공유 테이블 잠금조차 얻을 수 없다. 이 계층 구조는 내재된 잠금을 통해 보호된다. 단독 페이지 잠금이나 업데이트 페이지 잠금이나 내재된 단독 페이지 잠금을 얻고 있는 프로세스는 먼저 테이블에서 내재된 단독 잠금을 얻는다. 이 내재된 단독 테이블 잠금은 다른 프로세스들이 이 테이블에서 공유 테이블 잠금을 얻지 못하게 한다.(내재된 단독 잠금과 공유 잠금은 호환되지 않는다.)
이와 비슷하게 공유 행 잠금을 얻고 있는 프로세스는 먼저 테이블에 대해 내재된 공유잠금을 얻어야 한다. 이 내재된 공유 잠금은 다른 프로세스들이 단독 테이블 잠금을 얻지 못하게 방지한다. 또는, 단독 테이블이 잠금이 이미 존재하면 내재된 공유 잠금이 허용되지 않고, 단독 테이블 잠금이 해제될 때까지 공유 페이지 잠금이 기다려야 한다. 내재된 잠금이 없다면 A 프로세스가 테이블의 한 페이지를 단독 페이지 잠금으로 잠그고 B 프로세스가 동일한 테이블에서 단독 테이블 잠금을 얻은 후 테이블 전체(A프로세스가 단독으로 잠금 페이지 포함를 변경할 권한을 가졌다고 생각할지 모른다.
두 잠금이 호환되더라도, 호환되지 않은 잠금이 기다리고 있다면 두번째 잠ㄱ므을 요청한 프로세스가 여전히 기다려야 할지도 모른다. 예를 들어, A프로세스가 공유 페이지 잠금을 유지한다고 가정하자. 공유 페이지 잠금과 단독 페이지 잠금이 호환되지 않기 때문에 B 프로세스는 단독 페이지 잠금을 요청하고 기다려야한다. C 프로세스닌 이미 A프로세스가 유지하고 있는 공유 페이지 잠금과 호환되는 공유 페이지 잠금을 요청한다. 그러나 공유 페이지 잠금은 즉시 허용될 수 없다. 잠금 대기열에서 B프로세스가 C프로세스보다 앞에 있기 때문에 C프로세스는 공유 페이지 잠금을 기다려야 한다.
내부 잠금 아키테쳐
잠금은 디스크에 있는 구조가 아니다. 잠금이 디스크에 없는 것은 잠금 동작들을 위해 디스크 I/O 동작을 수행하는 것이 너무 느리기 때문이다. 데이터 페이지나 테이블 헤더에서 직접 잠금 필드를 찾을 수 없을 것이다. 잠금은 내부 메모리 구조이다. 이것은 SQL Server를 위해 사용된 메모리의 일부를 소비한다. 잠긴 각 데이터 리소스들은 데이터베이스와 잠금 형식과 잠긴 리소스를 기술하는 정보를 추적하기 위해 64바이트 메모리를 필료오 한다. 잠금을 유지하고 있는 각 프로세스들도 32바이트로 된 잠금 소유자 블록을 갖고 있어야 한다. 한 트랜잭션이 여러 개의 잠금 소유자 블록을 가질 수 있다. 또한, 한 잠금이 많은 잠금 소유자 블록을 가질 수 있다. 마지막으로, 잠금을 기다리고 있는 각 프로세스들은 32바이트로 된 잠금 대기자 블록을 갖는다. 잠금 소유자 블록과 잠금 대기자 블록이 동일한 구조를 갖기 때문에 필자는 “잠금 소유자 블록” 용어를 사용하여 이 두 가지를 모두 나타낼 것이다.
행 수준 잠금과 페이지 수준 잠금
행 수준 잠금이 페이지 수준 잠금보다 더 좋은지 아니면 페이지 수준 잠금이 더 좋은 논쟁하는 것은 오래된 논쟁 거리였다.
버전 7.0 이전에는 SQL Server가 잠글 수 있었던 가장 작은 데이터 단위가 페이지였다. 많은 사람들이 페이지 전체를 잠그는 동안 좋은 동시성을 유지할 수 없다고 주장했지만, 페이지 수준 잠금만을 사용하여 작성되고 배포된 많은 애플리케이션들이 있었다. 애플리케이션이 잘 설계되고 튜닝되었다면 동시성은 문제가 되지 않았고, 일부 애플리케이션들은 수백 개의 활성 사용자 연결을 지원 하면서도 좋은 응답 시간을 가지고 있었다. 그러나 SQL Server 7.0에서 페이지 크기가 2K에서 8KB로 바뀌면서 문제가 페이지 수준 잠금의 문제가 생기게 되었다. 페이지 전체를 잠그는 것은 이전 버전에서보다 4배의 데이터를 잠그는 것을 의미한다. SQL Server는 7.0부터 완전 행 수준 잠금을 구현했기 때문에, 더 큰페이지 크기에서 낮은 동시성으로 인해 발생할 수 있는 문제들은 실제로 문제가 되지 않아야 했다. 그러나 잠금은 자유가 아니다. 잠금을 관리하는 32바이트 구조이고, 잠금을 유지하고 있는 각 프로세스들과 잠금을 기다리고 있는 각 프로세스들마다 32바이트씩 필요하다는 것을 상기하기 바란다. 모든 행에 대해 잠금이 필요하고 백만개의 행을 스캔한다면, 이 한 개의 프로세스에서 잠금을 유지하기 위해 30MB 이사의 RAM이 필요하다.
메모리 사용 문제를 생각지 않더라도 잠금은 프로세스 집약적 동작이다. 잠금을 관리하려면 많은 것들을 기록해야 한다. 내부적으로 SQL Server는 스핀 잠금이라고 불리는 가변운 mutex를 사용하여 리소스를 보호하고, 래치를 사용하여 잎 수준이 아닌 인덱스 페이지들을 보호한다. 이것들을 사용하여 성능을 최적화함으로써 완전히 잠가야 하는 오버헤드를 피할 수 있다. 한 페이지에 50개의 행이 들어 있고 이것들이 모두 사용된다면, 50개를 관리하는 것보다 페이지에서 한 개의 잠금을 관리하는 것이 더 효과적일 것이다. 이것이 페이지 잠금의 이점이다.(즉, 만들어지고 관리되어야 하는 잠금 구조의 수가 줄어든다.)
두 프로세스가 각각 서로 다른 행들을 업데이트할 필요가 있고 이 행들 중의 일부가 우연히 동일한 페이지에 존재한다면, 한 프로세스의 페이지 잠금이 해제될때까지 다른 프로세스가 기다려야 한다. 이 경우에 여러분이 페이지 수준 잠금 대신 행 수준 잠금을 사용한다면 다른 프로세스가 기다릴 필요가 없다. 작은 잠금 단위를 사용하면 각 프로세스들이 서로 다른 행들을 사용하기 때문에 충돌이 일어나지 않는다. 이것은 행 수준 잠금의 이점이다. 이 장점들 중에서 어떤 것이 더 중요하겠는가? 이것은 간단히 결정할 수 있는 문제가 아니고 애플리케이션과 데이터에 따라 달라진다.
Sp_indexoption 저장프로시저는 인덱스 내에서 잠금 단위를 수동으로 제어할 수 있게 해준다. 또한, 이 프로시저는 인덱스 내에서 페이지 잠금이나 행 잠금을 허용하지 않을 수 있게 해준다. 인덱스에 대해서만 이 옵션을 사용할 수 있기 때문에 힙의 데이터 페이지 내에서 잠금을 제어하는 방법이 없다.(그러나, 테이블이 클러스터된 인덱스를 가지고 있다면 데이터 페이지는 인덱스의 일부이고 sp_indexoption설정에 의해 영향을 받는다.) 인덱스 옵션은 각 테이블이나 인덱스에 대해 개별적으로 설정된다. 두 가지 옵션 AllowRowLock와 AllowPageLock는 초기에 모든 테이블과 인덱스에 대해서 TRUE로 설정되어 있다. 테이블에 대해 이 옵션들이 모두 FALSE로 설정되면 완전 테이블 잠금만이 허용된다.
SQL Server는 런타임 시에 추기에 행을 잠글 것인지 페이지를 잠글 것인지 테이블 전체를 잠금 것인지를 결정한다. 행(또는, 키) 잠금이 주로 선호된다. 잠금 형식은 스캔될 행 및 페이지의 수와 페이지에 있는 행의 수와 사용중인 격리 수준과 진행중인 업데이트 동작과 시스템에서 메모리를 필요로 하는 사용자의 수에 근거하여 선택된다.
잠금 수준 조정
SQL Server는 필요할 때 행 잠금이나 키 잠금이나 페이지 잠금을 자동으로 테이블 잠금으로 바꾼다. 이 잠금 수준 조정은 시스템 리소스를 보호하고(즉, 시스템이 잠금을 유지하기 위해 너무 많은 메모리를 사용하지 않도록 방지해주고), 효율성을 증대시킨다. 예를 들면, 쿼리가 많은 행 잠금을 얻은 후 잠금 수준은 테이블 잠금으로 바뀔 수 있다. 테이블에 있는 모든 행들이 방문되어야 한다면 아마도 많은 행 잠금들을 얻는 것보다 한 개의 테이블 잠금을 얻는 것이 더 나을 것이다. 한 개의 테이블 잠금이 얻어지고 많은 행 잠금들이 해제된다. 테이블 잠금으로 바뀌면 잠금 오버헤드가 줄어들고 시스템에서 잠금 부족 현상일 일어나지 않게 예방된다. 잠금 구조에 사용할 수 있는 메모리의 양이 유한이기 때문에 이따금 잠금에 사용되는 메모리가 일정한 한계 내에 머무르도록 하기 위해 잠금 수준 조정이 필요한다.
한 트랜잭션에 대한 잠금 카운트가 1250을 초과할 때 또는 한 인덱스나 테이블 스캔에 대한 잠금 카운트가 765를 초과할 때, 잠금 관리자는 시스템의 모든 잠금들을 위해 얼마나 많은 메모리가 사용되고 있는지 알아본다. 메모리 풀의 40%이상이 잠금을 위해 사용되고 있다면 SQL Server가 여러 개의 페이지 잠금이나 키 잠금이나 RID 잠금을 테이블 잠금으로 바꾸려고 시도한다. SQL Server는 트랜잭션에 의해 부분적으로 잠기고 가장 많은 잠금 수를 유지하고 있는 테이블을 찾으려고 한다. 다른 프로세스가 동일한 테이블에 있는 다른 RID나 키나 페이지에서 호환되지 않은 잠금을 유지하고 있다면 여러 RID 잠금이나 키 잠금이나 페이지 잠금들은 테이블 잠금으로 바뀔 수 없다 가능한 모든 잠금 수준 조정이 이루어지거나 잠금에 사용된 전체 메모리가 40%이하로 떨어질 때까지 SQL Server는 동일한 트랜잭션에 의해 부분적으로 잠긴 다른 테이블들을 계속해서 찾을 것이다. SQL Server는 결코 페이지 잠금으로 바꾸지 않는다는 것에 주목하기 바란다. 잠금 수준 조정의 결과는 항상 테이블 잠금이다.
위로 가기
와 같은)에서 정렬된 뷰를 생성하는 방법에 대한 질문을 받는다. 필자가 비록 이런 정렬에 있어서의 제약사항을 피할 수 있는 백도어를 설명하고 있기는 하지만 백도어의 사용은 가급적 피할 것을 권고한다. ORDER BY 절을 지정한 뷰에서 ORDER BY 절을 제거하고 뷰를 액세스하는 쿼리문에 ORDER BY 절을 사용할 것을 권고한다.
