Due to some post this morn I discovered Iknow far too little about locks. So
I went out and tried a few things.
My data : TableA col1 , col2
First : I test rowlockling VS readpast
I lock row 2 by doing an update on it, leaving an uncommited transaction.
Then I do a select(readpast) on the table I get row 1 and 3 which I'm happy
When I update row 2 , with the new value the same as current value(update
set ... col1 = 'row2' where col1 = 'row2') , still leaving the tran
uncommited, I now do the select and get row 1 to 3 including the row being
I don't understand this, I mean sure the value didn't change but it could
stil change seeing the transaction is not yet commited. Basically now it's a
That's question 1 finished.
Same table and data.
I do a select on row2 , doing with rowlock with uncommited transaction.
Then in another instance do select on the table readpast, and I get all the
Now sure I'll get everything because select statements can't modify data so
nothing have to be locked with the rowlock.
Is there a way to lock single rows, on a table similar to rowlock for updates.
I can't see isolation levels locking single rows, it works fine for locking
I think of locking with selects similar to a library, although select only
"read" read book and then return it, is there a way to make the book (row)
not available to another person at the same time , even though the other
person will only be reading ?
if object_id('dummy') > 0
Drop procedure dummy
create procedure dummy
if object_id('dummytable') > 0
Drop table dummytable
create table dummytable (col1 int, col2 varchar(99) )
Insert into dummytable
set transaction isolation level serializable
begin tran tr1
-- update dummytable with (rowlock) set col2 ='unlock'
-- where col1 = 4
select * from dummytable with (holdlock) where col1= 2
set transaction isolation level read committed
select * from dummytable with(readpast)