deadlock.php

<?php

$host = '127.0.0.1';
$user = 'root';
$pwd = '';
$db = 'example';
$port = 3306;

// Setup table and data
$conn = new mysqli($host, $user, $pwd, $db, $port);
$conn->execute_query("DROP TABLE IF EXISTS tl_message_queue");
$conn->execute_query("
    CREATE TABLE tl_message_queue (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      delivered_at datetime DEFAULT NULL,
      PRIMARY KEY (id),
      KEY IDX_1E97E1AD16BA31DB (delivered_at)
    ) ENGINE=InnoDB;
");

$conn->execute_query("INSERT INTO tl_message_queue () VALUES()");
$conn->execute_query("INSERT INTO tl_message_queue (delivered_at) VALUES('9999-12-31 23:59:59')");
$conn->close();

// Run queries in parallel using two connections
$conn1 = new mysqli($host, $user, $pwd, $db, $port);
$conn2 = new mysqli($host, $user, $pwd, $db, $port);

$conn1->query("START TRANSACTION");
$id = $conn1->execute_query("SELECT id FROM tl_message_queue WHERE delivered_at < NOW() FOR UPDATE")->fetch_column();

// Run async to not block the PHP thread
// This query will run into the deadlock
$conn2->query("DELETE FROM tl_message_queue WHERE delivered_at = '9999-12-31 23:59:59'", MYSQLI_ASYNC);

$conn1->execute_query("UPDATE tl_message_queue SET delivered_at = NOW() WHERE id = 1");
$conn1->query("COMMIT");

$links = $errors = $reject = array($conn2);
mysqli::poll($links, $errors, $reject, 10);

$conn2->reap_async_query(); // Deadlock found when trying to get lock; try restarting transaction