| Author |
 Topic  |
|
|
Maleks
Starting Member
United Kingdom
10 Posts |
 Posted - 01/28/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/28/2010 : 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
|
Edited by - Kristen on 01/28/2010 07:48:42 |
 |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 01/28/2010 : 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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/28/2010 : 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?!! |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 01/28/2010 : 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 |
|
|
|
Maleks
Starting Member
United Kingdom
10 Posts |
Posted - 01/28/2010 : 08:23:39
|
Genius - thanks very much!
Id is certainly a PK. |
|
|
| |
Topic |
|
Locking, transactions and errors
