Knowing how to handle contention for updating a particular row is a common problem with database applications where many users may be accessing or attempting to modify the same data. For instance, if a client opens some data through a Web browser form, then gets up for a cup of coffee, how does he know that by the time he sits back down someone else hasn't changed the data he's looking at (which has been presented to him on a static page)? Allowing people to work with stale data is one surefire way to ruin the integrity of your data in the long run.
A very common solution to this involves placing a column in the relevant data table that indicates whether a particular user is currently looking at that row. This column could contain either a NULL value (to indicate no one is currently attempting to modify the data) or a time/date (indicating when the row was opened by a client for modification). When the client submits modifications properly, that column is NULLed, and the row is freed up for editing once again. This would help protect against breaking integrity if people try to work from a stale data set.
ENSURING DATA INTEGRITY IN SQL SERVER
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
|ABOUT THE AUTHOR:|
| Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget