MonetDB - Transaction aborted issue
All,
We are getting below error while creating a table. In the current session no other bulk load has happened.
CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME" AS SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0;
INSERT INTO "SCHEMA_NAME"."TABLE_NAME" SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2"; ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead ~
Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert?
Any help here is much appreciated!
Regards, Sreejith
On 09/04/18 17:52, Sharma, Sreejith wrote:
All,
We are getting below error while creating a table. In the current session no other bulk load has happened.
CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME"
AS
SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0;
I don't understand what you're trying to do here. If "SCHEMA_NAME"."TABLE_NAME" exists, this should do nothing, and if it doesn't exist, the SELECT has nothing to select from.
INSERT
INTO "SCHEMA_NAME"."TABLE_NAME"
SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2";
ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead
~
Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert?
Any help here is much appreciated!
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi,
The question here is, is it possible to update multiple tables concurrently when auto commit mode is on? As we have noticed do so we get below error. If not, what is the other option to do so? Will the auto commit model off will help for concurrent bulk update?
ERROR = !COMMIT: transaction is aborted because of concurrency > conflicts, will ROLLBACK instead
The below SQL is checking if the table exists, if not it will create. After we got above error, when we try even a simple SQL it will fail.
Regards, Sreejith
-----Original Message----- From: users-list [mailto:users-list-bounces+sreejith.sharma=harman.com@monetdb.org] On Behalf Of Sjoerd Mullender Sent: Monday, April 09, 2018 11:29 PM To: users-list@monetdb.org Subject: [EXTERNAL] Re: MonetDB - Transaction aborted issue
On 09/04/18 17:52, Sharma, Sreejith wrote:
All,
We are getting below error while creating a table. In the current session no other bulk load has happened.
CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME"
AS
SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0;
I don't understand what you're trying to do here. If "SCHEMA_NAME"."TABLE_NAME" exists, this should do nothing, and if it doesn't exist, the SELECT has nothing to select from.
INSERT
INTO "SCHEMA_NAME"."TABLE_NAME"
SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2";
ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead
~
Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert?
Any help here is much appreciated!
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://clicktime.symantec.com/a/1/crZE3lHEGpPtpigsIyNiB6xCQEbDQiPhRfR JysS7oqY=?d=dE5ScmGMu9hBGANL3pkMqzAazkQ_0Wxwl7oqFkatqWHF09iUSkfluzcAwC xzrpBH12SbPdIQ6nCTSHk5hLXcJDxO5Ve4mem5DsYdCU4yboJcvA_O3oM1DFw7onb9FYVc d_yzwNShiHpVE9MuMu325AKKoDNo9IWlQO1buMkDCEqQoSsRvPTRefO62E8H7dUcHdamgI k1q6D_HFN0kX6Jov6u54yv61SI7VWFlDRg8uMYPBHWtoeuJ18TkRrA1xMziqnKWniXEdbW Da25DYKPjYboZaVgv-ZsO9zxE4ulbirBi7Iwbmv6oCydSwLqmaLm5SIAdBvLDLlbKGhiDy hrUl39ZQ0lCyPJA79HY1fracI1XjZRvK1U3cOqMk9Ueu2insMUMhrLNeUQY0CMQ5fjgLRc 6UOjHBfdwW1Z-A9bsJqVHY9H&u=https%3A%2F%2Fwww.monetdb.org%2Fmailman%2Fl istinfo%2Fusers-list
-- Sjoerd Mullender
Updating different tables concurrently is possible.
Creating different/multiple tables --- as in your case --- concurrently is not possible, as that inherently creates conflicts due to concurrent updates on the same (set of) system tables (the SQL catalog), and will consequently result in the mentioned error.
This is independent of whether the auto commit mode is on or off.
If you need/want to learn more about what kind of concurrency is or is not possible please resort to general database / transaction processing literature of your choice. There you'll find details about the differences between pessimistic (lock-based, blocking) vs. optimistic (lock-free, non-blocking) concurrency control protocols. MonetDB uses an optimistic concurrency control protocol.
Best, Stefan
----- On Apr 10, 2018, at 4:11 AM, Sreejith Sharma Sreejith.Sharma@harman.com wrote:
Hi,
The question here is, is it possible to update multiple tables concurrently when auto commit mode is on? As we have noticed do so we get below error. If not, what is the other option to do so? Will the auto commit model off will help for concurrent bulk update?
ERROR = !COMMIT: transaction is aborted because of concurrency > conflicts, will ROLLBACK instead
The below SQL is checking if the table exists, if not it will create. After we got above error, when we try even a simple SQL it will fail.
Regards, Sreejith
-----Original Message----- From: users-list [mailto:users-list-bounces+sreejith.sharma=harman.com@monetdb.org] On Behalf Of Sjoerd Mullender Sent: Monday, April 09, 2018 11:29 PM To: users-list@monetdb.org Subject: [EXTERNAL] Re: MonetDB - Transaction aborted issue
On 09/04/18 17:52, Sharma, Sreejith wrote:
All,
We are getting below error while creating a table. In the current session no other bulk load has happened.
CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME"
AS
SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0;
I don't understand what you're trying to do here. If "SCHEMA_NAME"."TABLE_NAME" exists, this should do nothing, and if it doesn't exist, the SELECT has nothing to select from.
INSERT
INTO "SCHEMA_NAME"."TABLE_NAME"
SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2";
ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead
~
Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert?
Any help here is much appreciated!
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://clicktime.symantec.com/a/1/crZE3lHEGpPtpigsIyNiB6xCQEbDQiPhRfR JysS7oqY=?d=dE5ScmGMu9hBGANL3pkMqzAazkQ_0Wxwl7oqFkatqWHF09iUSkfluzcAwC xzrpBH12SbPdIQ6nCTSHk5hLXcJDxO5Ve4mem5DsYdCU4yboJcvA_O3oM1DFw7onb9FYVc d_yzwNShiHpVE9MuMu325AKKoDNo9IWlQO1buMkDCEqQoSsRvPTRefO62E8H7dUcHdamgI k1q6D_HFN0kX6Jov6u54yv61SI7VWFlDRg8uMYPBHWtoeuJ18TkRrA1xMziqnKWniXEdbW Da25DYKPjYboZaVgv-ZsO9zxE4ulbirBi7Iwbmv6oCydSwLqmaLm5SIAdBvLDLlbKGhiDy hrUl39ZQ0lCyPJA79HY1fracI1XjZRvK1U3cOqMk9Ueu2insMUMhrLNeUQY0CMQ5fjgLRc 6UOjHBfdwW1Z-A9bsJqVHY9H&u=https%3A%2F%2Fwww.monetdb.org%2Fmailman%2Fl istinfo%2Fusers-list
-- Sjoerd Mullender _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Sharma, Sreejith -
Sjoerd Mullender -
Stefan Manegold