Foreign key constraint conflict concurrency issue

Question

I think the issue I'm having is a concurrency issue, but I guess I don't understand SQL Server transactions well enough to be sure of what to do.

I have a stored procedure which inserts a new row into one table, then uses the ID created by that INSERT to create a record in another table, which has a foreign key constraint on RID:

INSERT INTO tbl_Registrations ...

DECLARE @RID int;
--get newly-created registration ID
SET @RID=(SELECT MAX(RID) FROM tbl_Registrations WHERE ...);
INSERT INTO tbl_RegistrationCertificates VALUES (@RID, 9);

This stored procedure is called from a user interaction on a website, and every time I've tested it, it works fine. However, every once in a while, I'll get an email from our error handler with the following exception:

System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl_RegistrationCertificates_tbl_Registrations". 
The conflict occurred in ... table "dbo.tbl_Registrations", column 'RID'.
The statement has been terminated.

But, either my users are persistent in clicking the "update" button until they see a change, or the transaction is going through even though the message says The statement has been terminated, because, using other information from the email, I can see that both tables have actually been updated.

I know I'm not giving a ton of information here, but the big questions that I can't find answers to are:

1. Does the semicolon on the end of a statement do anything in T-SQL? I just noticed that there's not a semicolon at the end of the INSERT statement. (I normally put them out of habit, but I'm working on systems that other people built.)
2. Does this look like a concurrency issue to people who know more about SQL Server than I do?
3. If it does look like a concurrency issue, can I use transactions to mitigate it, and how do I do that?

EDIT: I just realized the SP isn't what's throwing the error after all. There's another place in the website that's doing an insert without checking to see that the RID exists. Still, I'm glad I asked the questions because I still wouldn't have known the answers otherwise.

Answer 1

T-SQL treats the semicolon as a statement separator, though it wasn't always required. More in-depth discussion on that point can be found in this question.

As for the foreign key error, the stored procedure as you've presented it could use some improvement for a couple of reasons.

First, it's highly likely that you should be using a transaction here. If you don't want a registration record to be added unless a registration certificate record is also added, then the entire operation should be in a single transaction.

Second, SELECTing the MAX value of an identity insert column can easily be a source of concurrency issues if you get simultaneous calls to the stored procedure. Imagine a scenario where thread A INSERTS but can't run its SELECT MAX(RID) before thread B INSERTs. Now both thread A and B will try to INSERT the same value to tbl_RegistrationCertificates.

The SCOPE_IDENTITY() function will help with this by giving you the latest identity value generated from a query within this stored procedure.

Some updated code:

BEGIN TRANSACTION
INSERT INTO tbl_Registrations ...

DECLARE @RID int;
--get newly-created registration ID
SELECT @RID = SCOPE_IDENTITY()
INSERT INTO tbl_RegistrationCertificates VALUES (@RID, 9);
COMMIT TRANSACTION