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

Hi Jennie, Thanks for your detailed explanation. Actually I'm using Mapi to copy data and send queries to MonetDB. I send `COPY INTO ... FROM STDIN` to the server to transfer data. I know COPY INTO is much faster, but it requires the file to be local on the server. Currently I can only send data from the clients, and this is why I want to do it through multiple connections. I hope it can speed up the copy process. I'm trying to hack the code so that some transaction checks can be ignored, though it is not easy... Another question is that I'm afraid, for example, the low-level storage of data does not support concurrent copy into one table even if I changed the code. So is that possible theoretically? Regards, Yinjie Lin On Thu, Dec 27, 2018 at 10:33 PM Ying Zhang wrote:

...

On 27 Dec 2018, at 07:59, Yinjie Lin 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-m... , 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@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list