Maria DB, locking table for replacement?

Question

I have a web application using a mariaDB10.4.10 INNO_DB table which is updated every 5 minutes from a script. The script is working like:

Create a temp table from a table XY and writing data to the temp table from a received csv file. When the data is written, the script starts a transaction, drop the XY table and rename the temp table to the XY, and commits the transaction.

Nevertheless some times a user gets an "XY table does not exists" error working with the application.

I already tried to LOCK the XY table in the transaction but it doesn't change a thing.

How can I solve this? Is there any kind of locking (I thought locking is no longer possible with INNO_DB?)

Answer 1

Do this another way.

1. Create the temporary table (not as temporary table, as a real table). Fill it as needed. Nobody else knows it's there, you have all the time.
2. SET autocommit = 0; // OR: LOCK TABLE xy WRITE, tempxy READ;
3. DELETE FROM xy;
4. INSERT INTO xy SELECT * FROM tempxy;
5. COMMIT WORK; // OR: UNLOCK TABLES;
6. DROP TABLE tempxy;

This way, other customers will see the old table until point 5, then they'll start seeing the new table.

If you use LOCK, customers will stall from point 2 to point 5, which, depending on time needed, might be bad.

At point #3, in some scenarios you might be able to optimize things by deleting only rows that are not in tempxy, and running an INSERT ON DUPLICATE KEY UPDATE at point 4.

Funnily enough, I answered recently another question that was somewhat like yours.

autoincrement

To prevent autoincrement column from overflowing, you can replace COMMIT WORK with ALTER TABLE xy AUTO_INCREMENT=. This is a dirty hack and relies on the fact that this DDL command in MySQL/MariaDB will execute an implicit COMMIT immediately followed by the DDL command itself. If nobody else inserts in that table, it is completely safe. If somebody else inserts in that table at the exact same time your script is running, it should be safe in MySQL 5.7 and derived releases; it might not be in other releases and flavours, e.g. MySQL 8.0 or Percona.

In practice, you fill up tempxy using a new autoincrement from 1 (since tempxy has been just created), then perform the DELETE/INSERT, and update the autoincrement counter to the count of rows you've just inserted.

To be completely sure, you can use a cooperative lock around the DDL command, on the one hand, and anyone else wanting to perform an INSERT, on the other:

script thread                              other thread

SELECT GET_LOCK('xy-ddl', 30);
                                           SELECT GET_LOCK('xy-ddl', 30);
ALTER TABLE `xy` AUTO_INCREMENT=12345;     # thread waits while
                                           # script thread commits
                                           # and runs DDL
SELECT RELEASE_LOCK('xy-ddl');             # thread can acquire lock
                                           INSERT INTO ...
DROP TABLE tempxy;                         # Gets id = 12346
                                           SELECT RELEASE_LOCK('xy-ddl');