From: esquel@sommarskog.se   
      
   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?   
      
   >   
   >      
   >         
   >      
   >      
   >         
   >      
   >   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)