CODEWITHSUNDEEP
mysqlstored-proceduresconcurrencyinnodb
Simon Baars
Simon Baars

Reputation: 2299

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?

Upvotes: 2

Views: 1207

Answers (2)

e4c5
e4c5

Reputation: 53774

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.

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30839

Ideally, you should do it inside a Transaction, by using 'START TRANSACTION;' and 'COMMIT' syntax. You also need to set autocommit to 0 (it is enabled by default). Here is the documentation for Transactions and autocommit.

As long as your updates are wrapped inside a transaction and appropriate isolation level is set, changes made by other transaction will not affect your transaction.

Upvotes: 1

Related Questions