Roberto, The meaning of an SQL is not engine-dependent. But here you have a timing problem. As Elena explained, Aria allows concurrent inserts to the same table. As a consequence, if MAX(id) is 100, several threads could try to insert 101, resulting in duplicate key errors. Your application should be prepared to handle this, for the same reasons an application should normally be able to handle InnoDB's deadlocks. Regards Federico -------------------------------------------- Dom 14/12/14, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert A: "Elena Stepanova" <elenst@montyprogram.com> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Domenica 14 dicembre 2014, 19:11 Hi elena! I'm considering all engines 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-fa...) 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 -- Roberto Spadim SPAEmpresarialEng. Automação e Controle -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp