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

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