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 |
|
ahallett
Starting Member
7 Posts |
 Posted - 2006-09-24 : 11:49:34
|
| Can anyone offer some advice about a couple of system design issues? I am have been developing for many years and have just started to produce a multi-user client-server system using VB.Net and SQL Server Express. There are a couple of questions that I just can’t get answers to, despite repeated searches on the net, various SQL forums and in the Online Books. Maybe I’m just asking the question wrongly …The issues have to do with record-locking. I understand that SQL applies a lock on rows being updated in various ways, depending on the rows to be changed during the transaction.Yet, various sources warn (sensibly) against keeping locks applied across any user interaction i.e. don’t get a lock and then wait while a user decides what to do.Fair enough, but how do I protect against a record being changed by two users at the same time? For example, consider a single row in a table: User A displays the row data on screenUser B displays the same row data on screenUser A makes a change and commits to an updateUser B makes a change and commits to an updateWithout some sort of protection, the change by user A will get lost.How, generally, might this be done in the VB.NET/SQL Server? Are there any embedded processes that I can use? Or do I have to construct them myself? All advice appreciated …Many thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-24 : 12:57:58
|
| You can use a timestamp column and check the value on update - if it's changed then reject the update.If you don't want to fail updates then implement a read for update which holds an id of the entity to be updated in another table and refuses other read for updates on that entity until it is released - you will need to cater for lost connections using this method.Perform all database access via stored procedures and this sort of thing is easy, and you can change the way things are done without changing the app.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-24 : 15:31:35
|
| Hi ahallett, Welcome to SQL Team!Just in case you are not familiar with it a "timestamp column" does not actually store the date/time - unfortunate name for that datatype, it stores a unique ID that changes each time the record is updated.So in effect you can say:SELECT MyTimeStamp, MyOtherColumns ... FROM MyTablestore the value of MyTimeStamp locally and allow the user to perform edits as necessary then:UPDATE MyTable SET MyOtherColumns = ... WHERE MyPK = 'FooBar' AND MyTimeStamp = 'OriginalValue'and if you find that zero rows were updated then someone else has changed the record in the meantime.You could have your own "locks table" where you put the Table Name and the PK values in a table, with the user ID, and the second user that tries to "acquire" the record for locking gets a "Sorry, this record is being edited by FRED" message. This is still prone to people going to lunch having Acquired a record, but it is easy to also provide end users tools to allow folk to un-allocate locked resources - which in turn means that you do need to check that the current user still Owns the resource just before you write it!In effect you would be re-inventing the locking system that SQL already has, but overlaying it on SQL's own locking mechanics. It can provide a nice friendly end-user environment.One other way is:UPDATE MyTableSET SomeColumn = 'FooBar'WHERE MyColumn1 = 'OriginalValue1' AND MyColumn2 = 'OriginalValue2'for this to work you are in effect checking that none of the columns have changed from the original. (Actually, you don't need to check ALL column, just the columns that are effected by the user's change - so this approach MAY let you allow concurrent updates provided that the changes don't "overlap" - so for example you might allow the name of a Product to be changed provided that the original name is what the user started from, but not caring if, say, the Stock Level has changed in the meantime).Kristen |
|
|
|
ahallett
Starting Member
7 Posts |
Posted - 2006-09-25 : 04:58:45
|
| Thanks to both of you.Timestamp is the thing for me! I didn't really want to get into the whole issue of marking rows as 'in-use' and facing the quesion of what to do if the user doesn't end the transaction normally. The user base will be extremely naive and the whole idea of providing utilities to back out in-use rows is probably best avoided.Thanks again. |
|
|
|
|
|
|
|
|
Locking strategy and design question