From: anton.txt@g{oogle}mail.com   
      
   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:   
      
         
           
         
         
           
         
      
   whereas the UPDATE is waiting:   
      
         
           
         
         
           
         
      
   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]   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)