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@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert
A: "Elena Stepanova" <elenst@montyprogram.com>
Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net>
Data: Domenica 14 dicembre 2014, 19:11
SPAEmpresarialEng. Automação e
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@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 <elenst@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
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