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: Help needed in troubleshooting a lock
Date: Tue, 9 Mar 2021 16:47:54 +0300
Organization: A noiseless patient Spider
Lines: 107
Message-ID: <20210309164754.b2256b36ce7ddff08545ba1c@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="77be1c32919df14164774a00c23ae327"; logging-data="19984"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19spt04DpzSr0e8FOZWigEDKDhTusF4ZhE="
Cancel-Lock: sha1:60RebVHx1GPZCidWcrGhYAcYD5c=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2077

Hello, all

I  have  a  several databases on several MSSQL-2017 servers,
each containing the same table @GTTABLEKEYS created  by  the
following command:

   CREATE TABLE [dbo].[@GTTABLEKEYS]
   (  [Code]         [nvarchar](   50) NOT NULL,
      [Name]         [nvarchar](  100) NOT NULL,
      [U_CurrentKey] [numeric] (19, 6) NULL,
      CONSTRAINT [KGTTABLEKEYS_PR] PRIMARY KEY CLUSTERED
      (  [Code] ASC )
      WITH
      (  PAD_INDEX       = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
         ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS       = ON  )
      ON [PRIMARY]
   )
   ON [PRIMARY]

As  you  will have understood, this table stores our custom,
manually implemented sequences, but we  cannot  use  MSSQL's
built-in  mechanisms  instead because of the limitations im-
posed by higher-level software that uses this database.  For
purposes of debugging and monitoring, 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

   DROP TABLE #FREE
   DROP TABLE #ALL

On all of my databases but one, the script works as  expect-
ed. When I lock a row with, e.g.:

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   BEGIN TRAN
   UPDATE [@GTTABLEKEYS] WITH (ROWLOCK)
   SET U_CurrentKey = U_CurrentKey + 1
   WHERE Code = '00000003'
   -- ROLLBACK TRAN

from  one connection and then run my script from another, it
dutifully returns the code of  the  locked  row -- 00000003.
When,  however, I perform the same test on the single myste-
riously affected database, the query:

   SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH (READPAST)

hangs on a lock until I end the transaction with the UPDATE.
I  have  made sure that the execution plans for both the UP-
DATE and SELECT statements are the same in both working  and
affected  environments,  the only difference being quantita-
tive: in the "affected" database @GTTABLEKEYS has much  more
rows that in working ones.

When  the SELECT above is thus locked, I see that the UPDATE
has taken the following locks:

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

whereas the UPDATE 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>

Can you please help me troubleshoot the problem  or  explain
the locking of my script on only one of the many structural-
ly identical databases?

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