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 <[email protected]> escreveu: > Roberto, > > On 14.12.2014 8:37, Roberto Spadim wrote: > >> Hi Elena! >> >> 2014-12-13 21:02 GMT-02:00 Elena Stepanova <[email protected]>: >> >>> >>> 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 > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
