Windows Concurrency Error when accessing two separate tables in two separate mclient instances connected to the same mserver?
Hi, I am using a brand new mserver.exe instance of MonetDB5 on Windows. I am getting the error: "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" that I'm not sure I should? Shouldn't I be able to use the COPY command on two separate CSV files into two separate data tables at once? I don't think this behavior is correct, but maybe I'm wrong? :/
If this is incorrect behavior, should I file a bug report for this?
The files you can use to re-create this error are:
http://downloads.cms.gov/BSAPUF/2008_BSA_PartD_Events_PUF_1.zip
http://downloads.cms.gov/BSAPUF/2008_BSA_PartD_Events_PUF_2.zip
I create an mserver.exe instance with:
# MonetDB 5 server v11.13.9 "Oct2012-SP3" # Serving database 'bsapuf', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50003/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded
Then I ran this command in the first mclient:
CREATE TABLE bsa1 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECISION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_STR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE PRECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ;
copy 1000000 offset 2 records into bsa1 from 'c:\temp\2008_BSA_PartD_Events_PUF_1.csv' using delimiters ',' NULL AS '' ;
And (at the same time) ran this command in the second mclient:
CREATE TABLE bsa2 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECISION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_STR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE PRECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ;
copy 1000000 offset 2 records into bsa2 from 'c:\temp\2008_BSA_PartD_Events_PUF_2.csv' using delimiters ',' NULL AS '' ;
Here were the results. One worked, one didn't:
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\anthonyd.KFF>cd ..
C:\Users>cd ..
C:>cd "Program Files\MonetDB\MonetDB5"
C:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012-SP3) Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsapuf' Type \q to quit, ? for a list of available commands auto commit mode: on sql> sql> sql>CREATE TABLE bsa2 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECI SION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_S TR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE P RECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; operation successful (9.511ms) sql> sql>copy 1000000 offset 2 records into bsa2 from 'c:\temp\2008_BSA_PartD_Event s_PUF_2.csv' using delimiters ',' NULL AS '' ; 1000000 affected rows (42.4s) sql>
XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\anthonyd.KFF>cd ..
C:\Users>cd ..
C:>cd "Program Files\MonetDB\MonetDB5"
C:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012-SP3) Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsapuf' Type \q to quit, ? for a list of available commands auto commit mode: on sql>\d sql>CREATE TABLE bsa1 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECI SION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_S TR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE P RECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; operation successful (8.636ms) sql> sql>copy 1000000 offset 2 records into bsa1 from 'c:\temp\2008_BSA_PartD_Event s_PUF_1.csv' using delimiters ',' NULL AS '' ; 1000000 affected rows (41.6s) COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK in stead sql> sql>
Thanks as always! Anthony
Hi Anthony,
On 22/02/13 19:44, Anthony Damico wrote:
Hi, I am using a brand new mserver.exe instance of MonetDB5 on Windows. I am getting the error: "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" that I'm not sure I should? Shouldn't I be able to use the COPY command on two separate CSV files into two separate data tables at once? I don't think this behavior is correct, but maybe I'm wrong? :/
You can do that, but not with the LOCKED keyword. That says basically, that the COPY INTO is the only thing happening on the DB at that time and that it can forget about all the transactional stuff. Once you are doing two such things, it gets messy. So, either only run one COPY statement or remove LOCKED.
Best,
Hannes (who is still at work writing a paper... you are mentioned in the Acknowledgements!)
Thanks Hannes!!! Sorry, I'm confused because I am _not_ using the LOCKED command in my COPY INTO command (double-check my commands). Do I need to add something to specify I do _not_ want it locked? NOT LOCKED (doesn't work)..
Are you sure the behavior I've described is appropriate? :(
On Fri, Feb 22, 2013 at 1:47 PM, Hannes Mühleisen Hannes.Muehleisen@cwi.nlwrote:
Hi Anthony,
On 22/02/13 19:44, Anthony Damico wrote:
Hi, I am using a brand new mserver.exe instance of MonetDB5 on Windows. I am getting the error: "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" that I'm not sure I should? Shouldn't I be able to use the COPY command on two separate CSV files into two separate data tables at once? I don't think this behavior is correct, but maybe I'm wrong? :/
You can do that, but not with the LOCKED keyword. That says basically, that the COPY INTO is the only thing happening on the DB at that time and that it can forget about all the transactional stuff. Once you are doing two such things, it gets messy. So, either only run one COPY statement or remove LOCKED.
Best,
Hannes (who is still at work writing a paper... you are mentioned in the Acknowledgements!)
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Anthony Damico -
Hannes Mühleisen