Wednesday, October 2, 2013

UPDATE lock on table

Tom,


you can try




create table t1(cola int primary key, colb int, colc int);
create index t1colb on t1(colb);
insert t1(cola, colb, colc) values (1, 2, 3);
insert t1(cola, colb, colc) values (2, 3, 4);
insert t1(cola, colb, colc) values (3, 4, 5);
go
Set Transaction Isolation Level READ COMMITTED
go

begin tran;
Update t1 Set colc = @@SPID Where cola = 1;


--and from other window update on some other row won't be allowed:
UPDATE t1 set colc=@@SPID where cola=3
--select works as expected:
SELECT * FROM t1

Why it can't update the row, which hasn't been locked?

No comments:

Post a Comment