maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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_raz@xxxxxxxx>:
>
> 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 <roberto@xxxxxxxxxxxxx> ha scritto:
>
>  Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert
>  A: "Federico Razzoli" <federico_raz@xxxxxxxx>
>  Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>  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_raz@xxxxxxxx>: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@xxxxxxxxxxxxx>
>  ha scritto:
>
>
>
>   Oggetto: Re: [Maria-discuss] Doubt about 'atomic'
>  insert
>
>   A: "Elena Stepanova" <elenst@xxxxxxxxxxxxxxxx>
>
>   Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>
>   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@xxxxxxxxxxxxxxxx>
>
>   escreveu:
>
>   Roberto,
>
>
>
>
>
>
>
>   On 14.12.2014 8:37, Roberto Spadim wrote:
>
>
>
>
>
>   Hi Elena!
>
>
>
>
>
>
>
>   2014-12-13 21:02 GMT-02:00 Elena Stepanova <elenst@xxxxxxxxxxxxxxxx>:
>
>
>
>
>
>
>
>
>
>   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@xxxxxxxxxxxxxxxxxxx
>
>   Unsubscribe : https://launchpad.net/~maria-discuss
>
>   More help   : https://help.launchpad.net/ListHelp
>
>
>
>
>
>  _______________________________________________
>
>  Mailing list: https://launchpad.net/~maria-discuss
>
>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>
>  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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>


-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle