Modify two tables (insert or update) based on existance of a row in the first table-CodePudding

I have a simple thing to do but somehow can't figure out how to do it. I have to modify two tables (insert or update) based on existance of a row in the first table. There is a possibility that some other process will insert the row with id = 1 between getting the flag value and "if" statement that examines its value. The catch is - I have to change TWO tables based on the flag value. Question: How can I ensure the atomicity of this operation? I could lock both tables by "select with TABLOCKX", modify them and release the lock by committing the transaction but ... won't it be overkill?

  declare @flag int = 0
  begin tran
      select @flag = id from table1 where id = 1

      if @flag = 0 
      begin
        insert table1(id, ...) values(1, ...)
        insert table2(id, ...) values(1, ...)
      end
      else
      begin
        update table1 set colX = ... where id = 1
        update table2 set colX = ... where id = 1
      end
  commit tran

CodePudding user response：

To sumarize our conversation and generalize to other's case :

If your column [id] is either PRIMARY KEY or UNIQUE you can put a Lock on that row. No other process will be able to change the value of [id]

If not, in my opinion you won't have other choice than Lock the table with a TABLOCKX. It will prevent any other process to UPDATE,DELETE or INSERT a row. With that lock, it could possibly allow an other process to SELECT over the table depending on your isolation level.

If your database is in read_committed_snapshot, the other process would read the "old" value of the same [id].

To check your isolation level you can run

SELECT name, is_read_committed_snapshot_on FROM sys.databases