3

I seem to be missing something wrt locks in SQL Server 2008. This is my scenario:

  1. Begin tran.
  2. Read from table A to ensure a particular row is found.
  3. While reading, place a read-only lock on the single row read. If not found, throw error.
  4. Insert into table B, which includes a reference to table A.
  5. Commit the tran (releasing the lock).

Due to various design constraints, in this particular instance I cannot create a relationship to manage this for me. So I have to do it with code.

I don't want to XLOCK or UPDLOCK table A as the transaction I am in is only reading from it, not writing. However, obviously I don't want anything else to update / delete the referenced row either. So I need a read-only lock from an external perspective.

I don't want any phantom reads to be possible. I don't want different row versions to be possible, obviously.

Once the tran has committed, it's fine for table A to be modified because a trigger (after delete) will null the reference in table B.

This is what I have:

BEGIN TRAN -- test IF NOT EXISTS ( SELECT 1 FROM Table1 WITH (HOLDLOCK, ROWLOCK) WHERE (ID = @ID) ) {throw} {perform insert into Table2} COMMIT TRAN
Improve this question

1 Answer 1

Reset to default
1

Set your transaction isolation level to REPEATABLE READ for the duration of your transaction. This is also preferable, in my opinion, to using locking HINTS because of the increased clarity of your code implementation.

As you are reading only a single row, you do not need to worry about range inserts to your set, a caveat of REPEATABLE READ.

I suggest reading the "Locking and Latches" chapter in the book Professional SQL Server 2008 Internals and Troubleshooting. It contains excellent explanations, including code examples, of the various Isolation Levels available in SQL Server as well as describing the mechanics of the each of the data anomaly scenarios, such as Phantoms etc.

DISCLAIMER: I'm not on the payroll for this title but I do have two copies, one hard and another on kindle edition, that's how good it is that I bought it twice!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.