UPDATE lock on table

Dan,

this is not true.

update t1 set colb = 5 where colb = 4

This update will work. So, there is no lock on all keys.

The hold lock just puts the lock on all keys between 2 key values, to prevent also inserts between them. In this case when there is select on row with colb=2 it puts lock also on next record with colb=3, to get the boundaries. But all other keys are lock free.

For example:

update t1 set colb = 2 where colb = 4

this won't work since record would go between colb 2 and 3.

Tom,

thank you for your suggestion.

If I use colc as additional column and execute the following update:

begin tran;

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

there is exclusive lock on clustered index and all rows are blocked from update, not just the one with cola=1?

I have used other way.

If I add the column, which I would like to prevent from update, into my select statement, then also no other process can update that column.