Problem using ROWLOCK with INSERT/UPDATE.

(SQL Server 2005)
I am having a little trouble that I hope someone can clarify for me.
I have a session running with implicit_transactions turned on, and another
session trying to access a table undergoing some changes in that first
session. I had thought that I could sort of isolate the two sessions from
one another using ROWLOCK, but it seems as if (at least in my example) that
the ROWLOCK always gets promoted to a table lock.
Please see the attached DDL/DML for an example of what I'm trying to
accomplish (note that I ran this in [tempdb]).
Is there any way for me to have the second session successfully report the
counts of the *committed* rows without being blocked on the first session if
that first session is within a transaction scope (with, presumably, a
default isolation level of read-committed)?
Thanks for any help that anyone can provide!
Kind regards,
John Peterson
On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:

>(SQL Server 2005)
>I am having a little trouble that I hope someone can clarify for me.
>I have a session running with implicit_transactions turned on, and another
>session trying to access a table undergoing some changes in that first
>session. I had thought that I could sort of isolate the two sessions from
>one another using ROWLOCK, but it seems as if (at least in my example) that
>the ROWLOCK always gets promoted to a table lock.
>Please see the attached DDL/DML for an example of what I'm trying to
>accomplish (note that I ran this in [tempdb]).
>Is there any way for me to have the second session successfully report the
>counts of the *committed* rows without being blocked on the first session if
>that first session is within a transaction scope (with, presumably, a
>default isolation level of read-committed)?
>Thanks for any help that anyone can provide!
>Kind regards,
>John Peterson
>
Hi John,
I didn't read the complete code, nor did I try to run it, but based on
your narrative I think you need to add a READPAST locking hint.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||I don't think specifying ROWLOCK hint prevents the engine from escalating
locks as it sees appropriate, be it page, extent, etc.
Off the top of my head I can't think of a way to interact with the committed
rows directly from a separate transaction.
TheSQLGuru
President
Indicium Resources, Inc.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AIcMavpHHA.208@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2005)
> I am having a little trouble that I hope someone can clarify for me.
> I have a session running with implicit_transactions turned on, and another
> session trying to access a table undergoing some changes in that first
> session. I had thought that I could sort of isolate the two sessions from
> one another using ROWLOCK, but it seems as if (at least in my example)
> that the ROWLOCK always gets promoted to a table lock.
> Please see the attached DDL/DML for an example of what I'm trying to
> accomplish (note that I ran this in [tempdb]).
> Is there any way for me to have the second session successfully report the
> counts of the *committed* rows without being blocked on the first session
> if that first session is within a transaction scope (with, presumably, a
> default isolation level of read-committed)?
> Thanks for any help that anyone can provide!
> Kind regards,
> John Peterson
>
>
|||Hello Hugo!
The READPAST hint appears to do exactly what I want! Thanks for the tip.
Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
(I'm too lazy to bring up a 2000 BOL. ;-)
Thanks again!
John Peterson
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:f6696390qvpj9qtqcv89tovhp0pjdvsfg2@.4ax.com...
> On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:
>
> Hi John,
> I didn't read the complete code, nor did I try to run it, but based on
> your narrative I think you need to add a READPAST locking hint.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:

>Hello Hugo!
>The READPAST hint appears to do exactly what I want! Thanks for the tip.
>Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
Hi John,
Typing a message and going back to check for an answer is less effort
than double-clicking an icon on your desktop? <shakes head>
READPAST is available in SQL Server 2000 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:catb6317978pkrfm33rimiofeqrguu524j@.4ax.com...
> On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:
>
> Hi John,
> Typing a message and going back to check for an answer is less effort
> than double-clicking an icon on your desktop? <shakes head>
Heh! Well, as it turns out, when I sent the previous reply, it wasn't from
a machine that had access to SQL Server at the time. :-)

> READPAST is available in SQL Server 2000 as well.
Ah, interesting. I didn't realize that -- good to know.
Thanks again for your help!

> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis