maria-discuss team mailing list archive

[Elena Stepanova <elenst@xxxxxxxxxxxxxxxx>]

Roberto,

On 14.12.2014 8:37, Roberto Spadim wrote:
> Hi Elena!
>
> 2014-12-13 21:02 GMT-02:00 Elena Stepanova <elenst@xxxxxxxxxxxxxxxx>:
> > I suppose you forgot to mention that you are doing it on an Aria table,
> > concurrently, simultaneously from several threads?
> no, i'm considering any engine, just to know when it's possible to a insert
> return duplicate error, why, how, etc... i don't see a standard here, i
> didn't see a standard in others databases too, that's why i'm asking to
> community

Without a context, "no" is a natural answer because nobody can predict 
all preconditions for such a generic question. But the context does 
matter. I know that you raised the question while playing with 
MDEV-7314, others don't.

Aria allows concurrent INSERTs to the same table, while MyISAM does not. 
(https://mariadb.com/kb/en/mariadb/documentation/storage-engines/aria/aria-faq/#advantages-of-aria-compared-to-myisam)

You can see the difference if you modify your initial example to 
something easily "serializable" for the naked eye.

Compare:

*MyISAM*:

# CONNECTION 1

MariaDB [test]> create table t_myisam (pk int primary key) engine=MyISAM;
Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into t_myisam values (1);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t_myisam select max(pk)+sleep(10)+1 from 
t_myisam;

# It starts sleeping


# CONNECTION 2

MariaDB [test]> insert into t_myisam select max(pk)+1 from t_myisam;

# waits until the one in the 1st connection has finished, and then 
inserts the next pk:

# CONNECTION 1

Query OK, 1 row affected (10.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


# CONNECTION 2

Query OK, 1 row affected (8.66 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t_myisam;
+----+
| pk |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)



*Aria*:

# CONNECTION 1

MariaDB [test]> create table t_aria (pk int primary key) engine=Aria;
Query OK, 0 rows affected (0.44 sec)

MariaDB [test]> insert into t_aria values (1);
Query OK, 1 row affected (0.06 sec)

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria;

It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_aria select max(pk)+1 from t_aria;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

# CONNECTION 1

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'


That's why you are getting the duplicate key errors on Aria tables in 
that particular scenario.

Whether it's a bug or not, is another question. I don't see how it can 
work any other way and still allow concurrent INSERTs, but maybe 
somebody who knows more about Aria internals does.


Regards,
Elena