CODEWITHSUNDEEP
phpmysqlpdo
user784637
user784637

Reputation: 16102

Execution order of mysql queries from php script when same script is launched quickly twice

I have a php script that executes mysql pdo queries. There are a few reads and writes to the same table in this script.

For sake of example let's say that there are 4 queries, a read, write, another read, another write, each read takes 10 second to execute, and each write takes .1 seconds to execute.

If I execute this script from the cli nohup php execute_queries.php & twice in 1/100th of a second, what would the execution order of the queries be?

Would all the queries from the first instance of the script need to finish before the queries from the 2nd instance begin to run, or would the first read from both instances start and finish before the table is locked by the write?

NOTE: assume that I'm using myisam and that the write is an update to a record (IE, entire table gets locked during the write.)

Upvotes: 0

Views: 1205

Answers (1)

Mihai Stancu
Mihai Stancu

Reputation: 16107

Since you are not using transactions, then no, the won't wait for all the queries in one script to finish an so the queries may get overlaped.

There is an entire field of study called concurrent programming that teaches this.

In databases it's about transactions, isolation levels and data locks.

Typical (simple) race condition:

$visits = $pdo->query('SELECT visits FROM articles WHERE id = 44')->fetch()[0]['visits'];
/*
 * do some time-consuming thing here
 *
 */
$visits++;
$pdo->exec('UPDATE articles SET visits = '.$visits.' WHERE id = 44');

The above race condition can easily turn sour if 2 PHP processes read the visits from the database one millisecond after the other, and assuming the initial value of visits was 6, both would increment it to 7 and both would write 7 back into the database even though the desired effect was that 2 visits increment the value by 2 (final value of visits should've been 8).

The solution to this is using atomic operations (because the operation is simple and can be reduced to one single atomic operation).

UPDATE articles SET visits = visits+1 WHERE id = 44;

Atomic operations are guaranteed by the database engines to take place uninterrupted by other processes/threads. Usually the database has to queue incoming updates so that they don't affect each other. Queuing obviously slows things down because each process has to wait for all processes before it until it gets the chance to be executed.

In a less simple operation we need more than one statement:

SELECT @visits := visits FROM articles WHERE ID = 44;
SET @visits = @visits+1;
UPDATE articles SET visits = @visits WHERE ID = 44;

But again even at the database level 3 separate atomic statements are not guaranteed to yield an atomic result. They can be overlap with other operations. Just like the PHP example.

To solve this you have to do the following:

START TRANSACTION
    SELECT @visits := visits FROM articles WHERE ID = 44 FOR UPDATE;
    SET @visits = @visits+1;
    UPDATE articles SET visits = @visits WHERE ID = 44;
COMMIT;

Upvotes: 1

Related Questions