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 2005 Forums
 Transact-SQL (2005)
 Locking, transactions and errors

Author  Topic 

Maleks
Starting Member

10 Posts

Posted - 2010-01-28 : 07:21:01
Hey guys,

I'm new to locking and errors and just want to run this by you to make sure it'll behave correctly.

The scenario:
Messages in a message table can be published (setting their status). At the point of publishing I need to lock the particular message row, make sure it has not been changed (do not proceed if it has), get a publish sequence (taken from max of publish sequence from the event) and set the message to published.

The SQL:

-- @Id and @LastPublish come in as args

DECLARE @Updated DATETIME
DECLARE @EventId INT

BEGIN TRAN

SELECT
@EventId = EventId,
@Updated = Updated
FROM
tblMessage (UPDLOCK)
WHERE
Id = @Id

IF @Updated <> @LastUpdated
BEGIN
RAISERROR('Attempting to publish changed message', 16, 1)
END

IF @@ERROR <> 0
BEGIN
GOTO on_error
END

SELECT
@PublishSequence = ISNULL(MAX(PublishSequence),0) + 1
FROM
tblMessage
WHERE
EventId = @EventId

UPDATE
tblMessage
SET
PublishSequence = @PublishSequence,
Status = 5
WHERE
Id = @Id

COMMIT TRAN
RETURN 1
END

on_error:
ROLLBACK TRAN
RETURN 0

Will this do the trick?

Alex

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 07:48:09
I would do the UPDATE with a WHERE clause that makes all the other tests, then a) you don't need to lock it ahead of time and b) it is atomic.

After the UPDATE if @@ROWCOUNT = 0 then the update didn't happen, so one of the criteria tests failed (or the row has been deelted )

UPDATE T1
SET PublishSequence =
(
SELECT ISNULL(MAX(T2.PublishSequence),0) + 1
FROM tblMessage AS T2
WHERE T2.EventId = T1.EventId
),

Status = 5
FROM tblMessage AS T1
WHERE
Id = @Id
AND Updated = @LastUpdated
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-28 : 08:16:25
Maybe even:

UPDATE T1 SET
[PublishSequence] = newSeq.[newSeq]
, [Status] = 5
FROM
tblMessage AS T1

OUTER APPLY (
SELECT ISNULL(MAX(T2.PublishSequence),0) + 1 AS [newSeq]
FROM tblMessage AS T2
WHERE T2.EventId = T1.EventId
)
AS newSeq
WHERE
T1.Id = @Id
AND T1.Updated = @LastUpdated

Though if each update only hits one or two rows then there will be no discernible difference.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 08:20:35
"Though if each update only hits one or two rows then there will be no discernible difference."

Dangerous, I know, but I had assumed that ...

WHERE Id = @Id

gotta be PK, no? Surely? ... no?!!
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-28 : 08:22:51
HA. now it is *you* who are making a (probably entirely warranted) assumption!


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Maleks
Starting Member

10 Posts

Posted - 2010-01-28 : 08:23:39
Genius - thanks very much!

Id is certainly a PK.
Go to Top of Page
   

- Advertisement -