maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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
SPAEmpresarial
Eng. Automação e Controle