What is the proper way of locking a row for read? Here's what I'm trying to do:

1. Transaction 1 finds a row it likes. It locks it from being read by
Transaction 2
2. Transaction 2 looks for a row, but does not get blocked and does not find
the row that is being processed by Transaction 1. It simply picks another
suitable row.

I imagine it involves the READPAST hint, but what else should I do? Am I
stuck with having a field in the row to mark that a transaction "owns" a
particular row at a given instance? My transaction gets a "select" followed
by an "update"

Hi Val

If T1 wants to make sure T2 can't even read the row, T1 will have to make
sure the row has a X lock, using the XLOCK hint and perhaps also the ROWLOCK
hint.

You are correct, that if T2 uses the READPAST hint, it wil skip over the row
that T1 has locked.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Val P" <ValP (AT) discussions (DOT) microsoft.com> wrote

Thanks!

A question though. Does the XLOCK disallow ROWLOCK? If I read BOL, I see the
following, which seems to apply that XLOCK cannot be combined ROWLOCK, but it
doesn't really come out and say it.

"XLOCK Use an exclusive lock that will be held until the end of the
transaction on all data processed by the statement. This lock can be
specified with either PAGLOCK or TABLOCK, in which case the exclusive lock
applies to the appropriate level of granularity. "



"Kalen Delaney" wrote:

Val

This is very easy to test for yourself.

--------------------------
use pubs

begin tran

select * from titles (xlock, rowlock)
where title_id= 'ps2091'

exec sp_lock

commit tran

---------------------


The BOL entry is unclear. ROWLOCK and XLOCK can be specified together.

I think what BOL means is that if you don't specify the granularity, the
default is ROWLOCK. I wasn't sure of that, which is why I said you 'might'
need to add the ROWLOCK hint. You'd need to actually test this on a much
bigger table to be sure that XLOCK by itself always gave you rowlocks.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Val P" <ValP (AT) discussions (DOT) microsoft.com> wrote

It looks to me like it got promoted to page locks on a larger table... (I am
assuming I'm reading this correctly)

71 5 1117247035 1 PAG 1:249 IX GRANT
71 5 1117247035 1 PAG 1:248 IX GRANT
71 5 1117247035 1 PAG 1:324 IX GRANT
....


in which case this solution is not suitable for me, since it's locking too
many potential candidates. If this is so, is there a pattern for
implementing queue tables that I should read up on? I imagine this is a
common task.




"Kalen Delaney" wrote:

IX is an intent lock, which is always acquired on the larger units. If there is an X lock on a row, the page and table will always have IX locks to prevent anyone from locking at the larger unit. You shouldn't ever have IX locks without X locks. Where are your X locks? What hints did you use?

When I ran the code below on the little tiny titles table, I got:


spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 5 0 0 DB S GRANT
51 5 2121058592 1 PAG 1:99 IX GRANT
51 5 2121058592 1 KEY (ba008abb131c) X GRANT
51 1 85575343 0 TAB IS GRANT
51 5 2121058592 0 TAB IX GRANT

This shows an X lock on the row, and IX locks on the page and table containing the row.
IX locks do not prevent other IX locks. Please read "Understanding Locking in SQL Server" in the Books Online.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Val P" <ValP (AT) discussions (DOT) microsoft.com> wrote

Be very careful using the XLOCK. You can end up with it not doing its job. Try below (two
connections):

--Connection 1
BEGIN TRAN
SELECT au_lname FROM authors WITH(XLOCK)
WHERE au_lname = 'White'


--Connection 2
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'W%'

See? Connection 2 is *not* blocked even though c1 has an exclusive lock and c2 doesn't do a dirty
read. This is an optimization in SQL Server 2000 where a scan take a look to see if the contents is
older than the oldest open transaction (applies to READ COMMITTED only). See KB 324417 for more
info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/


"Val P" <ValP (AT) discussions (DOT) microsoft.com> wrote

Kalen,

the query included xlock and rowlock on a table that had only about 12000
rows, and the ResourceID in the collection below is not an indexed key.
Readpast doesn't make any difference to the result below

begin tran
select * from AuditLog (xlock, rowlock) where ResourceID= 'xxx'
exec sp_lock
commit tran


I am thinking that I just need to reengineer this process in another way,
since this approach does not seem to be working. But I can't think of a
foolproof way of doing this that does not involve retries in some rare cases.
In the real-world using the current method I'm running out of results even
though many are available but they are page-locked by a concurent query...



"Kalen Delaney" wrote:

Well your problem is that you need an index on ResourceID. Without the
index sql server must scan the table and you are seeing this behavior. If
you have a proper index you should be able to use:

begin tran
select TOP 1 * from AuditLog (xlock, HOLDLOCK, READPAST) where ResourceID=
'xxx'
commit tran


--
Andrew J. Kelly SQL MVP


"Val P" <ValP (AT) discussions (DOT) microsoft.com> wrote

Tibor,

I checked out the kb article, and if I'm reading it correctly, the problem
appears with two queries at different transaction isolation levels
(serializable vs. read-committed, with the second one optimizing away the
locks). In my case, concurent connections have the same isolation level, so
unless I'm missing something in the article, it should work.

Thanks for the input though, it's a good thing to keep in mind.

"Tibor Karaszi" wrote: