Path: csiph.com!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Help needed in troubleshooting a lock
Date: Wed, 10 Mar 2021 13:05:43 +0300
Organization: A noiseless patient Spider
Lines: 19
Message-ID: <20210310130543.ed1a4d8455e1f621016617c3@g{oogle}mail.com>
References: <20210309164754.b2256b36ce7ddff08545ba1c@g{oogle}mail.com> <XnsACE8D53BB1A41Yazorman@127.0.0.1> <20210310125221.8604d106869d396a1586b827@g{oogle}mail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: reader02.eternal-september.org; posting-host="dcee196be4d01a8cba95ce5caf0acba1"; logging-data="21544"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1/ZYRXd5FpcO9DHaGq7CBJGhkb9RH+jZck="
Cancel-Lock: sha1:oqFZjv3+YuG7Mxp++TgH8oqhk5g=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2080

I wrote to Erland Sommarskog:

> > The  SELECT  wants  a page lock but is blocked by the IX
> > lock held by the UPDATE statement. You  need  to  add  a
> > ROWLOCK hint to that query.
>
> That  solved  it,  thanks.  Somehow it did not occur to me
> that I needed a (ROWLOCK) for the SELECT  as  well.

A simplified version of my query:

   SELECT A.Code
   FROM      [@GTTABLEKEYS] A WITH (NOLOCK)
   LEFT JOIN [@GTTABLEKEYS] F WITH (READPAST,ROWLOCK) ON F.Code = A.Code
   WHERE F.Code IS NULL

-- 
()  ascii ribbon campaign - against html e-mail
/\  http://preview.tinyurl.com/qcy6mjc [archived]