CODEWITHSUNDEEP
phpdatabaseconcurrencypdo
Lea Hayes
Lea Hayes

Reputation: 64216

PDO and Multiple Query / Concurrency Issues

In PHP I am using PDO to interact with databases. One procedure that commonly takes place consists of multiple queries (several SELECT and UPDATE). This works most of the time, but occasionally the data becomes corrupt where two (or more) instances of the procedure run concurrently.

What is the best way to work around this issue? Ideally I would like a solution which works with the majority of PDO drivers.

Upvotes: 1

Views: 1316

Answers (1)

GordonM
GordonM

Reputation: 31750

Assuming your database back end supports transactions (mysql with InnoDB, Postgres, etc), then simply wrapping the operation in question in a transaction will solve the problem. If one instance of the script is in the middle of the transaction when the second scripts attempts to start it, then the second script's database changes will be queued up and not be attempted until the first transaction completes. This means the database will always be in a valid state provided the transaction starting and committing logic is implemented correctly. 

if ($inTransaction = $pdo -> beginTransaction ())
{
    // Do your selects and updates here. Try to keep this section as short as possible though, as you don't want to keep other pending transactions waiting
    if ($condition_for_success_met)
    {
        $pdo -> commit ();
    }
    else
    {
        $pdo -> rollback ();
    }
}
else
{
    // Couldn't start a transaction. Handle error here
}

Upvotes: 5

Related Questions