Race conditions with Apache, MySQL and PHP

Question

My understanding is that Apache creates a separate PHP process for each incoming request. That means that if I have code that does something like:

check if record exists
if record doesn't exist, create it

Then this is susceptible to a race condition, is it not? If two requests come in at the same time, and they both hit (1) simultaneously, they will both come back false, and then both attempt to insert a new record.

If so, how do people deal with this? Would creating a MySQL transaction around those 2 requests solve the issue, or do we need to do a full table lock?

INSERT IGNORE, LOCK TABLE or using an UNIQUE index on the column you do not want to duplicate are all good candidates. — guido, Mar 08 '13 at 01:08
See also http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Matt Browne, Mar 08 '13 at 01:10

score 2 · Answer 1 · answered Mar 08 '13 at 01:06

As far as I know you cannot create a transaction across different connections. Maybe one solution would be to set column you are checking to be unique. This way if two connections are made to 10, and 10 does not exist. They will both try to create 10. One will finish inserting the row first, and all is well; then the connection just a second behind will fail because the column isn't unique. If you catch the exception that is thrown, then you can subsequently SELECT the record from the database.

score 1 · Answer 2 · answered Mar 08 '13 at 01:33

Honestly, I've very rarely run into this situation. Often times it can be alleviated by reevaluating business requirements. Even if two different users were trying to insert the exact same data, I would defer management of duplicates the users, rather than the application.

However, if there were a reason to enforce a unique constraint in the application logic, I would use an INSERT IGNORE... ON DUPLICATE KEY UPDATE... query (with the corresponding UNIQUE index in the table, of course).

score 0 · Answer 3 · answered Mar 08 '13 at 01:28

I think that handling errors on the second step ought to be sufficient. If two processes try to create a record then one of them will fail, as long as youve configured the MySQL table appropriately. Using UNIQUE across the right fields is one way to do the trick.

score 0 · Answer 4 · edited May 23 '17 at 12:04

0

Apache does not "create a separate PHP process for each incoming request". It either uses a pool of processes (default, prefork mode), or threads.

The race conditions, as you mention, may also be refered to (or cause) DB "Deadlocks". @see what is deadlock in a database?

Using transactions where needed should solve this problem, yes.
By making sure you check if a record exists and create it within a transaction, the whole operation is atomic.
Hence, other requests will not try to create duplicate records (or, depending on the actual queries, create inconsistencies or enter actual deadlocks).

Also note that MySQL does not (yet) support nested transactions: You cannot have transactions inside transactions, as the first commit will commit everything.

edited May 23 '17 at 12:04

Community

1
1

answered Mar 08 '13 at 01:30

J.C. Inacio

4,274
2
19
24

3

this a simple race condition, and has nothing to with deadlocks – Karoly Horvath Mar 08 '13 at 01:38
3

using transactions (alone) won't solve the problem, as two transactions both could see that the record doesn't exist yet. – Karoly Horvath Mar 08 '13 at 01:47
@Karoly Horvath: Yes, edited for a bit more clarity. the example will not cause a deadlock, but race conditions in general may facilitate them, obviously depending on the actual db/code. – J.C. Inacio Mar 08 '13 at 01:50
no, it's still completely misleading.. on many levels. who upvoted this? well, I see one of them... :D – Karoly Horvath Mar 08 '13 at 01:55

Race conditions with Apache, MySQL and PHP

4 Answers4