[Maria-discuss] Doubt about 'atomic' insert
Hi guys i'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT... should this insert select return duplicate key? INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX; considering: ---- CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id) ) ; ---- -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
INSERT SELECT is not standard SQL. But there is no reason to have a duplicate value error. Regards Federico -------------------------------------------- Sab 13/12/14, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: [Maria-discuss] Doubt about 'atomic' insert A: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Sabato 13 dicembre 2014, 19:13 Hi guysi'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT... should this insert select return duplicate key?INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX; considering:----CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id)) ;---- -- Roberto Spadim SPAEmpresarialEng. Automação e 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
Hi, Federico! On Dec 13, Federico Razzoli wrote:
INSERT SELECT is not standard SQL. But there is no reason to have a duplicate value error.
Why do you think it's not? Here, in sql:2003 standard, part 2: <insert statement> ::= INSERT INTO <insertion target> <insert columns and source> <insert columns and source> ::= <from subquery> | <from constructor> | <from default> <from subquery> ::= [ <left paren> <insert column list> <right paren> ] [ <override clause> ] <query expression> <query expression> ::= [ <with clause> ] <query expression body> and so on, from <query expression body> via <query term> to <query primary> to <simple table> to <query specification>, which is <query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression> So, INSERT INTO ... SELECT seems to be a valid standard syntax. Regards, Sergei
-------------------------------------------- Sab 13/12/14, Roberto Spadim <roberto@spadim.com.br> ha scritto:
Oggetto: [Maria-discuss] Doubt about 'atomic' insert A: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Sabato 13 dicembre 2014, 19:13
Hi guysi'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT... should this insert select return duplicate key?INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;
considering:----CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id)) ;----
Hi guys! Nice, a "sql language standard", but... do we have a standard about how should this query be executed? for example... for transactional engines, should it consider the "tx_isolation" variable and execute a particular procedure? for non transactional engines, should them "lock others inserts" execute the query and return max value? or we don't have a standard about how to execute it, and each engine do what's "better"?
some more points, that i read at mysql documentation and mariadb jira (i didn't read the mariadb kb yet) ---- 1) reported at MDEV-7314, from Jean Weisbuch (jean@phpnet.org) ... Hitting a duplicate key on such query means that no lock has been issued. The KB on concurrent INSERTS on *MyISAM* states that : If the binary log is used, CREATE TABLE ... SELECT and INSERT ... SELECT statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way the log can be safely used to restore data. ---- 2) from mysql documentation http://dev.mysql.com/doc/refman/5.7/en/insert-select.html - To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html> statements. The order in which rows are returned by a SELECT <http://dev.mysql.com/doc/refman/5.7/en/select.html> statement with no ORDER BY clause is not determined. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY *column*. The choice of *column* does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 17.4.1.16, “Replication and LIMIT” <http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html>. Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE <http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html> and INSERT IGNORE ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html>statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439) See also Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication” <http://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html>. In MySQL 5.7, an INSERT ... SELECT statement that acted on partitioned tables using a storage engine such asMyISAM <http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html> that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB <http://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html> that employ row-level locking.) See Section 18.6.4, “Partitioning and Locking” <http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html>, for more information. ---- 3) from http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html 17.4.1.16 Replication and LIMIT Statement-based replication of LIMIT clauses in DELETE <http://dev.mysql.com/doc/refman/5.7/en/delete.html>, UPDATE <http://dev.mysql.com/doc/refman/5.7/en/update.html>, and INSERT ... SELECT <http://dev.mysql.com/doc/refman/5.7/en/insert-select.html> statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered: - When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued. Currently, when using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851) - When using MIXED mode, the statement is now automatically replicated using row-based mode. ------ i don't see a standard yet, any idea?
Hi Roberto, On 13.12.2014 21:13, Roberto Spadim wrote:
Hi guys i'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT...
should this insert select return duplicate key? INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;
considering: ---- CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id) ) ; ----
I suppose you forgot to mention that you are doing it on an Aria table, concurrently, simultaneously from several threads? Regards, /E
_______________________________________________ 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
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
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-fa...) 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
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 SPAEmpresarial Eng. Automação e Controle
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 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-fa...) 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
multiple< concurrent inserts
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 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@yahoo.it>:
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
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-fa... )
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@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
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
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@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@yahoo.it>: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 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-fa...) 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@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 -- Roberto Spadim SPAEmpresarialEng. Automação e Controle
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@yahoo.it>:
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@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@yahoo.it>: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
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-fa... )
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@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
-- Roberto Spadim SPAEmpresarialEng. Automação e Controle
_______________________________________________ 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
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Hi, Roberto! On Dec 13, Roberto Spadim wrote:
Hi guys i'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT...
should this insert select return duplicate key? INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;
considering: ---- CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id) ) ; ----
Depends on how many rows the table errorsX has. If it has only one row, than this INSERT ... SELECT will insert a one row with a different id. No error. If it has more than one row, this INSERT ... SELECT will try to insert more than one row, all with the same id value - there will be a duplicate key error. Regards, Sergei
Hi Sergei! 2014-12-13 21:06 GMT-02:00 Sergei Golubchik <serg@mariadb.org>:
Depends on how many rows the table errorsX has.
If it has only one row, than this INSERT ... SELECT will insert a one row with a different id. No error.
If it has more than one row, this INSERT ... SELECT will try to insert more than one row, all with the same id value - there will be a duplicate key error.
Regards, Sergei
well, let me reformulate the query... INSERT INTO errorsX (id) SELECT IFNULL(MAX(id),0)+1 FROM errorsX GROUP BY NULL; considering only a single row result with always >0 not null values
participants (4)
-
Elena Stepanova -
Federico Razzoli -
Roberto Spadim -
Sergei Golubchik