Given table A and table B, such that rows in B reference a row in A...
CREATE TABLE A (Id INT, column1 INT) CREATE TABLE B (Id INT, AId INT)
Rows in B must be consistent with one another. So, when inserting rows in B, I update A within a transactions, delete existing rows and then insert new ones...
BEGIN TRANSACTION UPDATE A SET column1 = 1 WHERE Id = 1 DELETE FROM B WHERE AId = 1 INSERT INTO B (AId) VALUES (1) COMMIT
Question is how to retrieve rows from B without returning rows half way through the above transaction.
SELECT a.Id, b.Id FROM B AS b INNER JOIN A AS a ON a.Id = b.AId
Could the above transaction read elements from two different sets of Bs or do I need to add
WITH (UPDLOCK) on A...?
SELECT a.Id, b.Id FROM B b INNER JOIN A a WITH (UPLOCK) ON a.Id = b.AId
This works. The update transaction can't get going until the read query has been completed. But is it necessary?