Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.15
-
None
-
Debian Wheezy amd64
Description
Running concurrent "INSERT INTO table SELECT MAX(id)+1 FROM table" statements are creating a deadlock on Aria tables using ROW_FORMAT=PAGE (which is the default for Aria).
Step to reproduce
Run this PHP script that :
- Creates an Aria table having a single int column used as a PK
- Run INSERTs on this table from two concurrent processes using the value for id of SELECT MAX(id)+1 on the table itself
Here is the script :
<?php
|
// forking a second process that will run at the same time and do the same thing
|
$pid = pcntl_fork();
|
// the script will abort after 2 seconds of runtime if no new call to pcntl_alarm is done
|
pcntl_alarm(2);
|
|
|
// connect to database
|
$conn = mysqli_connect("localhost", "username", "password");
|
mysqli_select_db($conn, "database");
|
|
|
if($pid == 0) {
|
// we are on the main process, no need for the fork to execute this
|
mysqli_query($conn, "DROP TABLE errorsX;");
|
mysqli_query($conn, "CREATE TABLE errorsX (
|
id SMALLINT(5) NOT NULL,
|
PRIMARY KEY (id)
|
)
|
ENGINE=Aria ROW_FORMAT=PAGE;");
|
mysqli_query($conn, "INSERT INTO errorsX (id) VALUES (1);");
|
} else {
|
// the fork will wait for 0.5sec before starting to INSERT to be sure the main process had time to recreate the table properly
|
usleep(500000);
|
}
|
|
|
$insertCount = 0;
|
while($insertCount < 250) {
|
// the script timer is reset to 2 seconds
|
pcntl_alarm(2);
|
if(mysqli_query($conn, "INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;")) {
|
// the INSERT succeeded
|
echo ".";
|
} else {
|
// the INSERT failed
|
echo("\n".mysqli_error($conn)."\n");
|
}
|
$insertCount++;
|
|
|
// we wait for 0.01s before looping
|
usleep(10000);
|
}
|
|
|
die("\nThe INSERT limit has been reached which indicates that no locking issue has been hit.\n");
|
?>
|
Actual result
The alarm signal is terminating the script as one INSERT loop takes more than 2 seconds to run :
$ php testconcurrentinsert.php
|
...............Alarm clock
|
The table is locked by two INSERT queries waiting for table lock :
MariaDB [testdb]> SHOW FULL PROCESSLIST;
|
+-----+------+-----------+--------+---------+------+------------------------------+--------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+------+-----------+--------+---------+------+------------------------------+--------------------------------------------------------+----------+
|
| 356 | user | localhost | testdb | Query | 7 | Waiting for table level lock | INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX | 0.000 |
|
| 357 | user | localhost | testdb | Query | 7 | Waiting for table level lock | INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX | 0.000 |
|
+-----+------+-----------+--------+---------+------+------------------------------+--------------------------------------------------------+----------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [testdb]> SELECT * FROM information_schema.METADATA_LOCK_INFO;
|
+-----------+-------------------------+-----------------+---------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+-----------------+---------------------+--------------+------------+
|
| 356 | MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT | Global read lock | | |
|
| 357 | MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT | Global read lock | | |
|
| 356 | MDL_SHARED_WRITE | MDL_TRANSACTION | Table metadata lock | testdb | errorsX |
|
| 357 | MDL_SHARED_WRITE | MDL_TRANSACTION | Table metadata lock | testdb | errorsX |
|
+-----------+-------------------------+-----------------+---------------------+--------------+------------+
|
4 rows in set (0.01 sec)
|
These queries will never end until a KILL statement is issued or the server is restarted.
Issuing another INSERT query on the same table will work as long as an aggregate function on the primary key (id) is not used for the value of id.
The issue doesnt appear using ROW_FORMAT=DYNAMIC or on MyISAM, InnoDB and TokuDB engines.
Having the query cache enabled or disabled doesnt have any influence on the issue.
Expected result
Each forks should run 250 INSERTs (a dot is printed at each successful INSERT) and end by outputting the die() informational message such as :
$ php testconcurrentinsert.php
|
...........................................................................................................................................................................................................................................................................................................................................................................................................................................................................
|
The INSERT limit has been reached which indicates that no locking issue has been hit.
|
.........................................
|
The INSERT limit has been reached which indicates that no locking issue has been hit.
|