maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #02084
Re: Doubt about 'atomic' insert
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
Follow ups
References
