

Step 7: Use a 'check-in/check-out' mechanism for contested data

This guide tells you how to enforce and ensure SQL Server data integrity on many fronts at once.

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.


 Home: Introduction
 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



Serdar Yegulalp
Copyright 2005 TechTarget


Start the conversation

Send me notifications when other members comment.

Please create a username to comment.