February 11, 2009 at 9:47 am
Currently we are in a working environment where the application needs to be aware of changes made to the DB. The app tries to limit the DB hits by caching much of the information it requires. There are locks set within the program but we double check commits by looking at the row version (Timestamp). So if you delete a record we may update a parents timestamp and children. then we have the sync tool. This basically looks at every table and refreshes the timestamps in the cache. I see this as an issue (hitting every table), but I do not know a good solution. how do people handle multi-user environments to make sure people do not over-write each other. I am aware of a row lock, even a field for locking a record but I am more worried about if person has it on the screen sitting there for .5 hours and two people came in and changed the record already. If you need clarification please ask. Thanks ahead of time for reading this rant
February 11, 2009 at 10:17 am
Hi
Normally what we do is, we add Row_Version column to the table where we are expecting DML operations. When we query and display the data in the application we will get this column also along with other columns in the table. If one is trying to update the data in the same UPDATE statement we will also increatement this by 1 in addition to this we will also put where condition on this column to make sure we dont over-write if there were any updates.
EX:
UPDATE EMP SET Row_Version = Row_Version + 1,
WHERE Row_Version =
Thanks -- Vijaya Kadiyala
February 11, 2009 at 10:52 am
That is basically what the timestamp is doing. I am trying to figure out how to let the users know what changed. It will not just be one table. I was talking to a developer and basically in C# there is a replication scenerio called iobserver, inotify, and iGetChanges. Well it seems like there is no good solution to in a multi-user enviroment. It is weird to think that locking is still the best bet.
February 13, 2009 at 7:24 am
If you have to constantly check the cache against the database, haven't you eliminated the usefullness of the cache?
First, try not to cache data that is really likely to change during a user's session. Second, don't update the local cache - update the database and have the database notify your cache that an update is available.
From there, you need to make you application aware of data changes in cached information. This can be done in several ways.
One method I have used in the past was to use service broker and web services to notify the application. When a change happens to a record in a cache-enabled table, we would use a trigger that wrote to a service broker queue (to get the notification work out of the update transaction). This queue would call to a web service. When the application opened, it would register with this web service so the web service would have a notification connection back to the application. This allowed us to take the notification of a data change and send it back (actively) to the client application. In our case, the client application would refresh the entire table's cache, but this process could be modified to send the changed record with the notification to the client app. It worked well, but it was a complex system that took a lot of maintenance. It may be cheaper over time to buy a Citrix server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply