Markus Malkusch's weblog 

Entries tagged [mysql]

Deadlocks are not dangerous. Just try again.

by Markus Malkusch


Posted on Sunday Aug 02, 2015 at 06:20PM in Technology


TL;TR

Set the appropriate isolation level, wrap a unit of work into a transaction and do expect it to fail. Therefore I recommend a pattern like this:

for ($i = 3; true; $i--) {
    $pdo->beginTransaction();
    try {

        // Do the unit of work

        $pdo->commit();
        break;

    } catch (\PDOException $e) {
        $pdo->rollback();
        if ($i <= 0) {
            throw $e;
        }
    }
}

The long way

Let's consider this very simple script which should just increase a counter per request:

$increaseCounter = function(\PDO $pdo, $id = 1) {
    $select = $pdo->prepare("SELECT counter FROM counter WHERE id = ?");
    $select->execute([$id]);
    $counter = $select->fetchColumn();

    $counter++;

    $pdo->prepare("UPDATE counter SET counter = ? WHERE id = ?")
        ->execute([$counter, $id]);
};

I'd like to reference the above mentioned script in the further reading simply as $increaseCounter. Also for the purpose of this article we simply ignore that the same can be achived with a single UPDATE query. Let's take this exemplary for any use case where you have a unit of work consisting of several database queries.

So let's emulate 1000 requests and see what happens:

for ($i = 0; $i < 1000; $i++) {
    $increaseCounter($pdo);
}

Nothing unexpected happend. The counter increases sequentially to 1000. But this emulation is not the reality of the web. Requests are not coming sequentially, they are coming concurrently. We can emulate this contention by simply forking the process. As this article is not about forking itself, I won't polute it with that pcntl_* noise and just use for simplicity the spork library.

$concurrency = 4;
$manager = new \Spork\ProcessManager();

for ($i = 0; $i < $concurrency; $i++) {
    $manager->fork(function () use ($concurrency) {

        // each child needs its own connection.
        $pdo = new \PDO("mysql:host=localhost;dbname=test", "test");

        for ($i = 0; $i < 1000 / $concurrency; $i++) {
            $increaseCounter($pdo);
        }
    });
}

Now the counter increased concurrently only to around 500 at my multi core machine. The race condition which happens here is quiet obvious. Multiple processes read the same value while only one of them will effectivly set the counter. The other updates are lost.

Then let's wrap the whole thing into a transaction (assuming we are using a DBS which can do that, i.e. MySQL's InnoDB). That's why we have the I in ACID. To keep the code example readable I will ommit the outer forking skeleton. You can assume for all following examples that there's still those 4 forks created which will run the code concurrently. But let's focus on the actual code of these forks and increase the counter concurrently within transactions.

$manager->fork(function () use ($concurrency) {
    $options = [
        PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_AUTOCOMMIT => false,
    ];
    $pdo = new \PDO("mysql:host=localhost;dbname=test", "test", null, $options);

    for ($i = 0; $i < 1000 / $concurrency; $i++) {
        $pdo->beginTransaction();
        $increaseCounter($pdo);
        $pdo->commit();
    }
});

Suprisingly the result is still not 1000. Why is that so? Let's learn about isolation levels. In our case MySQL uses per default the level REPEATABLE READ. This level is actually quiet consistent, but as our example is not using SELECT ... FOR UPDATE MySQL is still not locking the row. So let's increase the isolation level to SERIALIZABLE and get that counter until 1000. Also I will skip the boiler plate about forking and connecting to the database. Let's concentrate on the transaction and use your imagination for the missing code.

$pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");

for ($i = 0; $i < 1000 / $concurrency; $i++) {
    $pdo->beginTransaction();
    $increaseCounter($pdo);
    $pdo->commit();
}

Unfortunately before reaching 1000 the forked children got killed by an uncaught PDOException. Fortunately the exception tells us very clearly what we can do to reach the 1000:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Ok, let's repeat those failing units of work. Disclaimer: Children, please don't do that at home. There should be a timeout around that loop. We do it here because we just want to see a 1000 and after that we can throw the code away.

$pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");

for ($i = 0; $i < 1000 / $concurrency; $i++) {
    $pdo->beginTransaction();
    try {
	$increaseCounter($pdo);
	$pdo->commit();

    } catch (\PDOException $e) {
        $pdo->rollback();
	$i--; // that's a very subtle loop, is it?
    }
}

Heureka - after repeating every deadlock I can observe a very 1000 in the database. You can avoid that boiler plate by using e.g. TransactionalMutex:

$pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
$mutex = new \malkusch\lock\mutex\TransactionalMutex($pdo);

for ($i = 0; $i < 1000 / $concurrency; $i++) {
    $mutex->synchronized(function () use ($pdo, $increaseCounter) {
	$increaseCounter($pdo);
    });
}

I'd like to finish this article with some warm words from MySQL's manual: How to Cope with Deadlocks

Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.