maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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