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

Re: Help needed in troubleshooting a lock

From	Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Help needed in troubleshooting a lock
Date	2021-03-10 13:05 +0300
Organization	A noiseless patient Spider
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>

Show all headers | 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