Groups > comp.databases.ms-sqlserver > #2080

Re: Help needed in troubleshooting a lock

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> (permalink)
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

Show key headers only | View raw

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]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-09 16:47 +0300
  Re: Help needed in troubleshooting a lock Erland Sommarskog <esquel@sommarskog.se> - 2021-03-09 20:57 +0100
    Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 12:52 +0300
      Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 13:05 +0300

csiph-web