2008버전에서는 락 에스컬레이션을 명시적으로 조정할 수 있는 구문이 생겼다. 직접 테스트 해보자.

--테스트데이터입력
-- SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
set nocount on 
set statistics io off

--drop table lock_test
create table lock_test
(
	id int primary key
,	name varchar(20)
);
go

begin tran
declare @i int = 1;
while(@i <= 10000)
begin
	insert lock_test 
	values(@i, left(convert(varchar(50), newid()), 20));
	set @i = @i + 1;
end
commit;
go

--auto
alter table lock_test 
set(lock_escalation = auto);
go

세션1에서 다음을 실행한다.

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6000;

세션2에서 락의 레벨을 살펴보자.

--session2
exec sp_lock

key락인 것을 확인 할 수 있다. 세션1에서 다시 다음을 실행하여 rollback 시킨 후, 다시 7000개의 row를 한 번에 업데이트 시켜보자. 이런 짓을 하는 이유는 락 에스컬레이션이 걸리게 하기 위해서다.

--session1
rollback

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 7000;

세션2에서 확인해보자.

--session2
exec sp_lock

다시 세션1에서 rollback 시킨 후 테이블의 설정을 다시 바꿔보자. 그리고 다시 트랜잭션을 발생시키자.

--session1
rollback

--table
alter table lock_test 
set(lock_escalation = table);
go

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6219;

세션2에서 확인해보자.

--session2
exec sp_lock

다시 세션1에서 rollback 후에 더 많은 Row에 락을 걸어보자.

--session1
rollback

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6220;

exec sp_lock

이제 락 에스컬레이션이 발생하지 않도록 테이블의 설정을 바꿔보자.

--session1
rollback

--disable
alter table lock_test 
set(lock_escalation = disable);
go

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6220;

--락 에스컬레이션이 일어나지 않음.

lock escalation은 5000개의 잠금을 획득시 시도된다. 만약 lock escalation이 실패하면 추가적으로 1250개의 잠금을 얻었을 때에 lock escalation을 재시도 한다.

ssis에서 데이터 조낸 구겨 넣을 때 유용하다.. -- 이재학 2011-02-15 00:34:09