CODEWITHSUNDEEP
sql-server
Derek Jee
Derek Jee

Reputation: 147

Row Locking for update

Can anyone confirm this for me. I need to be able to write to a field in a row an 'ownership' value (Who own's the record) and need it to be the first person who selects the row for update and ignore any further selects until the row is available to write to....

My Transaction will be:

BEGIN TRANSACTION

Declare @OwnerField Varchar(20)

SET @OwnerField = SELECT OwnerField
                   FROM Table
                   WHERE RecordID = 2

IF @OwnerField IS NULL -- Can own

BEGIN 
    UPDATE Table
    SET OwnerField = 'John Smith'
    WHERE RecordID = 2
END

END TRANSACTION

As far as my knowledge goes (with Google's help) this will allow me to lock the row, check if there is a value in it, if not then write one, if so then exit..

Does this make sense?

Thank you in advance..

Derek.

Upvotes: 0

Views: 130

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Unless you want to handle the contention by producing deadlocks, don't use SERIALIZABLE for this. SERIALIZABLE will take and hold Shared (S) locks in the first query, so concurrent transactions will both read the row, and enter into a deadlock as they both try to update it. One will be killed; the other will succeed, and the SERIALIZABLE semantics are preserved.

Instead you should put a restrictive lock on the target row as you read it.

eg:

BEGIN TRANSACTION

Declare @OwnerField Varchar(20)

SET @OwnerField = SELECT OwnerField
                   FROM Table with (UPDLOCK,HOLDLOCK)
                   WHERE RecordID = 2

IF @OwnerField IS NULL -- Can own

BEGIN 
    UPDATE Table
    SET OwnerField = 'John Smith'
    WHERE RecordID = 2
END

END TRANSACTION

(UPDLOCK,HOLDLOCK) gives you the same range-locking protection of the SERIALIZABLE isolation level, but uses a restrictive lock, so multiple transactions will block on the SELECT. The second reader will block until the first has committed, and see the updated OwnerField column.

Upvotes: 2

Related Questions