0

SQL Table contains a table with a primary key with two columns: ID and Version. When the application attempts to save to the database it uses a command that looks similar to this:

BEGIN TRANSACTION

INSERT INTO [dbo].[FirstTable] ([ID],[VERSION]) VALUES (41,19)
INSERT INTO [dbo].[SecondTable] ([ID],[VERSION]) VALUES (41,19)

COMMIT TRANSACTION

SecondTable has a foreign key constraint to FirstTable and matching column names.

If two computers execute this statement at the same time, does the first computer lock FirstTable and the second computer waits, then when it is finished waiting it finds that the first insert statement fails and throws an error and does not execute the second statement? Is it possible for the second insert to run successfully on both computers?

What is the safest way to ensure that the second computer does not write anything and returns an error to the calling application?

Adam
  • 1,825
  • 1
  • 17
  • 24

3 Answers3

1

Since these are transactions, all operations must be successful otherwise everything gets rolled back. Whichever computer completes the first statement of the transaction first will ultimately complete its transaction. The other computer will attempt to insert a duplicate primary key and fail, causing its transaction to roll back.

Ultimately there will be one row added to both tables, via only one of the computers.

RH6
  • 144
  • 8
0

Ofcourse if there are 2 statements then one of them is going to be executed and the other one will throw an error . In order to avoid this your best bet would be to use either "if exists" or "if not exists" What this does is basically checks to see if there is data already present in the table if not then you insert , else just select.

Take a look at the flow shown below :

if not exists (select * from Table with (updlock, rowlock, holdlock) where 
...)
/* insert */
else
/* update */

commit /* locks are released here */
Jack James
  • 152
  • 1
  • 3
  • 12
0

The transaction did not rollback automatically if an error was encountered. It needed to have 

set xact_abort on

Found this in this question

SQL Server - transactions roll back on error?

Community
  • 1
  • 1
Adam
  • 1,825
  • 1
  • 17
  • 24