Avoiding deadlock when updating table

Question

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.

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.
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:

MAX(MAX(MAIN_TABLE), MAX(CHILD1_TABLE), MAX(CHILD2_TABLE))

So I switched and added a trigger to this table so that it update a field in a TBL_METADATA table:

CREATE TABLE [TABLE_METADATA](
    [TABLE_NAME] [nvarchar](250) NOT NULL,
    [TABLE_LAST_MODIFICATION] [datetime] NOT NULL

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

But ... now I've random deadlock related to updating this table.

This is due to 2 transactions modifying the TABLE_METADATA at a different step, and then locking each other.

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:

• The lastmodification stay updated even if the transaction is rollback
• The 'dirty' lastmodification (updated but not yet committed) is overwritten by a new value

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.

Thank you for any help

Answer 1

As far as I know, you cannot prevent a U-lock. However, you could try reducing the number of locks to a minimum by using with (rowlock). This will tell the query optimiser to lock rows one by one as they are updated, rather than to use a page or table lock.

You can also use with (nolock) on tables which are joined to the table which is being updated. An alternative to this would be to use set transaction isolation level read uncommitted. Be careful using this method though, as you can potentially create corrupted data.

For example:

update mt with (rowlock)
    set SomeColumn = Something
    from MyTable mt
        inner join AnotherTable at with (nolock)
        on mt.mtId = at.atId

You can also add with (rowlock) and with (nolock)/set transaction isolation level read uncommitted to other database objects which often read and write the same table, to further reduce the likelihood of a deadlock occurring.

If deadlocks are still occurring, you can reduce read locking on the target table by self joining like this:

update mt with (rowlock)
    set SomeColumn = Something
    from MyTable mt
    where mt.Id in (select Id from MyTable mt2 where Column = Condition)

More documentation about table hints can be found here.