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.
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 argsDECLARE @Updated DATETIMEDECLARE @EventId INTBEGIN 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 TRANRETURN 1ENDon_error:ROLLBACK TRANRETURN 0Will 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 T1SET PublishSequence = ( SELECT ISNULL(MAX(T2.PublishSequence),0) + 1 FROM tblMessage AS T2 WHERE T2.EventId = T1.EventId ), Status = 5FROM tblMessage AS T1WHERE Id = @Id AND Updated = @LastUpdated |
|
|
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] = 5FROM tblMessage AS T1 OUTER APPLY ( SELECT ISNULL(MAX(T2.PublishSequence),0) + 1 AS [newSeq] FROM tblMessage AS T2 WHERE T2.EventId = T1.EventId ) AS newSeqWHERE 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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?!! |
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Maleks
Starting Member
10 Posts |
Posted - 2010-01-28 : 08:23:39
|
Genius - thanks very much!Id is certainly a PK. |
|
|
|
|
|
|
|