maria-developers team mailing list archive

[Vincent Pelletier <vincent@xxxxxxxxxx>]

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