How to avoid NULL value in IF EXISTS statement within transaction?

Question

I have thousands of threads executing concurrently. The first type of thread is T-SQL query like this one:

DECLARE @import_id int

IF EXISTS (
        SELECT [id]
        FROM [dbo].[Import]
        WHERE created IS NULL
        )
    SELECT @import_id = [id]
    FROM [dbo].[Import]
    WHERE created IS NULL
ELSE
BEGIN
    INSERT INTO [dbo].[Import] ([startdate])
    SELECT GETDATE()

    SELECT @import_id = @@IDENTITY
END

The second type of thread is T-SQL query like this one:

UPDATE [dbo].[Import] SET created = GETDATE() WHERE created IS NULL

All of these types of T-SQL queries executing in transactions with type Read Commited.

The problem: Sometimes, the first query, returns @import_id = NULL, but I am expecting some value there in @import_id.

The question is how to avoid this case with Read Commited transaction type?

Answer 1

That seems a strange design, but I imagine you've left out a lot.

One way you can fix this is remove the IF test, because the data would appear to be changing between the IF test and the SELECT. So instead:

SELECT @import_id = [id]
FROM [dbo].[Import]
WHERE created IS NULL;

IF @import_id IS NULL BEGIN
    INSERT INTO [dbo].[Import] ([startdate])
    SELECT GETDATE();

    SELECT @import_id = SCOPE_IDENTITY(); -- SCOPE_IDENTITY() is recommended over @@IDENTITY
END;

Note you probably want a TOP 1 and an ORDER BY for your SELECT.

Of course as shown you might obtain a @import_id where created is null but your other thread could set it to GETDATE() even before @import_id is returned to the client. But I assume you've thought of that.