Locking out INSERTs on MySQL

[zensunni]

I want to check for a scenario on the database, and then INSERT data if the scenario is met. However, there is a window between the time of the check and the time of the INSERT for someone else to do a check and INSERT (thus compromising the check). Is there any way to lock the database for the time between the check and INSERT?

If so, what is the best technique? Thanks for any help given.

[ronverdonk]

I really don't understand this "there is a window between the time of the check and the time of the INSERT ". Please elaborate a bit more. If difficult, please show some code on how you try to handle this. And show it within the appropriate code tags.

ronald

[dlite922]

I want to check for a scenario on the database, and then INSERT data if the scenario is met. However, there is a window between the time of the check and the time of the INSERT for someone else to do a check and INSERT (thus compromising the check). Is there any way to lock the database for the time between the check and INSERT?

If so, what is the best technique? Thanks for any help given.

Your right on the dot with the keyword lock.

LOCK TABLE yourTableName READ

or

LOCK TABLE yourTableName WRITE

I think this is what your looking for:

http://forums.mysql.com/read.php?98,180395,180429

Note: READ locks it and other can only read the data and not write to it. Only the thread issuing that lock command can write until UNLOCK TABLE command issued by that thread (or the database is restarted)

if you Execute the WRITE lock, then others can't even read it until you unlock.

try that, i've never actually executed this through PHP, but it doesn't hurt to try.