right i forgot to set autocommit=0 while testing, i will continue tests with innodb
any others errros about (1),(2),(3),(4) ? 2014-12-15 15:50 GMT-02:00 Federico Razzoli <federico_...@yahoo.it>: > > With InnoDB you will get a deadlock error. Your application will need to > retry the query if you get error 1213. > Don't know about TokuDB, but I suppose it's the same. > > Regards > Federico > > > -------------------------------------------- > Lun 15/12/14, Roberto Spadim <robe...@spadim.com.br> ha scritto: > > Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert > A: "Federico Razzoli" <federico_...@yahoo.it> > Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> > Data: Lunedì 15 dicembre 2014, 18:23 > > 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 <federico_...@yahoo.it>: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 <robe...@spadim.com.br> > ha scritto: > > > > Oggetto: Re: [Maria-discuss] Doubt about 'atomic' > insert > > A: "Elena Stepanova" <ele...@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 <ele...@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 <ele...@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 > > > > > > > > -- > > 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 > > > > > > _______________________________________________ > > 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 > > > > -- > Roberto > Spadim > SPAEmpresarialEng. Automação e > Controle > > > _______________________________________________ > 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 > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ 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
