Confusion about concurrency with regards to DDL and Insert Into / Select From
I'm very new to MonetDB and am just starting to play around with it. One of the things I'm trying to get my head around is the concurrency model. I've observed some things that make sense and others that I don't understand. 1. Concurrent COPY commands into the same table will cause one or the other to roll back. This makes sense as MonetDB seems to lock at the table level. 2. SELECTing data from a table that concurrently has a COPY command running against it doesn't seem to produce any concurrency errors 3. However, if I INSERT INTO table_B (SELECT ... FROM table_A) while a COPY command is running against table_A, I do get concurrency conflicts 4. Also, if I have a COPY command running into table_A and concurrently issue DDL creating a new table_B, I get a concurrency conflict as well Observations 3+4 seem to indicate that DDL operations and INSERT ... SELECT FROM ... use some sort of database-wide lock. This begs the following questions: a) Is there in fact database-wide locking going on here? b) Is there a workaround that would allow me to issue DDL and make also internal copies of tables while concurrently loading data into other tables? c) Is there something fundamental about the architecture of the database that requires database-wide locking for these ops or is this just a detail of the current implementation that could potentially be ironed out? Thanks! Percy _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hello Percy Thanks for taking the MonetDB tour. On 10/12/12 8:34 PM, Percy Wegmann wrote:
I'm very new to MonetDB and am just starting to play around with it. One of the things I'm trying to get my head around is the concurrency model. I've observed some things that make sense and others that I don't understand.
1. Concurrent COPY commands into the same table will cause one or the other to roll back. This makes sense as MonetDB seems to lock at the table level. The concurrency control scheme is called 'optimistic concurrency control' See for details: http://en.wikipedia.org/wiki/Optimistic_concurrency
2. SELECTing data from a table that concurrently has a COPY command running against it doesn't seem to produce any concurrency errors They are isolated activities.
3. However, if I INSERT INTO table_B (SELECT ... FROM table_A) while a COPY command is running against table_A, I do get concurrency conflicts Yes, that is the basis of OCC.
4. Also, if I have a COPY command running into table_A and concurrently issue DDL creating a new table_B, I get a concurrency conflict as well Conflict on the catalog, most likely.
Observations 3+4 seem to indicate that DDL operations and INSERT ... SELECT FROM ... use some sort of database-wide lock.
This begs the following questions:
a) Is there in fact database-wide locking going on here?
no!
b) Is there a workaround that would allow me to issue DDL and make also internal copies of tables while concurrently loading data into other tables? Serialise your update operations using e.g. a funnel
c) Is there something fundamental about the architecture of the database that requires database-wide locking for these ops or is this just a detail of the current implementation that could potentially be ironed out? MonetDB is primarilly aimed at business analytics, which can deal with a different concurrency control scheme.
Besides, in many instances doing concurrent bulk work not necessarily leads to performance improvement. For example, the COPY command already is highly parallel in the number of cores on your machine. regards, Martin
Thanks! Percy
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Dear Martin,
Thanks for the quick reply.
Cheers,
Percy
On Fri, Oct 12, 2012 at 4:14 PM, Martin Kersten
Hello Percy
Thanks for taking the MonetDB tour.
On 10/12/12 8:34 PM, Percy Wegmann wrote:
I'm very new to MonetDB and am just starting to play around with it. One of the things I'm trying to get my head around is the concurrency model. I've observed some things that make sense and others that I don't understand.
1. Concurrent COPY commands into the same table will cause one or the other to roll back. This makes sense as MonetDB seems to lock at the table level.
The concurrency control scheme is called 'optimistic concurrency control' See for details: http://en.wikipedia.org/wiki/**Optimistic_concurrencyhttp://en.wikipedia.org/wiki/Optimistic_concurrency
2. SELECTing data from a table that concurrently has a COPY command
running against it doesn't seem to produce any concurrency errors
They are isolated activities.
3. However, if I INSERT INTO table_B (SELECT ... FROM table_A) while a
COPY command is running against table_A, I do get concurrency conflicts
Yes, that is the basis of OCC.
4. Also, if I have a COPY command running into table_A and concurrently
issue DDL creating a new table_B, I get a concurrency conflict as well
Conflict on the catalog, most likely.
Observations 3+4 seem to indicate that DDL operations and INSERT ... SELECT FROM ... use some sort of database-wide lock.
This begs the following questions:
a) Is there in fact database-wide locking going on here?
no!
b) Is there a workaround that would allow me to issue DDL and make also
internal copies of tables while concurrently loading data into other tables?
Serialise your update operations using e.g. a funnel
c) Is there something fundamental about the architecture of the database
that requires database-wide locking for these ops or is this just a detail of the current implementation that could potentially be ironed out?
MonetDB is primarilly aimed at business analytics, which can deal with a different concurrency control scheme.
Besides, in many instances doing concurrent bulk work not necessarily leads to performance improvement. For example, the COPY command already is highly parallel in the number of cores on your machine.
regards, Martin
Thanks! Percy
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
______________________________**_________________
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
-- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Martin Kersten -
Percy Wegmann