To Tran or not to Tran. That is the 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)

To Tran or not to Tran. That is the question.

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-13 : 08:17:07

I think I asked this question long ago and far away, but hey ...

Does the following check for timestamp before INSERT need to be bracketed with begin tran or not?
[CODE]
BEGIN TRAN
IF EXISTS (SELECT * FROM dbo.ISSOS WHERE ISSOID = @ISSOID AND RowID = @RowID) BEGIN -- Timestamp Valid?
UPDATE dbo.ISSOS -- Mark this row deleted
SET Deleted = 1
WHERE ISSOID = @ISSOID
END ELSE BEGIN
SET @ReturnMessage = 'Row was modified by another user before delete. Try again.'
SET @ReturnValue = 1
END
COMMIT TRAN[/CODE]

such that the update will appear atomic and concurrent users executing the same update will find one is the winner and the other denied?

Column ISSOID is the PK and RowID is TIMESTAMP.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-13 : 12:13:19

Is that part of a larger sproc?

And isn't a timestamp uniqu across all tables?

So what's with the PK and a timestamp?

Looks like this would be a good reason for a trigger though...

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-13 : 13:27:32

The only reason I could see for the BEGIN TRAN etc. is if it's part of a larger proc, which is what Brett said. I don't know that I would agree with the trigger question. Wrapping this in a BEGIN and COMMIT will basically provide any locking you need, which seems to be what you're getting at. ???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-13 : 16:40:28

Since I seem to be the only one that seems to have a problem, I'm probably confused. Situation Normal.

RowID is of type TIMESTAMP and is used to verify that the row to be updated hasn't been updated by another user since the row was read. There's an ASP interface in use by multiple operators, and I suppose it's possible that two operators might be updating the same row. If another user has updated the row values, RowID is automatically changed (it's a TIMESTAMP). The Update procedure checks that the value of RowID has not changed before applying the second user's updates, and returns a denial if there's no match.

So what's the problem. If SQL executes multiple requests at the same time, timeslicing between different execution requests, then the IF EXISTS / UPATE must be an atomic sequence or the test is useless. I thought BEGIN TRAN / COMMIT TRAN might make it atomic.

Sam

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-13 : 22:31:47

Can't you just do this?

UPDATE dbo.ISSOS -- Mark this row deleted
SET Deleted = 1
WHERE
ISSOID = @ISSOID
AND RowID = @RowID

IF @@ROWCOUNT <> 0
BEGIN
SET @ReturnMessage = 'Row was modified by another user before delete. Try again.'
SET @ReturnValue = 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-13 : 22:55:57

Schweeeeeeeeeeet !

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-14 : 00:57:24

Your initial post would not work and the transaction would not make any difference (apart from making the update take longer).
Without a holdlock hint the shared lock taken by the select would be released immediately. Even if it wasn't two spids could take the shared lock at the same time and both pass the test.
The two spids could then both perform the update - the first being overwritten by the second.
For that code to work it would need something like

(holdlock, tablockx) on the select to prevent the other spid performing the select until the update completes.

The code derrickleggett gave should be
IF @@ROWCOUNT = 0

Also be careful about @@rowcount being modified by triggers (depending on version) you might want to set the @ReturnValue in the update instead.

==========================================
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.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-14 : 02:13:52

:) The ROWCOUNT was a dumb error on my part. If he's running SQL Server 2000, this shouldn't be an issue. The code will work. He just needs to make the @@ROWCOUNT = 0.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-14 : 08:14:17

Thanks for the feedback. So BEGIN TRAN / COMMIT TRAN doesn't do anything to make a sequence atomic, but it has other vitures I'm sure.

So I'm OK using Derrick's suggestion. This problem is solved. But I'm wondering if there may be other problems down the road that might involve 2 or 3 SQL statements to work as an atomic unit. Is this not a real problem - if it is, what techniques are available to gate a few statements so user's don't stomp on each other?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-15 : 08:35:40

Transactions will make updates atomic and prevent access to updated records - it's just selects that you need to be careful of. You will need code including some variation of:

begin tran
update tbl
select @rowcount = @@rowcount, @error = @@error
if @error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update tbl
select @rowcount = @@rowcount, @error = @@error
if @error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update tbl
select @rowcount = @@rowcount, @error = @@error
if @error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

==========================================
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.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-15 : 16:30:02

You might want to do some research on nested transactions for this Sam. They can come in really handy with this type of processing. It's pretty amazing what you can accomplish with them for consistency across a process.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-15 : 18:28:59

The problem I had in hand was solved using Derick's UPDATE modification earlier. Nested TRANS? Hadn't thought about it much. I use BEGIN TRAN a little on some interactive work - safety net.

I'd like to see an example of Nigel's suggestion applied to my problem. I didn't get enough traction from his post to complete the syntax myself:

quote:
Originally posted by nr

Without a holdlock hint the shared lock taken by the select would be released immediately. Even if it wasn't two spids could take the shared lock at the same time and both pass the test.
The two spids could then both perform the update - the first being overwritten by the second.
For that code to work it would need something like

(holdlock, tablockx) on the select to prevent the other spid performing the select until the update completes.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-15 : 18:34:21

Then, is this the mechanism Nigel was referring to?
[CODE]
BEGIN TRAN
IF EXISTS (SELECT * FROM dbo.ISSOS WITH HOLDLOCK, ROWLOCK WHERE ISSOID = @ISSOID AND RowID = @RowID) BEGIN -- Timestamp Valid?
UPDATE dbo.ISSOS -- Mark this row deleted
SET Deleted = 1
WHERE ISSOID = @ISSOID
END ELSE BEGIN
SET @ReturnMessage = 'Row was modified by another user before delete. Try again.'
SET @ReturnValue = 1
END
COMMIT TRAN[/CODE]

And the lock on the row is held until the COMMIT TRAN ?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 09:36:27

It's a question you can answer yourself via a simple test using query analyser (hint - you should find it doesn't work).
Create a table

create table a (i int, j int)
insert a select 1, 1

In one window execute
BEGIN TRAN
IF EXISTS (SELECT * FROM a WITH (HOLDLOCK, ROWLOCK) WHERE j = 1 AND i = 1)
begin
select 1
end

That will check for existance and execute the select 1 and hold the locks
Excute the same statement in another window. What you are looking for is for the statement to be blocked.

Try the same thing with (HOLDLOCK, tablockx), (HOLDLOCK, updlock).

To test what you believe will work set a few spids going in a loop doing the update and checking the results.

==========================================
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.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-16 : 09:51:20

Thanks ... Nigel.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources