Tzahi Serruya Tzahi Serruya - 2 months ago 11
MySQL Question

Locking table for other script's queries until my php script ends

I have a php script that truncates a table and re-builds it with data from a .csv file. This script has a cronjob running it every 15 minutes.

I have another script which this table's data is very important for it and this script is a bit heavy (might take the server a 10-15 seconds of calculation.

sometimes it happens that the first script happens while the second one is being processed , and returns an error. How can I "lock" this table for the time the first script is running?
Thanx

Answer

Don't truncate and rebuild. That's pointless. Instead rebuild into a new table, then do a quick rename of the new table to the old name, then trash the now old table:

CREATE TABLE _new LIKE old;
TRUNCATE _new;

LOAD DATA INFILE ... INTO _new ...;

RENAME TABLE old TO _old, _new TO old;
DROP TABLE _old;

You'll want to be careful to test this and make sure it doesn't leave you with no data, but the same risk here applies to the TRUNCATE method if it quits without finishing.

Comments