摘要:MS SQL ROWLOCK
-- 實現rowlock
---------環境MS SQL 2005 ISOLATION=>READ COMMITTED
-----A1 column::ID設PK
USE TEST2
create table lp (id int primary key,[name] varchar(50))--column::ID設PK
insert into lp values(1,'a')
insert into lp values(2,'b')
begin tran ----做個pending transaction
update lp
set name='kkk'
where id=1
select * from lp
----- B1 session
select * from lp ---BLOCK
select * from lp with(nolock)---dirty read
select * from lp where id=2 --rowlock OK
delete from lp where id=2 –rowlock OK
--------A2 : there is no index
create table lpl (id int,[name] varchar(50))
insert into lpl values(1,'a')
insert into lpl values(2,'b')
begin tran -----做個pending transaction
update lpl
set name='kkk'
where id=1
select * from lpl
--B2 session
select * from lpl ---BLOCK
select * from lpl with(nolock)---dirty read
select * from lpl where id=2 --BLOCK
delete from lpl where id=2 --BLOCK
-----------A3 nonclustered index on column::ID
create table lpA (id int,[name] varchar(50))
create nonclustered index lpA_id_idx on test2.dbo.lpA(id);
insert into lpA values(1,'a')
insert into lpA values(2,'b')
begin tran -----做個pending transaction
update lpA
set name='kkk'
where id=1
select * from lpA
----- B3 session
select * from lpA ---BLOCK
select * from lpA with(nolock)---dirty read
select * from lpA where id=2 --BLOCK
delete from lpA where id=2 –rowlock OK
------A4 clustered index on column::ID
create table lpB (id int,[name] varchar(50))
create clustered index lpB_id_idx on test2.dbo.lpB(id);
insert into lpB values(1,'a')
insert into lpB values(2,'b')
begin tran -----做個pending transaction
update lpB
set name='kkk'
where id=1
select * from lpB
--------- B4 session
select * from lpB ---BLOCK
select * from lpB with(nolock)---dirty read
select * from lpB where id=2 ----rowlock OK
delete from lpB where id=2 ----rowlock OK
-------結論
判別條件的欄位要使用index
不同種類的index會有不同的rowlock狀況