Lock table for retrieving MAX() id until the max id + 1 is inserted into the table as a new record

Question

Triggered by a web-hook of an e-commerce platform after a new order, I have to update a table with the order's details in a local system. So for the customer's info insertion part, I need to determine whether the customer is an existing one, or should be inserted as a new customer...

So, I wrote this piece of code:

        sqlsrv_query($connection, sprintf("INSERT INTO z_web_users (UserID, OrderID, FirstName, LastName, Country, State, Region, Street, Zip, Phone, Email) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",
            sqlsrv_fetch_array(sqlsrv_query($connection, "SELECT COALESCE((SELECT DISTINCT UserId FROM z_web_users WHERE Email = '{$order->billing->email}'), (SELECT MAX(UserId) + 1 FROM z_web_users), 1) AS ID;"), SQLSRV_FETCH_ASSOC)['ID'],
            $order->id,
            $order->billing->first_name,
            $order->billing->last_name,
            $order->billing->country,
            $states[$order->billing->state],
            $order->billing->city,
            $order->billing->address_1,
            $order->billing->postcode,
            $order->billing->phone,
            $order->billing->email,
        ));

The problem is a got many suggestions that I should add some mechanism to ensure the returned MAX(UserId) stays out of conflicts. Given that the table z_web_users doesn't take any measures by its design, nor do I have any control over its design, what can I do in my code to ensure that?

Answer 1

Well, the first thing I would say is that you should avoid rolling your own id sequence mechanism if at all possible. This is a case of reinventing an existing well oiled wheel.

So, if you really have to roll your own, then I would use a stored procedure with the following content which takes a complete table lock, therefore ensuring only 1 application can access this at a time.

This is a simple way to solve the concurrency issue, and not the most optimal, but it will solve the problem in the short term while you investigate a better solution.

BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRAN;

    DECLARE @Id INT;

    -- Once we get this lock, we have exclusive access to the table until the end of the transaction
    SELECT @Id = COALESCE(MAX(id), (SELECT COALESCE(MAX(id), 0) + 1 FROM Users))
    FROM Users WITH (TABLOCKX)
    WHERE Email = @Email;

    -- Put your original insert here, modified to use the @Id we have already obtained
    INSERT INTO Users (Id, OtherCols...)
        VALUES (@Id, OtherCols...);

    COMMIT;

    RETURN 0;
END;

Note: The Create Procedure Docs provide all you need to know to encapsulate this into a stored procedure.

And you'll need to research how to call a stored procedure from your environment.