November 16, 2009 at 2:40 pm
My company is currently trying to make a system that will know when data at lower levels has been changed so the program knows to refresh its cache. the way we did this was by putting a timestamp in all required tables. When the insert/update run we know the table has been updated. So we update the cache. When we delete we update the parent timestamp. The cache will now update for that object. This works in theory but since we have a multiuser environment and when a person goes to save data in the parent level the program won't let them since they do not have a current version. there cached timestamp is old. That is obviously not a good solution. Have any of you worked with this and can show some guidance?
November 16, 2009 at 4:56 pm
There are many, many different ways to solve concurrency problems. You may consider looking into using a combination of Query Notifications and client code or using snapshot isolation. Look in BOL if you aren't familiar with either of these concepts.
There's always a challenge when working with data cached on the client. You have to have a way to not only update the cache, but notify the user in the case when their data goes stale.
November 29, 2009 at 9:55 am
You can implement Service Broker and Messaging Technology to notify your clients.
Review this link for more information.
http://www.developer.com/db/article.php/3640771/Getting-Started-with-SQL-Server-Service-Broker.htm
November 30, 2009 at 9:09 am
I looked into both those solutions. They are very specific solutions being that you would have queries that would be registered with notifications so it knows when the data is dirty. We discussed this in our team meeting whether to implement from a push standpoint (SQL) vs a cache pull from .Net. What we found is there is not really a good way for an application across the board to use cache. This is very much the case when your system allows deletes. Without using a tombstone table or a cache refresh (buckets) trigger table you have to deal with complex logic to get timestamps updated correctly and effectively. Then the problems we have is saving the data when we use timestamps for comparison. ie make sure timestamp = timestamp or do not save. When I am updating them behind the scenes the cache many times is not aware of it.
So below is the discussion points we have come up with. After two meetings we have decided we have to have cache but we do not know the best way to implement it. So if you have a solution you have done and it has worked I would really appreciate the help. Also when we send this application out it is given to a client which means they have to maintain this. There may not be a DBA.
Started from caching entities: how can we detect changes?
- Timestamp
How do we detect deleted entities?
- Change parent's timestamp
Solutions:
- Sync (gets all entities that have updated timestamps) Selecting all tables with timestamp > then last cached timestamp
- Triggered by timer interval
- Triggered by specific user action
- Deletion should return all entities' timestamps (not implemented - tombstone table)
- Deletion should return parent entities that are deleted (not implemented - tombstone table)
- No caching - re-read entities
- Lock entities in all modes
- Add deleted entity IDs to a separate table (not implemented - tombstone table)
- SQL Push / notification services - To specific for our implementation. this application requires cache for the entire thing
- Sync Framework
Task:
- DB work (sproc, SQL, DAC Layer)
- Code work (Infrastructure, Sync, Realize)
- Updating at responsible client and non-responsible clients
Problems:
- Sync: executes over 200 SQL statements, deemed will be non performant, fragile
Sync mechanism:
- Keep last timestamp value
- Query for anything WHERE timestamp > lastTimestamp
- Need Id qualifiers
Solution discussions:
- Get entity also pass in Timestamp; SELECT will use timestamp as WHERE clause to see if entity has to be returned.
- If not caching; old paradigm: throw away whatever is displayed and re-read data from DB to display. Can do clone of entity for comparison purposes so updates are 'smart' (only updates stuff that have changed). Another approach is to have isDirty flag for entities/records that need to be updated
- With WPF use of DataBinding, it is assumed all data is already loaded when the view is displayed.
- Caching is a necessary evil for fairly static data; when we cache though, then we need to do sync
- Selective / Bucket caching:
- Sets up portion of data to be cached; if any part of that data changes, then dump the whole cache and re-read the data (put back in cache)
- "Bucket" table contains a row for each 'bucket'. Row 1 e.g. represents the configuration block 'bucket'. Timestamp is t1. Client data reads the configuration block data and the bucket timestamp and stores t1 in its bucket cache.
- Detect if an OT (or any of its children) has changed.
Another possible approach:
- Bucket cache on system admin items (@ configuration block level for instance)
- No cache on project management - re-read each time.
Current problems:
- Single user: deletion causes problem due to non-sync and timestamp conflict
- Multi-user: sync not running, sync problematic / difficult to maintain
November 30, 2009 at 9:33 am
Did you look into using Snapshot isolation? There is no client side cache, the database handles all of the comparisons and notifies the application when the data has changed. The only thing you have to program for is the error message that SQL will throw when a data colision happens.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply