Created
September 30, 2025 21:12
-
-
Save ausi/87c7ccb14858f6c4085264d8a82a5c13 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment