Hi. Bumping this thread with a slightly different finding which may help understanding the issue. I'll use two command lines for this test, "MariaDB 1" and "MariaDB 2", also distinguished by indentation level for readability. Server version: 5.5.30-MariaDB-log MariaDB Server Some setup: MariaDB 1> SET autocommit = 0; Query OK, 0 rows affected (0.00 sec) MariaDB 1> CREATE TABLE `tst` ( -> `uid` int(10) unsigned NOT NULL, -> `foo` smallint(6) NOT NULL DEFAULT '-1', -> PRIMARY KEY (`uid`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected (0.03 sec) MariaDB 1> insert into tst values (1, -2); Query OK, 1 row affected (0.00 sec) MariaDB 1> commit; Query OK, 0 rows affected (0.00 sec) Here starts the actual test: MariaDB 1> select count(*) from tst\G *************************** 1. row *************************** count(*): 1 1 row in set (0.00 sec) MariaDB 2> select count(*) from tst\G *************************** 1. row *************************** count(*): 1 1 row in set (0.00 sec) MariaDB 2> insert into tst values (2, 4), (3, 0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB 1> select count(*) from tst\G *************************** 1. row *************************** count(*): 1 1 row in set (0.00 sec) So far, all good. MariaDB 1> delete from tst; Query OK, 3 rows affected (0.00 sec) Why "3 rows affected" ? MariaDB 1> commit; Query OK, 0 rows affected (0.01 sec) MariaDB 1> select * from tst; Empty set (0.00 sec) The query Kazuhiko started this thread with can happen in parallel with highly-probably-different set of values for the "uid" column (the first column in both tables and index reporting the duplicate key). Probable, but not guaranteed. So a case like this is possible: c 1> delete ... where uid=1 ... (x lines affected) c 2> delete ... where uid=1 ... (y lines affected, none with uid 1) c 1> insert ... values (1, ...), ... c 1> commit (all fine) c 2> insert ... values (1, ...), ... c 2> commit (duplicate key error) I'm surprised by delete's behaviour, as I am mentally applying transaction isolation below what SQL can query, to the btree the table is. Is it just a bad assumption from my part ? If there a way to make this assumption true in mariadb ? Regards, -- Vincent Pelletier ERP5 - open source ERP/CRM for flexible enterprises