2

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?

  • When you say "Rows in B must be consistent with one another", are you trying to ensure that the value in column1 in table A is a count of the rows corresponding to the id column? Check out this on how locking and blocking works in sql server. brentozar.com/sql/locking-and-blocking-in-sql-server – Aaron Nov 6 '18 at 2:08
0

try REPEATABLE READ

on the select statements:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

0

As you are running your updates under transaction(Begin transaction...Commit)the changes to data in the tables will not be committed until it completes all set of statements inside the transaction block.

So you can use below select query to get data consistently from both tables, as the select waits until the transaction completes.so, it will not return uncommitted data from both tables. SELECT a.Id, b.AId FROM B b INNER JOIN A a ON a.Id = b.AId

  • But the select could read a row from A and a subset of corresponding rows from B, then the update deletes the rows from B and add some new ones. The select continues and then has B rows from both independent sets of Bs. In other words, the select can't be happening at the same time as the update. – Ian Warburton Nov 6 '18 at 16:29
  • That's why you should use repeatable read – user1443098 Nov 7 '18 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.