[SOLVED] lock table to prevent insert

[markjoe]

I am having trouble understanding how to use LOCK TABLE.

I need to prevent inserts to a table while a PHP script is using it.

Is LOCK TABLE the right way to do this, or am I better off using my own method, such as a "lockfile"?

I really only need to prevent another user running the PHP script and altering the table while script is already running.

[fenway]

PHP and MySQL are asynchronous... there's no concept of "while the script is running".

 

What exactly are you trying to prevent?

[markjoe]

The PHP script will aquire info from text files, then insert the info into the a table.

It will then get info from more text files and insert into the table acording to the info from the first insert, so to match the individual records. It will then select all the data in the table, process and display it.

Oh, first it needs to delete all records from the table, so it is starting new. The table is only used for temp storage.

This is nearly the same method I've used in the past for similar tasks.

 

The source is up to 2,000 text files, containing up to 50 records each.

 

Problem: A user starts the script, a second user starts the script before the first has finished. The second instance will delete all entries made by the first, attempting to clear it for itself, then both instances will be inserting records into it.

 

With the amount and complexitity of the info, combined with it needing to be processed 2 or 3 times over, I don't see anyway to do it without using a table to hold the info.

After the script is done and the info displayed, the table is no longer needed.

 

Hopefully this makes some sence. The way I see it, "while the script is running" would be the time between it starting and ending.

[fenway]

Oh, first it needs to delete all records from the table, so it is starting new. The table is only used for temp storage.

 

Problem: A user starts the script, a second user starts the script before the first has finished. The second instance will delete all entries made by the first, attempting to clear it for itself, then both instances will be inserting records into it.

 

After the script is done and the info displayed, the table is no longer needed.

So why not use temporary tables, one for each instantiation of the script?

[markjoe]

Yea, I just had that suggested from elsewhere too.

I guess I would give a "CREATE TABLE ..." query using mysql_query(), then drop the table afterwards?

My best guess is to use a microtime stamp in the name, to make it unique.

 

Thanks for the info.

[fenway]

Yea, I just had that suggested from elsewhere too.

I guess I would give a "CREATE TABLE ..." query using mysql_query(), then drop the table afterwards?

My best guess is to use a microtime stamp in the name, to make it unique.

 

Thanks for the info.

No, a "true" TEMPORARY table type, that resides in memory, not on disk.

[markjoe]

Silly me, I was unaware that real temporary tables were built into MySQL.

Just as you should think before speaking, google before posting (or even thinking) is nearly as important.

 

Thanks for the info.