From: anton.txt@g{oogle}mail.com   
      
   Erland Sommarskog to Anton Shepelev:   
      
   > > -- 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   
   >   
   > There  is  more  than  one way that this can go wrong. One   
   > thing is that things may change quickly  in  a  concurrent   
   > environment.   
      
   The  environment  is indeed concurrent, but all write access   
   to @GTTABLEKEYS is made via my own key-generation procedure,   
   which  uses  (ROWLOCK) to serialise the process.  My purpose   
   is the detection of long-standing locks, such as from trans-   
   actions that somebody has forgotten to end, so I do not wor-   
   ry about transient effects.   
      
   > 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.   
      
   That is OK for my situation, where I do not care about  get-   
   ting wrong results occasionally.   
      
   > Then  again,  if the number of rows in this table are con-   
   > stant, or rows are added or deleted very rarely, it  could   
   > work.   
      
   It chages very rarely. Most of the time, only the values for   
   individual counters are updated.   
      
   > 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.   
      
   Thanks for the recommendation, but  triggers  are  generally   
   considered  a  code  smell, perhaps becuase of their lack of   
   transparency, and the software that relies on  our  database   
   will  not let us add triggers to it, as it forbids even cov-   
   ering indexes, which is outright stupid. What is the  advan-   
   tage  of  a  shadow copy over a NOLOCK read -- better perfo-   
   mance and lower impact of the monitoring query upon the load   
   of the production table?   
      
   > > whereas the UPDATE is waiting:   
   >   
   > The SELECT is waiting?   
      
   Yes,  I beg your pardon. The UPDATE has taken a lock and the   
   SELECT is waiting on it.   
      
   > 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.   
      
   That solved it, thanks. Somehow it did not occur to me  that   
   I  needed  a (ROWLOCK) for the SELECT as well. Thank for the   
   help, Erland.   
      
   --   
   ()  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)