Read-Only Lock in SQL Server

Question

It seems like something is missing in SQL Server 2008. This is my scenario:

• Start the transition.
• Read from Table A to verify that a specific row is found.
• When reading, place a fixed record for read-only one line. If not found, run the error.
• Insert in table B, which includes a link to table A.
• Lock transition (release lock).

Due to various design limitations, in this particular case, I cannot create relationships to manage this for me. So I have to do this with code.

I do not want to use the XLOCK or UPDLOCK A table, since the transaction I am working on is just a read, not a write. However, obviously, I do not want anything else to update / delete the link string. Therefore, I need a read-only lock from an external point of view.

I do not want phantom to read that this is possible. I don't want different versions of strings to be possible, obviously.

Once tran has done, this is fine for table A, which will be modified because the trigger (after deletion) resets the link 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

Answer 1

REPEATABLE READ . , , HINTS - .

, , REPEATABLE READ.

" " SQL Server 2008 . , , , SQL Server, , Phantoms ..

: , , Kindle Edition, , , !