Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2009-06-29 : 17:09:47
|
| We are reviewing code that a developer has passed on to use for review and I'm wondering how best to handle this. An appropriate example would be if two customers service reps were to edit the same record at the same time how would you go about preventing this. The rep could be on the phone with the customer for up to 10 minutes entering his/her data, whilst another rep could be entering the data from a correspondence.If anyone has encountered something similar please feel free to add to this.thanks. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-06-30 : 09:21:18
|
| It's called optimistic or pessimistic locking. You have to decide which is the most likely scenario and code for either 'read and assume it will not have change when you write it back' or 'lock the record using the application on the assumption that the values will change based on the phone call'.A word of advice though - do not use the DB for implementing pessimistic locking (which is often wrong). The DB locks are different and are used to manage concurrency not application specific requirements. You will have to a) Try to update based on PK and a timestamp acquired when you first read (for optimistic)b) Maintain some kind of 'PK in use' structure, which you permanently need to monitor as people lock each other out (pessimistic).This is driven by business requirements not DB technology.Personally, I would maintain a log of rep activity so there is no conflict. If they really are updating details at the 'same time' then optimistic is best anyway - first come, first served. |
 |
|
|
|
|
|
Locking a table(s) question