dbTalk Databases Forums  

read locks

microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming


Discuss read locks in the microsoft.public.sqlserver.programming forum.

Reply
 
Thread Tools Display Modes
  #1  
Old   
Val P
 
Posts: n/a

Default read locks - 03-02-2005 , 03:41 PM






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"






Reply With Quote
  #2  
Old   
Kalen Delaney
 
Posts: n/a

Default Re: read locks - 03-02-2005 , 04:17 PM






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

Quote:
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"






Reply With Quote
  #3  
Old   
Val P
 
Posts: n/a

Default Re: read locks - 03-02-2005 , 04:49 PM



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:

Quote:
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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"







Reply With Quote
  #4  
Old   
Kalen Delaney
 
Posts: n/a

Default Re: read locks - 03-02-2005 , 05:27 PM



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

Quote:
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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"









Reply With Quote
  #5  
Old   
Val P
 
Posts: n/a

Default Re: read locks - 03-02-2005 , 06:05 PM



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:

Quote:
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 in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"










Reply With Quote
  #6  
Old   
Kalen Delaney
 
Posts: n/a

Default Re: read locks - 03-02-2005 , 06:39 PM



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

Quote:
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 (AT) discussions (DOT) microsoft.com> wrote in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"










Reply With Quote
  #7  
Old   
Tibor Karaszi
 
Posts: n/a

Default Re: read locks - 03-03-2005 , 03:21 AM



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

Quote:
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 (AT) discussions (DOT) microsoft.com> wrote in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"












Reply With Quote
  #8  
Old   
Val P
 
Posts: n/a

Default Re: read locks - 03-03-2005 , 10:37 AM



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:

Quote:
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

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"










Reply With Quote
  #9  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: read locks - 03-03-2005 , 10:47 AM



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

Quote:
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 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 in message
news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53 (AT) microsoft (DOT) 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:

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 in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"












Reply With Quote
  #10  
Old   
Val P
 
Posts: n/a

Default Re: read locks - 03-03-2005 , 10:57 AM






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:

Quote:
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 in message
news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53 (AT) microsoft (DOT) 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:

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 in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6 (AT) microsoft (DOT) 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:

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 in message
newsCEE6BF0-EE47-48AD-A46E-438AB382D1D5 (AT) microsoft (DOT) 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"













Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.