Data Integrity in a SQL Server Application

[zachattack05]

Thanks to you guys I have been able to get my first application halfway going. But I have a question about data integrity.

The application that I am developing utilizes a single sql server and multiple work stations accessing that sql server.

What is the best way to maintain data integrity? If user X is editing record 20 and user Y is editing record 20 at the same time, whoever saves the data last wins. That's not a good plan.

I was thinking about having records "locked" when they are edited, or something similar to that. Maybe making all records read-only unless you "check out" the record for editing....how have you guys handled this in your applications?

[mcriscolo]

One simple way is to use a DateTime or Timestamp column. When you update the row, check the timestamp to see if it matches the value you got when you pulled the record for editing. If it does not match, you know someone else updated the record while you were working on it.

[zachattack05]

One simple way is to use a DateTime or Timestamp column. When you update the row, check the timestamp to see if it matches the value you got when you pulled the record for editing. If it does not match, you know someone else updated the record while you were working on it.

Good idea. But that leads me into the direction of a corner. If I compare the value displayed on workstation1 to the value stored in the SQL server and the values match there's no problem. Simply update the record and change the time stamp. But what if the values are different? Do I just say to the user that the changes cannot be saved because the record was changed by someone else first? Do I offer the user the opportunity to overwrite the changes, revert to the new changes etc...

If I offer a record checkout or record locking system then if user X edits the record and user Y tries to at the same time, user Y gets a message saying the record is already locked for editing.

I think there are downsides to locking records too...for example, software crashes, network interruptions etc...can leave records permanently checked out making them uneditable unless someone updates the record manually.

I think I like the time stamp idea you proposed better, but I'm not sure how to handle it when two users attempt to update the record.

[mcriscolo]

As always with this stuff, there are many ways to skin the proverbial cat. Using the timestamp method, you could even go as far as this: when you attempt to apply the update but find the record's been updated under you, re-read it into another buffer and compare the appropriate values, highlighting any differences and display to the user. Of course, that's a lot of elbow grease. More simply, you could just put a message out to the user saying "Another user has updated the record; please re-enter your changes" and re-display the form to the user with the new values from the newly-updated record.

[zachattack05]

As always with this stuff, there are many ways to skin the proverbial cat. Using the timestamp method, you could even go as far as this: when you attempt to apply the update but find the record's been updated under you, re-read it into another buffer and compare the appropriate values, highlighting any differences and display to the user. Of course, that's a lot of elbow grease. More simply, you could just put a message out to the user saying "Another user has updated the record; please re-enter your changes" and re-display the form to the user with the new values from the newly-updated record.

Hmmm...that's not a bad idea, but your right it does sound like a pain in the rear. The big problem is that sometimes a user might enter a lot of information, a paragraph or so. That would suck to have to re-enter a paragraph again if it is just deleted.

Anyone else have any suggestions? Nothing seems to stick out to me too much, but I do think that the time stamp idea is the best. I just want to make sure that it is easy for the end user to use.

[zachattack05]

I think what I'm going to do is have a record locking system. I'll have the locking computer post a updated time stamp every 5 minutes or so, if a different computer requests to lock the record and the time stamp is more than 5 minutes old I'll have that new requesting computer unlock it and then re-lock it with the new credentials in case there is a failure or something.

Only issue I can see with this is each computer having a different clock. If computer A is 5+ mins faster it can mess up the system...I may need a program on the SQL Server computer to manage locked records....hmmmm

Any other ideas?

[powerbox]

If you're using MS SQL server then you can use its notification services to prompt users that there has been an update. Now its up to your client to keep up on it and how such updates will be interpreted.

You can also go with the time stamp logic but I suggest you have only 1 app that will update your database. Let that app queue up your transactions and if there is a probable error you can then notify the client that its data is not recent anymore.

I suggest that you read on disconnected distributed application and how it is done.

[zachattack05]

If you're using MS SQL server then you can use its notification services to prompt users that there has been an update. Now its up to your client to keep up on it and how such updates will be interpreted.

You can also go with the time stamp logic but I suggest you have only 1 app that will update your database. Let that app queue up your transactions and if there is a probable error you can then notify the client that its data is not recent anymore.

I suggest that you read on disconnected distributed application and how it is done.

The problem is I am trying to avoid human error. The system that I am designing could potentially hold medical record information for small clinics. I would want to prevent user A from getting a message saying that the data they are looking at is outdated and to refresh it. If I go with a system like that, then if user A is working in a certain recordset and user B makes a small change, then user A may have to re-enter all of their information if they refresh it. I would rather simply prevent two people from editing a record at the same time instead of fielding data collisions when they happen. See what I mean?

[powerbox]

Yes that is why I have this on my suggestions : "Now its up to your client to keep up on it and how such updates will be interpreted."

Anyway another way is to lock up certain record and notify the current User who is editing it that there is someone waiting for him to finish.

I guess that is why data collision analysis should be done carefully in your project. I think you need to clear your project process specially on the editing part. I don't see any reason why 2 persons are editing 1 record at the same time, either of the 2 must have not followed certain procedure. If certain events that 2 users must indeed work on a record at the same time then I think you should provide a history of changes on real-time notifying other users about it and and let them continue their work as if it is a different transaction.

[zachattack05]

OK, I think I may have an idea. Let me know what you guys think:

Have a separate SQL table on my database called "locks" or something.

In that table have these columns:
id - Auto Inc. Integer field (for sorting mostly)
table - holds the name of the table that has a lock
record - holds the record id number of the record that is locked/requested
status - can either be "locked" or "queued"
user - contains the name of the user that has the record locked or is requesting the record
expires - holds a randomly generated number that changes every 60 seconds

This table would allow for a queue for records, so if user A is editing record 50 and user B needs to edit it, they would essentially click a button or something that would check the locks table for that particular record id and see if it is locked, if it is, it will add their name to the locks table with the status of queued.

The form would also have a timer function on it set to 60 seconds. Every 60 seconds the client that has the record locked would generate a random number and update the locks form with that number in the expires column. Every 60 seconds the client that is waiting for the record to be unlocked would check to see if the number in expires changed, if it did then they wait another 60 seconds, if it doesn't change then a deadlock has occurred, in that case, the record is unlocked and then the next person in the queue (determined by sorting on the id column) will lock the record and be presented with a message box saying that it is available for editing now. The user would have 60 seconds to determine if they want to edit the record or sacrifice their position in the queue by either clicking yes or no in the message box. This would prevent AFK users from locking records for their whole lunch hour. If they fail to respond in 60 seconds the message box closes, a new one appears scolding them for leaving their computer during work hours...the lock is then released and the next person is given the record.

Thoughts? This is just a concept at the moment.

[powerbox]

I think there is too much transaction going to your database, you have the idea already but I think there will be even more better design than this. Technically I don't want my database to do all the work for me specially on the locking part. I guess if you can create another app that monitors the locked documents , something like a client-server setup application , then I think it will be a lot faster and you can actually patch up some logic behind it.

I think your solution will help you out as your initial proof of concept. Try it first because you will never know if it really works the way you want it and experienced it.

I can't push my suggestions to you since you're actually the one doing the programming part and not me, hope you can solved your problem the soonest possible time.

[zachattack05]

I think there is too much transaction going to your database, you have the idea already but I think there will be even more better design than this. Technically I don't want my database to do all the work for me specially on the locking part. I guess if you can create another app that monitors the locked documents , something like a client-server setup application , then I think it will be a lot faster and you can actually patch up some logic behind it.

I think your solution will help you out as your initial proof of concept. Try it first because you will never know if it really works the way you want it and experienced it.

I can't push my suggestions to you since you're actually the one doing the programming part and not me, hope you can solved your problem the soonest possible time.

I can see where there could potentially be quite a few transactions going to the database...but having a second piece of software installed on the SQL server could complicate things.

Do you think that there would be much of a performance impact on either the SQL server itself or the application? Or is it the design that concerns you?