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