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
lock01.jpg

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
lock02.jpg

れ 語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
lock03.jpg

れ 語1 rollback 襷 Row 曙 蟇語企慨.
--session1
rollback

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

exec sp_lock
lock04.jpg

伎 れ錆伎 覦讌 襦 企 れ 覦蠖覲伎.
--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;

-- れ錆伎 殊企讌 .
lock05.jpg

lock escalation 5000螳 蠍 . 襷 lock escalation ろ覃 豢螳朱 1250螳 蠍 詞 lock escalation .


ssis 一危 譟磯 蟲蟆 l .. -- 伎 2011-02-15 00:34:09