Re: Update Integrity
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/16/05
- Next message: Alejandro Mesa: "RE: show f1 when f2 is not distinct"
- Previous message: gv: "Re: Two columns have data mixed up need to fix"
- In reply to: Steven Lazan: "Update Integrity"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Feb 2005 21:59:31 +0100
On Wed, 16 Feb 2005 09:36:31 -0500, Steven Lazan wrote:
>Looking for opinions:
>
>What are some of the preferred methods for maintaining data integrity during
>updates. In other words, how to prevent one user from overlaying data that
>another user just saved. If two users are looking at the same data on two
>different PCs. User 1 changes data and saves, then a few seconds later User
>2 changes and saves, User 2 data overlaid User 1.
>
>The thought was to have a col in the table (either number or datetime) that
>increases with each update. Then the user submitting the update passes to
>the proc the last value of that col (called LastUpdate) along with the
>update values. If the value of LastUpdate has changed, then return error to
>the user asking them to refresh their screen since the data has changed
>since they retrieved it.
>
>Are there other methods or suggestions for this circumstance.
Hi Steve,
This is the most common method. It's usually called "optimistic locking"
(optimistic, as it assumes no update conflicts will occur and takes extra
steps to correct in the rare cases that they do).
In SQL Server, you don't even have to manually increase the value of an
integer column, you can use a column with the rowversion (aka timestamp)
datatype/attribute - SQL Server will increase the value of this column
every time a row is updated. If you include it in the select and in the
WHERE clause for the UPDATE, you have imnplemented optimistic locking:
SELECT @Col1 = Col1, @Col2 = Col2, ..., @RowVers = RowVers
FROM MyTable
WHERE KeyColumn = @KeyValue
do something with the values in @Col1, @Col2, ...
UPDATE MyTable
SET Col1 = @Col1, Col2 = @Col2, ...
WHERE KeyColumn = @KeyValue
AND RowVers = @RowVers
IF @@ROWCOUNT <> 0
BEGIN
-- Handling for row changed by other connection
END
You also asked for other methods; there is one called "pessimistic
locking" (pessimistic, as it assumes that update conflicts will occur and
therefore takes steps to prevent them).
You can implement pessimistic locking in two ways:
(a) Start a transaction when the "change values" screen is opened, take an
exclusive lock on the data (use UPDLOCK hint on the select), keep the
transaction open until the user ends the screen, either disbanding or
saving his changes, or
(b) Use a column to indicate that a user has reserved the row for change
and check the value of that column before allowing another user to start
changing the values
In both options, a second user doesn't get a chanace to overwrite the
changes made by the first, since he simply is not allowed to start making
changes until the first user is ready.
Pessimistic locking isn't used much, mainly because neither option is very
scalable; option (a) has serious impact on concurrency as well and option
(b) is prone to locks staying around in the database forever if a
connection is broken ungracefully.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Alejandro Mesa: "RE: show f1 when f2 is not distinct"
- Previous message: gv: "Re: Two columns have data mixed up need to fix"
- In reply to: Steven Lazan: "Update Integrity"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
