c# - Invalid redis cache upon SQL Server update/trigger -

i'd make use of redis cache objects retrieved sql server have cluster of webservers , consistency important. there way cache updates in redis such when row came updated, cache invalidated?

in ideal world, i'd able ask redis "widget-123" , if don't it, load "select * widgets id=123" sql server, create object , store in redis future requests if row modified, sql server able tell redis invalidate corresponding key webservers know fetch new instance.

is feature possible using triggers or sql broker

how introducing layer between database , code.the layer responsible of following, assumption cruds goes through layer.

1. you perhaps figured it

request "widget-123" -> | layer (keys exists) ? redis : db , add redis | sql server

2. update/delete entity ( id=123) -> | layer ( key exists ) ? evict redis |-> sql server.

this approach can give consistent result. but.

if still want sql server should push details, sorry , there no client ( sql not general purpose ) tell redis evict directly. @jeremy bell suggested, can have trigger write table , table can polled continuously(by quartz kind) , pushed expire request ids. disadvantage may not consistency, due write , read on db temporay table may bottle neck or problematic ( race or deadlocks ) if not taken care of.