ASP / SQL record update question

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP / SQL record update question

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 14:22:12

I allow multiple users to edit records in a table. An ASP form is used to present the requested record's current information to the user. Users can update fields and apply the update.

What kind of steps would ensure that User1 doesn't overwrite the updated data in the record User2 just posted? This isn't a likely situation, but it's not impossible.

I can imagine two solutions - (1) lock the record for the duration of the update - this solution has a downside if the client PC doesn't complete the update, (2) keep a checksum on the record which is read during the data retrieval and should match when the update is applied - (if no match, assume another user has made a prior update).

Any other approaches?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-23 : 15:03:33

1 is an absolute no-no unless you want to commit suicide.

2 is a good idea.

3) add a timestamp column to your table(s) and include it when retrieving data from the database, and also when sending updates back for processing. Write your stored procedure to compare the timestamp value to the row being updated, if they differ, the row was updated by another process since it was retrieved.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 15:06:04

I recommend Rob's number 3 option. We used that method at my last job in a time keeping application. We added a timestamp column on all of the tables except for the tables with static data such as lookup tables.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 16:06:31

Ya know, a timestamp sounds a l of a lot easier than a checksum. Plus, it's already in the table as 'UpdateDate'. All I've got to do is a little handshakeing.

Thanks,

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 16:34:21

Um, this will require some table locking within the SP to close all the holes wouldn't it?


CREATE PROC MYSP (@MyKey INT, @MyData varchar (100), @UpdateDate datetime, @Result VARCHAR(100) OUTPUT)
AS

IF @UpdateDate <> (SELECT UpDateDate FROM MyTable WHERE MyKey = @MyKey) BEGIN
  UPDATE MyTable SET MyData = @MyData, UpdateDate = GETDATE() WHERE MyKey = @MyKey
END ELSE BEGIN
 SET @Result = 'Sorry Charlie.  Someone beat you to the update.'
END
RETURN

If the proc above was called twice at the same instant, both copies could satisfy the IF condition. How can the proc above be modified to be an atomic sequence?

Here's my guess. Get rid of the IF statement (this isn't very flexible)

UPDATE MT
SET MyData = @MyData, UpdateDate = GETDATE()
FROM MyTable MT WITH (LOCK)
WHERE MyKey = @MyKey AND UpdateDate = @UpdateDate

The problem with this solution is: I don't know if the update occured or not so how can I set a return value accordingly?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 17:10:21

Three posts in a row. Feels like I'm muttering to myself here.

How's this?

DECLARE @Doneit BIT
SET @Doneit = 0

UPDATE MT
SET MyData = @MyData, UpdateDate = GETDATE(), @Doneit = 1
FROM MyTable MT WITH (LOCK)
WHERE MyKey = @MyKey AND UpdateDate = @UpdateDate

IF @Doneit = 1 BEGIN -- The update happened

Is all this unnecessary if I bracket the SP body with BEGIN TRAN COMMIT TRAN?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-23 : 20:11:11

Ummm, when I said timestamp I didn't mean a datetime. A SQL Server timestamp column is a database-unique binary datatype (it will be called rowversion after SQL Server 2000). This column gets updated automatically without any intervention from the application (you can't update it directly anyway). Sorry if I didn't make this clear before. See Books Online for the details.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 20:48:35

Thanks Rob,

I played around with timestamp and rowversion after reading your post. Either makes identifying changes to a row's data easier than using DATETIME. Not to mention it's doubtful that DATETIME has the granularity to ensure unique values. Thanks for the clarification.

Still unanswered is the question I raised in my previous post about best locking / atomic mechanism to update the row and know if the update occured or if I was beaten by some other user. Any thoughts?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-23 : 21:12:40

No, there's not much point in locking something when you'll be able to check for updates anyway. You'll only impede concurrency. However, you might want to do some testing to see if there will be a problem. If you're only going to be updating one or a few rows at most, then maybe using a ROWLOCK hint might be a good idea during the UPDATE.

Forgot to add that comparing the timestamp is easy:

CREATE PROCEDURE UpdateRow @key int, @ts timestamp, @col1 varchar(20) AS
IF EXISTS(SELECT * FROM myTable WHERE KeyID=@key AND tsCol<>@ts)
BEGIN
RAISERROR('Someone beat you to it!', 16, 1)
RETURN
END
UPDATE myTable SET col1=@col1
WHERE KeyID=@key AND tsCol=@ts

You could also skip the IF EXISTS test and just include the timestamp in the WHERE clause of the UPDATE, then check the @@ROWCOUNT afterwards. Zero rows will mean the timestamp must've been changed.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 22:32:48

I like skipping the IF EXISTS and going straight to the UPDATE WITH ROWLOCK / @@ROWCOUNT solution. I presume that without ROWLOCK on the UPDATE it isn't atomic and could overlap (in theory) with another UPDATE on the same row?

Thanks,

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-23 : 22:47:39

No, the UPDATE itself is atomic and won't overlap unless you SET ISOLATION LEVEL READ UNCOMMITTED, which would be bad.

The ROWLOCK hint will protect individual rows and prevent lots of concurrent updates from locking pages and interfering with each other.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-23 : 23:04:56

Umm... I don't want this to sound nit-pick, but I'm close enough that I want to be sure I've got this straight, and I'm not sure I do. I have some conflicting perceptions about why ROWLOCK is needed if UPDATE is really atomic in every sense of the word.

What I think you're saying is UPDATE is atomic in that either all rows meeting the update's WHERE condition are updated or not (never some of the rows). But during the execution of the UPDATE, (between the time the WHERE condition evaluates the effected recordset and the data is actually UPDATED) and other processes can modify the data since it isn't locked (ergo - we need ROWLOCK?). Otherwise, why would ROWLOCK be needed?

UPDATE without ROWLOCK (for the above query with WHERE KeyID=@key AND tsCol=@ts) is NOT atomic because the WHERE could be satisfied, yet another user's UPDATE could modify the data, which would result in a change to tsCol for that row BEFORE the update modified the data. Right? In that sense, UPDATE is not an atomic process. Not without ROWLOCK?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-24 : 00:17:24

No. ROWLOCK is there only to keep SQL Server from locking an entire page or table, which would block other updates from occurring until the current update completes. Regardless of locking level, an UPDATE statement is atomic by itself, because it is not wrapped inside a BEGIN TRANSACTION...COMMIT TRANSACTION block.

If someone needs to update a completely different row(s), ROWLOCK will not block them. If they want to update THE SAME row(s) currently being updated by another process, it WILL block them until the original process finishes.

Keep in mind I'm talking about two updates happening at the EXACT same moment, not a situation where two users are browsing the same data and one of them updates long after the other one does. The second person could still successfully update the row and overwrite the first's changes, that's why you want to compare timestamps. When the second person's update comes along, it will find the timestamp has changed and it will not process anything.

If you don't use ROWLOCK, the worst that could happen is that other processes may be blocked unnecessarily by a page or table lock; it will in no way affect the integrity of the data. And as long as you check the timestamp you will not inadvertently overwrite another transaction's update.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-24 : 00:37:18

Thanks for the ROWLOCK 101 ! I think I've got it. For my purposes (updating a single record), the UPDATE will be so short, I can't imagine ROWLOCK being any benefit. I can code a single UPDATE to get this job done.

Thanks and goodnight.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources