Search code examples
mysqlstored-proceduresconcurrencyinnodb

Locking a table for inserts from a MySQL InnoDB Stored Procedure

I am writing a stored procedure. In it I have the following mysql insert statements:

INSERT INTO `Address`(`countryCode`, `addressLine1`, `addressLine2`, `postcode`, `region`, `city`) VALUES (country, addressLine1, addressLine2, postcode, region, city);
INSERT INTO `UserAddress`(`username`, `creationDate`, `addressId`) VALUES (username,NOW(),(SELECT addressId FROM Address ORDER BY addressId DESC LIMIT 1));

As you can see, I'm performing an insert on an Address table, and then use the auto incremented "addressId" in the subquery on the next line. Now, if between these 2 statements another transaction would insert something into the Address table I would insert the wrong addressId into the UserAddress table. My guess would be I need to lock the Address table while these statements are being executed.

After a long search I found the following code to lock the Address table:

SELECT addressId FROM Address FOR UPDATE;

Would this also work for newly inserted rows? If not, what would?

Solution

  • That's exactly why they invented a LAST_INSERT_ID 

    INSERT INTO `Address`(`countryCode`, `addressLine1`, `addressLine2`, `postcode`, `region`, `city`) VALUES (country, addressLine1, addressLine2, postcode, region, city);
INSERT INTO `UserAddress`(`username`, `creationDate`, `addressId`) VALUES (username,NOW(),LAST_INSERT_ID());

    No need for locks or transactions or even stored procedures for the task of grabbing the last id. Just do this in your app.