c8eefb10   
   From: esquel@sommarskog.se   
      
   migurus (migurus@yahoo.com) writes:   
   > The biggest concern is to make sure that   
   > a) nobody does updates to runs 1 and 2 while the update steps 1   
   > through 6 are running   
   > b) nobody could read a state of the runs while updates are in progress   
   >   
   > One posibility is to use sp_getapplock to serialize all access, but   
   > that is not ANSI, and we would like to stay away from db-specific   
   > solutions.   
      
   Moreover, sp_getapplock would apply to that procedure only. I don't know,   
   but it seems to me that there would be several paths of access in this case.   
   Also, an application lock on the lock would mean that while you are working   
   with runs 1 and 2, no one can work with runs 12 and 13.   
      
   I think you should start the transction with something like:   
      
      SELECT * INTO #temp FROM Stops WITH (UPDLOCK) WHERE RUN_ID IN (1, 2)   
      DROP TABLE #temp   
      
   The temp table here is of no interest in itself. What you do is that you   
   place an update lock on the runs you plan to modify. An update lock is a   
   shared lock which states an intent to update. Other process can still   
   read, but no can update the data. Or take a second update lock on the   
   resource.   
      
   As written, this gives you REPEATABLE READ. This means that none of   
   the data you have read can be changed. However, it still possible   
   for another process to insert new trips of stops in the runs.   
      
   To get protection against such phantoms (as they are called), you can   
   change the locking hint to (UPDLOCK, SERIALIZABLE). There are two drawbacks   
   with this. One is that your lock may be too wide. You may also block   
   another process to insert a new trip first in run 3. The other drawback   
   is that the serliasable isolation level is prone to create deadlock   
   situations.   
      
   You semed to be concerned about portability, and UPDLOCK is indeed a   
   Microsoft-specific hint. You can to without it, but in that case you   
   will see a lot of conversion deadlocks, where two processes both   
   were able to lock the same trip and then tries to continue with an   
   update.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)