The query

insert into table select max(pk)+1 from table;

Should be executed different with different storage engines? Or the results should be the same?

For example, i consider that

max(pk)+1

Will always get the highest value of pk, and never insert a duplicate value, if it insert a duplicate value, that's not the max() value of pk, or i'm wrong?

Em domingo, 14 de dezembro de 2014, Elena Stepanova <elenst@montyprogram.com> escreveu:

Roberto,

On 14.12.2014 8:37, Roberto Spadim wrote:

Hi Elena!

2014-12-13 21:02 GMT-02:00 Elena Stepanova <elenst@montyprogram.com>:

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