Debian Wheezy amd64

Here with mariadb 10.0.14 i executed and i have this error:

INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;

/* Erro SQL (1062): Duplicate entry '531' for key 'PRIMARY' */

but... why should this fail ?! isn't INSERT "atomic" in this case?

with myisam,innodb,tokudb i don't have this error

—
could you connect from other mysql and check the MDL values with METADATA_LOCK_INFO information schema plugin? https://mariadb.com/kb/en/mariadb/documentation/plugins/metadata_lock_info/

SELECT * FROM `information_schema`.`METADATA_LOCK_INFO`

in my tests i don't have deadlocks, just error reporting about duplicate keys

i done this:

/// create table before script...

DROP TABLE errorsX;

CREATE TABLE errorsX (

	id SMALLINT(5) NOT NULL,

	PRIMARY KEY (id)

)

ENGINE=Aria ROW_FORMAT=PAGE;

////

SET autocommit=on; // important to innodb engine!

SET @@global.concurrent_insert = 'NEVER'; // just to explain that any value of concurrent_insert don't change the errors, i tested with all values of concurrent_insert

<?php

$pid = pcntl_fork();

$conn = mysqli_connect("localhost", "username", "password");

mysqli_select_db($conn, "database");

while($insertCount < 250) {

	if(mysqli_query($conn, "INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;")) {

		// the INSERT succeeded

		echo ".";

	} else {

		echo("\n".mysqli_error($conn)." --- should never happen!!! \n");

		$insertCount++;

	}

}

?>

with ARIA -> mysql error appears

after that, i execute:

ALTER TABLE errorsX ENGINE=MYISAM;

and executed the same php script
no error , only "...." script run forever (i don't kill the script...)

now, i run:

ALTER TABLE errorsX ENGINE=INNODB;

script runs forever, no error, only "........"
i don't kill the script, and i run:

ALTER TABLE errorsX ENGINE=MEMORY;

script runs forever, no error, only "........"
i don't kill the script, and i run:

ALTER TABLE errorsX ENGINE=ARIA;

and script report error

I added the METADATA_LOCK_INFO table content on the description.

report these variables too (https://mariadb.com/kb/en/mariadb/documentation/storage-engines/aria/aria-two-step-deadlock-detection/) :

deadlock_search_depth_long
deadlock_search_depth_short
deadlock_timeout_long
deadlock_timeout_short

i don't know if anyother variable is relevant (maybe aria_* variables, sql_mode and thread_handling?)

jeanweisbuch,

Do you happen to have binary logging enabled and set to statement or mixed?

Indeed, i have the binlog activated in statement format.
Disabling the log_bin or setting its format to row result in hitting duplicate key errors like Roberto.

For the deadlock with SBR, we have the old bug MDEV-4010. It's good to have another test case though, maybe it will help the problem to start moving.

For the duplicate keys, I don't see an issue here since Aria allows concurrent inserts, and at some moment (quite often, actually) concurrent threads are bound to select exact same MAX(id) and attempt to insert the same new value. However, I'm no expert on Aria, so lets see how the thread on maria-discuss goes. Anyway, if it is a bug, it's a different one.

monty,
Although it's the same problem as in MDEV-4010, I'm not closing it, because it has a different test case (php vs rqg). Please use whichever is more convenient for you, and close both bugs together when done.

The duplicate key is a bug, INSERT ... SELECT should be atomic.
Hitting a duplicate key on such query means that no lock has been issued.

The KB on concurrent INSERTS on MyISAM states that :

If the binary log is used, CREATE TABLE ... SELECT and INSERT ... SELECT statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way the log can be safely used to restore data.

i don't know if this is relevant, and if it's the real problem, reading last comment from Jean...
at MDEV-6817, the "concurrent" insert (or something like it), isn't considered and query cache 'save' an old result set, maybe we don't have the "read lock on the table" when using aria with page row format?

at aria documentation we have another interesting information:
https://mariadb.com/kb/en/mariadb/documentation/storage-engines/aria/aria-storage-formats/

• Slower if there are multiple duplicated keys, as Aria will first write a row, then keys, and only then check for duplicates

i don't know where the read lock occurs, but it's a expected result?

jb-boin,

I don't quite understand the argument about it being atomic. Obviously the transactional definition doesn't apply here, so maybe you can clarify what exactly you mean, and more importantly, how does it apply to the concurrent context?

Duplicate key errors occur on an Aria table without binary logging, so the KB statement about MyISAM and binlog does not apply here.
For more detail, see an example on the mailing list.

Atomic might not be the right word here but such query wont hit duplicates error on other engines and different Aria row formats.

Having the query to hit duplicates that easily means that using "INSERT ... SELECT" taking datas on the table itself has a non negligeable chance to INSERT incorrect datas on the table (eg. if the query was inserting values on a non PK column with an auto-increment PK), it should at least be clearly documented as an engine limitation or maybe having a way to disable the concurrent inserts should be possible.

Anyway it definitely doesnt have anything to do with the original bug.

Back to the original problem, I forgot to mention that technically these deadlocked queries don't hang forever, they will finish with ER_LOCK_WAIT_TIMEOUT after lock_wait_timeout is exceeded. Of course, since lock_wait_timeout = 1 year by default, for all practical purposes it looks like forever. But as a workaround, you can reduce the timeout.

Also, I got a deterministic test case (I will add it to MDEV-4010 as well):

--source include/have_binlog_format_statement.inc

create table t1 (pk int primary key) engine=Aria;

insert into t1 values (1);

send insert into t1 select sleep(2)+1 from t1;

--connect (con1,localhost,root,,)

insert into t1 select 2 from t1;

--connection default

--reap

The issue was that when doing insert into t1 select from t1 from the same table, we are requesting a 'insert concurrent' and a 'read_no_insert' lock from the table.
There was a bug in the lock manager (thr_lock) that allowed a second 'insert_concurrent' lock while we hold a 'read_no_insert' lock and this caused the deadlock.

The reason for having read_no_insert here is to ensure that insert ... select should be atomic when running with binary log enabled.

Fix pushed to 10.0 tree