maria-discuss team mailing list archive

[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