read locks

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@.discussions.microsoft.com> wrote in message
news:DCEE6BF0-EE47-48AD-A46E-438AB382D1D5@.microsoft.com...
> 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"
>
>|||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 i
t
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:

> 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 ROWLO
CK
> hint.
> You are correct, that if T2 uses the READPAST hint, it wil skip over the r
ow
> that T1 has locked.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Val P" <ValP@.discussions.microsoft.com> wrote in message
> news:DCEE6BF0-EE47-48AD-A46E-438AB382D1D5@.microsoft.com...
>
>|||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@.discussions.microsoft.com> wrote in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6@.microsoft.com...
> 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:
>|||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:

> 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@.discussions.microsoft.com> wrote in message
> news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6@.microsoft.com...
>
>|||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 prev
ent 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 containi
ng the row.
IX locks do not prevent other IX locks. Please read "Understanding Locking i
n SQL Server" in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Val P" <ValP@.discussions.microsoft.com> wrote in message news:6C5CDDF1-4CCC-43B3-951B-61B7
41B24E53@.microsoft.com...
> 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:
>|||Be very careful using the XLOCK. You can end up with it not doing its job. T
ry 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@.discussions.microsoft.com> wrote in message
news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53@.microsoft.com...
> 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:
>|||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:

> 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 fro
m locking at the larger unit. You shouldn't ever have IX locks without X locks. Wher
e a
re 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 contai
ning 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@.discussions.microsoft.com> wrote in message news:6C5CDDF1-4C
CC-43B3-951B-61B741B24E53@.microsoft.com...|||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@.discussions.microsoft.com> wrote in message
news:385F426F-BE2D-489E-859C-83666C52A605@.microsoft.com...
> 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:
>|||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:

> 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 an
d 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). S
ee 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@.discussions.microsoft.com> wrote in message
> news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53@.microsoft.com...
>
>