lock locking locked

lock locking locked

Post by TWFs » Thu, 28 Oct 2004 23:45:10


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
row 1,row1
row 2,row2
row 3,row3

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
with.
But...
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
edited(row2).

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
dirty read.

That's question 1 finished.

Test 2.

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
data.
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
modified tables.

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 ?

The code...

if object_id('dummy') > 0
Begin
Drop procedure dummy
End

go

create procedure dummy

as

begin

if object_id('dummytable') > 0
Begin
Drop table dummytable
End


create table dummytable (col1 int, col2 varchar(99) )

Insert into dummytable
select 1,'row1'
union
select 2,'row2'
union
select 3,'row3'
union
select 4,'row4'
union
select 5,'row5'
union
select 6,'row6'
union
select 7,'row7'

end

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

commit
go
/*****************************/
set transaction isolation level read committed

select * from dummytable with(readpast)
 
 
 

lock locking locked

Post by Toby Herri » Thu, 28 Oct 2004 23:58:54

try:

SELECT * FROM table WITH (ROWLOCK, XLOCK) WHERE col1 = row2

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://www.yqcomputer.com/

 
 
 

lock locking locked

Post by TWFs » Fri, 29 Oct 2004 00:23:04

Thanks for the reply,

Tried it, without luck. I think the bit of locking that come into play is
this,/*
. This lock prevents others from reading or updating the table and is held
until the end of the statement or transaction. - BOL , */ end of
transaction... select finished return data so it's done.

I tried your code with read uncommited, commited, with both I'm stil able to
access the same row even though it's "lock" for reading and updating.

serialization , lock my transaction completely out.

What I want to do is lock a row with a select statement, then do another
select on same table and see all the records in the table except the locked
one.
Think I should stick with update locking.
 
 
 

lock locking locked

Post by JXSter » Fri, 29 Oct 2004 01:47:08

On Wed, 27 Oct 2004 07:45:10 -0700, Mal XXXX@XXXXX.COM >


Very interesting, I've always wondered about the no-change change
cases but never did try it, thanks for doing it for me!


I've always used "select * from mytable with (updlock)" inside of an
uncommitted trx.


What you want is called "pessimistic locking", and it's out of fashion
in favor of "optimistic locking", in which you don't lock anything
explicitly, but the system looks to see (by various magics) if the
record you think you're updating has been changed since you read it.
Various arguments suggest optimistic locking is more system-efficient,
at a miniscule cost in late-detected collisions.

Another aspect that may be involved here is the "timestamp" field, ...
hmm, no, a quick experiment shows it has the same behavior, never
mind. And my (updlock) acts as did your updates.

Note that if one user gets an (updlock) like this, and a second user
tries for the same (updlock) the second user will be queued - or you
can set the lock_timeout short and get an error timeout instead. This
is a cooperative form of pessimistic locking, I've used this to do
pessimistic locking and it works well on SQLServer.

HOWEVER, if you do make an update and continue to hold a "checked-out"
record for several seconds (or minutes or hours), note that it will
block out (cause to queue) other users who simply want to scan the
table looking for unrelated records -- that is, unless you lock by an
indexed field, SQLServer has some poorly-documented locking
sensitivities along those lines. Mostly good things, but, well,
complicated.

Note also that Yukon will add a whole new set of (optional?)
locking/concurrency behaviors having to do with "lookaside" values,
duplicating (?) Oracle capabilities.

Your locking enthusiast,

J.
 
 
 

lock locking locked

Post by David Gugi » Fri, 29 Oct 2004 08:43:49

> What I want to do is lock a row with a select statement, then do

Is this possible with SQL Server? If you request all the rows in the
table, I think one of three things will occur:

1- If some rows are exclusively locked (from whatever means), your
Select statement wait until the locks are freed and you'll get
everything (if you don't use read uncommitted)
2- If some rows are exclusively locked (from an update/insert/delete
op), your Select statement runs and you get everything including dirty
rows or missing rows in the case of a delete (if you use read
uncommitted - nolock)
3- If some rows have shared locks, your Select statement runs and you
get everything

I'm not sure you can read the data and skip locked pages/rows. But maybe
I'm blowing smoke and need more coffee...

--
David Gugick
 
 
 

lock locking locked

Post by Hugo Korne » Fri, 29 Oct 2004 19:25:08

n Wed, 27 Oct 2004 07:45:10 -0700, Mal .mullerjannie wrote:

(snip)

Hi Mal,

Interesting! And it appears you've stumbled over a bug (or I misunderstand
the documentation!)



This should not be the case (and if it is working as intended, MS should
definitely put it very explicitly in BOL, using the biggest font
available).

Using the dummytable you provided, I was able to reproduce this on my
system (*). I also tried what happened if a row is changed, then changed
back to the original value - it won't reappear in the output of the second
connection (with readpast).

(Extended repro script to follow)

(*) The system I used to reproduce this on:
SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)



This is correct behaviour. Even in the serializable isolation level you
used in your script, a SELECT will still issue a shared lock, not an
exclusive lock. Another SELECT should be able to see this row, with or
without (readpast) hint.

What you are trying to achieve requires an (xlock) locking hint - this
tells SQL Server to take an exclusive lock on the row instead of the
default shared lock.


I played around with your dummytable some more and came up with the
following rather interesting repro script:

(From connection 1:)

set transaction isolation level read committed

begin tran tr1
update dummytable with (rowlock)
set col2 = 'row4' --original value
where col1 = 4
select * from dummytable with (rowlock, xlock)
where col1= 2

(From connection 2:)

set transaction isolation level read committed

exec sp_lock
select * from dummytable with(readpast)

*** All rows are shown; the locks seem to be disregarded, though sp_lock
does show them

(From connection 1:)

update dummytable with (rowlock)
set col2 = 'something else'
where col1 = 4

(From connection 2:)

exec sp_lock
select * from dummytable with(readpast)

*** Now, both row 2 and row 4 are skipped - as intended. Note that the
output of sp_lock is completely unchanged!

(From connection 1:)

update dummytable with (rowlock)
set col2 = 'row4' --restore original value
where col1 = 4

(From connection 2:)

exec sp_lock
select * from dummytable with(readpast)

*** Output doesn't change from before. Obviously, problem is not because
value is same as before, but because it enver actually changed.

(From connection 1:)

commit tran


I didn't search Knowledge Base to find if this is a known issue. If it
isn't, you might consider opening a case with Microsoft. As far as I know,
the cost will be refunded if they confirm this to be a bug that's not yet
documented at the knowledge base.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)