How to write lock table while inserting data in MySQL

Question

Objective

When user makes a request (REST API) I want to copy N records from table source to table destination.

Current solution

1. Start the transaction.
2. Truncate table destination.
3. Insert into table destination select from source LIMIT N (Single query).
4. Commit the transaction.

Problem

N can be between the range 100000 - 2500000, if the user makes two request one after the other with N records then 2xN records are being inserted.

Example flow:

1. [Action - 1] User makes a request to copy 100 records.
2. [Action - 1] Transaction starts.
3. [Action - 2] User makes a request to copy 200 records.
4. [Action - 1] Truncated table destination.
5. [Action - 2] Transaction starts.
6. [Action - 2] Truncated table destination.
7. [Action - 1] Insert query triggered.(Copy 100 records)
8. [Action - 2] Insert query triggered.(Copy 200 records)
9. [Action - 1] Transaction ends.
10. [Action - 2] Transaction ends.

Result: There are 300 records in destination table.

Desired solution

I want to make sure that at any given point of time only one insertion happens.

1. Start transaction.
2. Lock destination table.
3. Truncate destination table.
4. Insert into destination table.
5. Unlock destination table.
6. End transaction.

Note that when I want to only write lock destination table only, write operations should not happen (but read operation should still happen).

Other details

• MySQL (Database)
• NodeJS (REST API server)
• No stored procedures

Temporary solution

Temporarily we are depending on a flag which we set in database before starting the transaction and then we unset it once the transaction completes. Any other request which arrives while the flag is set they will be terminated with a message (Process already running, please try again later)

Please help me out finding a better solution for this. Let me know if you need more details.

Thanks

Answer 1

You can try the below queries as follow ...

SET AUTOCOMMIT=0;
LOCK TABLES `staff` WRITE;

-- inserts start here
INSERT INTO `staff` VALUES 
(2,'Jon','Snow',4,NULL,'[email protected]',2,1,'Jon',NULL,'2006-02-15 03:57:16'),
(2,'Jon','Stephens',4,NULL,'[email protected]',2,1,'Jon',NULL,'2006-02-15 03:57:16');
-- till here

UNLOCK TABLES;
COMMIT;