Dead-lock issue created by 2 SQL connections, each using transactions, each working with a different table, with a foreign key constraint between the two tables

Question

Environment:

I'm working on a C++ application that uses SQL Native Client 9.0 to communicate with a SQL Server 2000 database.

Scenario:

• 2 connections are opened to the DBMS
• Each connection is set to use transactions
• A query on Connection1 works with TableA
• A query on Connection2 works with TableB
• TableB has a foreign key constraint on the key_id field in TableA

I constructed the function that does the following:

begin a transaction on Connection1 & Connection2
prepare a query in TableA on Connection1
prepare a query on TableB on Connection2

begin loop over some_data
   (1) insert into key_id on TableA

   begin loop over some_other_data
      (2) insert into TableB using same key_id as in Table A
   end loop
end loop

commit on Connection1
commit on Connection2

What I encountered was that query (1) executes successfully but as soon as SQLExecute is called for query (2), the debugger goes off in never-never-land.

The Question:

Am I correctly diagnosing what is happening as a dead-lock issue?

I gathered that because Connection1 is creating a key in TableA but not committing it, then Connection2 is trying to add information to TableB that, because of the foreign key constraint, must have the key present in TableA. Because of this, the SQLExecute query blocks, waiting for the transaction on TableA to complete, which it cannot do until TableB completes its write, thanks to the way the code was written.

Additional Notes:

I can, and have, coded around this issue but I want to make sure my understanding of the problem is correct.

Answer 1

The foreign key constraint on TableB against TableA must check to confirm the key's existence. It will then accept or reject the TableB record.

Because the TableA record containing the key is (on a different connection) not yet commited, the Foreign Key constraint must wait - the insert will not return until the TableA record is committed or rolledback.

Because the commit on the first connection waits for the TableB insert to return... you have deadlock.

In other words, you are correct.