serializable transaction isolation lock

Question

I have set a serializable transaction isolation lock for a transaction.But i am observing some results which are not expected.

My query is

update tabl1 set col2 = 10 where col1 > 10 and col1 < 20

Here col1 is primary key.Here rows with col1 having value 10,11,12....19,20 are locked for update/insert. In where condition i have given conditions as col1>10 and col1<20 but still rows having col1=10 and col1=20 gets locked.

If i give

update tabl1 set col2 = 10 where col1 => 10 and col1 <= 20

then rows with col2 having values from 9 to 21 gets locked.So why col1 having 9 and 21 gets locked here?

For the below query full table gets locked.Here col3 is a non-primary column.Can't i set lock if the column in where condition is not a primary key?

update tabl1 set col2 = 10 where col3 > 10 and col3 < 20

score 4 · Accepted Answer · edited May 23 '17 at 11:46

4

For key range locking SQL Server must use an index to satisfy the range condition so regarding your last query you would need an index on col3 (and possibly query hints to force its use if the plan does not use it)

Also it does not just lock the specific range in your WHERE clause. It locks keys. A range lock on a key protects the range from that key downwards to the next key so the exact range that will be blocked depends upon what keys exist in the index.

More details/links are in my answer here

edited May 23 '17 at 11:46

Community

1
1

answered Jan 07 '13 at 10:33

Martin Smith

438,706
87
741
845

But which index should be used?Clustered or non- clustered? – wincoding Jan 07 '13 at 13:37
@wincoding - It makes no difference SQL Server can take key range locks on either as long as it uses that index to resolve the `WHERE` – Martin Smith Jan 07 '13 at 13:53

serializable transaction isolation lock

1 Answers1