Path: csiph.com!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Help needed in troubleshooting a lock
Date: Tue, 09 Mar 2021 20:57:42 +0100
Organization: Erland Sommarskog
Lines: 57
Message-ID: <XnsACE8D53BB1A41Yazorman@127.0.0.1>
References: <20210309164754.b2256b36ce7ddff08545ba1c@g{oogle}mail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="efdbab53a897024d70fb4df415d67b11"; logging-data="5982"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18tFE7TREhkhwg+CglOHOIV"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:6+eVEoOHNJgmf0oMmc6P+7sdTJU=
Xref: csiph.com comp.databases.ms-sqlserver:2078

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> I am trying to write a
> T-SQL script that shall output currently locked rows in @GT-
> TABLEKEYS, assuming that write access to @GTTABLEKEYS is al-
> ways made with (ROWLOCK). Here is what have come up with:
> 
>    -- 1. Select the free (unlocked) rows:
>    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
>    SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH 
> (READPAST)
> 
>    -- 2. Select all the rows:
>    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>    SELECT Code INTO #ALL FROM [@GTTABLEKEYS]
>    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> 
>    -- 3. Report rows that are not free (i.e. locked):
>    SELECT #ALL.Code
>    FROM #ALL
>    LEFT JOIN #FREE ON  #FREE.Code = #ALL.Code
>    WHERE #FREE.Code IS NULL

There is more than one way that this can go wrong. One thing is that 
things may change quickly in a concurrent environment.

Another is that a NOLOCK read amy skip already committed rows, if 
you come right in the middle of page split or something like this.

Then again, if the number of rows in this table are constant, or 
rows are added or deleted very rarely, it could work. Yet, then again,
in that case, you can keep a shadow copy of the table, and keep
it maintained from a trigger, and you don't need the NOLOCK read at
all.
 
> whereas the UPDATE is waiting:

The SELECT is waiting?

> 
>   <Locks>
>      <Lock request_mode="S" request_status="GRANT" request_count="1" />
>   </Locks>
>   <Objects>
>      <Object name="@GTTABLEKEYS" schema_name="dbo">
>        <Locks>
>          <Lock resource_type="OBJECT" request_mode="IS" 
request_status="GRANT" request_count="1" />
>          <Lock resource_type="PAGE" page_type="*" 
index_name="KGTTABLEKEYS_PR" request_mode="S" request_status="WAIT" 
request_count="1" />
>        </Locks>
>      </Object>
>   </Objects>
> 

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.