SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Locking, transactions and errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maleks
Starting Member

United Kingdom
10 Posts

Posted - 01/28/2010 :  07:21:01  Show Profile  Visit Maleks's Homepage  Reply with Quote
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
22403 Posts

Posted - 01/28/2010 :  07:48:09  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/28/2010 :  08:16:25  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/28/2010 :  08:20:35  Show Profile  Reply with Quote
"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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/28/2010 :  08:22:51  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

United Kingdom
10 Posts

Posted - 01/28/2010 :  08:23:39  Show Profile  Visit Maleks's Homepage  Reply with Quote
Genius - thanks very much!

Id is certainly a PK.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000