How to perform copy into in multiple clients at the same time?

Ying Zhang Y.Zhang at cwi.nl
Thu Dec 27 15:32:17 CET 2018



> On 27 Dec 2018, at 07:59, Yinjie Lin <exialin37 at gmail.com> wrote:
> 
> Hi,
> 
>    I'm new to MonetDB and I think this is a great project!

Hello Yinjie,

Thanks for using MonetDB.


> 
>    I have a problem the same as this one:
> https://stackoverflow.com/questions/9179265/how-would-monetdb-perform-with-multiple-sql-copy-operations-running-at-the-same?answertab=votes#tab-top
> , namely copy data into tables from multiple clients at the same time.
> 
>    As the stackoverflow question suggests, MonetDB uses Optimistic
> Concurrency Control, but the behaviour is different from my
> expectation. For example, say I have a table `t` without any
> constraints, and I run the following queries:
> 
> client1                                      client2
> -------------------------------------------------------------------
> start transaction;
> insert into t values (1);
>                                           start transaction;
>                                           insert into t values (2);
> commit;
>                                           commit;
> 
> I would expect both commits succeed since the rows to be inserted do
> not conflict with each other and do not change original rows in the
> table. And this is the behaviour of some databases with OCC, e.g. TiDB
> (a MySQL compatible database). But MonetDB would rollback the second
> transaction.

This is because MonetDB doesn’t allow concurrent updates on the same table.  Here is some more information:
https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme

> So my question is: is it possible to change this
> behaviour by modifying the code? If so, could you give me some
> guidance or suggestion?

Changing this behaviour basically means changing the whole transaction manager of MonetDB...
Our recommendation is simply don’t do this.  The best is to serialise the inserts at the client side.
Such concurrency will give you nothing but unnecessary resource contention.

BTW, if data loading speed is important for what you want to do, please try to avoid using INSERT INTO.
COPY INTO is a much faster alternative, and it is highly parallelised internally (which renders concurrent COPY INTO unnecessary).

Regards,
Jennie
> 
> Thanks in advance!
> 
> Best Regards,
> Yinjie Lin
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list



More information about the developers-list mailing list