Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Help needed in troubleshooting a lock

Show all headers | View raw

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]

Back to comp.databases.ms-sqlserver | Previous | Next — Next 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