Hi guys, i agree with you federico, sql is a language, only, and engine do what it's supossed to do
considering all, could anyone check if i'm right, just to explain the 'standard' that i was looking: 1) aria report duplicate keys errors with row format = page, because of >multiple< concurrent inserts 2) innodb it don't report errors with read-repeatable (maybe with a differente tx_isolation it report i must test) 3) myisam don't report cause it allow only one concurrent insert (not multiple as aria) 4) toku works like innodb if that's right, could we include a topic at KB, to explain how concurrent inserts, and transaction level, could 'change' how inserts are handled? to have a good documentation if not, please point where it's wrong 2014-12-15 10:00 GMT-02:00 Federico Razzoli <[email protected]>: > > 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 <[email protected]> ha scritto: > > Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert > A: "Elena Stepanova" <[email protected]> > Cc: "Maria Discuss" <[email protected]> > 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 <[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 > SPAEmpresarialEng. Automação e > Controle > > -----Segue allegato----- > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > -- 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
