title: Avoiding deadlock when updating table
body: <p>I've got a 3-tier app and have data cached on a client side, so I need to know when data changed on the server to keep this cache in sync.</p>
  
  <p>So I added a "lastmodification" field in the tables, and update this field when a data change. But some 'parent' lastmodification rows must be updated in case child rows (using FK) are modified.<br>
  Fetching the MAX(lastmodification) from the main table, and MAX from a related table, and then MAX of these several values was working but was a bit slow.
  I mean:</p>
  
  <pre><code>MAX(MAX(MAIN_TABLE), MAX(CHILD1_TABLE), MAX(CHILD2_TABLE))
  </code></pre>
  
  <p>So I switched and added a trigger to this table so that it update a field in a TBL_METADATA table:</p>
  
  <pre><code>CREATE TABLE [TABLE_METADATA](
      [TABLE_NAME] [nvarchar](250) NOT NULL,
      [TABLE_LAST_MODIFICATION] [datetime] NOT NULL
  </code></pre>
  
  <p>Now related table can update the 'main' table last modification time by just also updating the last modification in the metadata table.
  Fetching the lastmodification is now fast</p>
  
  <p>But ... now I've random deadlock related to updating this table.</p>
  
  <p>This is due to 2 transactions modifying the TABLE_METADATA at a different step, and then locking each other.</p>
  
  <p>My question: Do you see a way to keep this lastmodification update without locking the row? 
  In my case I really don't care if:  </p>
  
  <ul>
  <li>The lastmodification stay updated even if the transaction is rollback</li>
  <li>The 'dirty' lastmodification (updated but not yet committed) is
  overwritten by a new value</li>
  </ul>
  
  <p>In fact, I really don't need these update to be in the transaction, but as they are executed by the trigger it's automatically in the current transaction.</p>
  
  <p>Thank you for any help</p>