[Monetdb-developers] JDBC: transaction/savepoint issue
Hello, I am trying to use MonetDB as a backend for Java application I am developing. I have noticed that inserts the application does have no effect. Statements are executed without exceptions, but data rows do not get into table. I have investigated the problem and have figured out that problem is in transaction and savepoint handling. If transaction had savepoints, it could be silently rolled back when Connection.commit() is called. I have finished with short Java program that demonstrate the bug(?), I am attaching it to the message. Please, take a look. I tested with MonetDB-4.8.2+sql-2.8.2 (release, source tarball) and with MonetDB-4.8.3+sql-2.8.3 (nightly build, August 30). Platforms: 32-bit Linux RedHat 9, 32-bit Linux Ubuntu 5.04 -- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com
Hi, Thanks for your interest in MonetDB/SQL. I looked through your example code. I think it can be summarised as follows: Client A Client B setAutoCommit(false) setAutoCommit(false) create table X; commit; (1) savepoint s1; insert y into X; release savepoint s1; select * from X; commit; (2) select * from X; commit; (3) Now the actual problem is that client B doesn't see the inserted row by A. Without commit marked with (1), B does return the row inserted by A. I could limit this problem to a single user problem as follows: (pegasus:~/develop/MonetJDBC) fabian% java -jar jars/MonetDB_JDBC.jar Connection warning: database specifier not supported on this server (mserver_sql), protocol version 4 Welcome to the MonetDB interactive JDBC terminal! Database: MonetDB 4.9.2 Driver: MonetDB Native Driver 1.2 (Blunt+) Type \q to quit, \h for a list of available commands auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> create table x (id int); Operation successful monetdb-> commit; auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> savepoint s1; monetdb-> insert into x values (1); 1 affected row monetdb-> release savepoint s1; monetdb-> select * from x; +------------+ | id | +============+ | 1 | +------------+ 1 row monetdb-> commit; auto commit mode: on monetdb-> select * from x; +------------+ | id | +============+ +------------+ 0 rows monetdb-> It appears that on the last commit, the inserted row is gone. Something which has something to do with the savepoint, because if I omit it, the last select does return my 1. Niels, can you look into this issue? Andrei Martsinchyk wrote:
Hello,
I am trying to use MonetDB as a backend for Java application I am developing. I have noticed that inserts the application does have no effect. Statements are executed without exceptions, but data rows do not get into table. I have investigated the problem and have figured out that problem is in transaction and savepoint handling. If transaction had savepoints, it could be silently rolled back when Connection.commit() is called. I have finished with short Java program that demonstrate the bug(?), I am attaching it to the message. Please, take a look. I tested with MonetDB-4.8.2+sql-2.8.2 (release, source tarball) and with MonetDB-4.8.3+sql-2.8.3 (nightly build, August 30). Platforms: 32-bit Linux RedHat 9, 32-bit Linux Ubuntu 5.04
-- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com
The problem as described here has been fixed in the current branch of MonetDB/SQL. If you pick up the nightlies tomorrow for the "current" branch, this issue should have been fixed. Does this help you Andrei? or do you prefer this fix being backported to the stable branch? Fabian wrote:
Hi,
Thanks for your interest in MonetDB/SQL.
I looked through your example code. I think it can be summarised as follows:
Client A Client B setAutoCommit(false) setAutoCommit(false) create table X; commit; (1) savepoint s1; insert y into X; release savepoint s1; select * from X; commit; (2) select * from X; commit; (3)
Now the actual problem is that client B doesn't see the inserted row by A. Without commit marked with (1), B does return the row inserted by A.
I could limit this problem to a single user problem as follows:
(pegasus:~/develop/MonetJDBC) fabian% java -jar jars/MonetDB_JDBC.jar Connection warning: database specifier not supported on this server (mserver_sql), protocol version 4 Welcome to the MonetDB interactive JDBC terminal! Database: MonetDB 4.9.2 Driver: MonetDB Native Driver 1.2 (Blunt+) Type \q to quit, \h for a list of available commands auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> create table x (id int); Operation successful
monetdb-> commit; auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> savepoint s1;
monetdb-> insert into x values (1); 1 affected row
monetdb-> release savepoint s1;
monetdb-> select * from x; +------------+ | id | +============+ | 1 | +------------+ 1 row
monetdb-> commit; auto commit mode: on monetdb-> select * from x; +------------+ | id | +============+ +------------+ 0 rows
monetdb->
It appears that on the last commit, the inserted row is gone. Something which has something to do with the savepoint, because if I omit it, the last select does return my 1.
Niels, can you look into this issue?
Andrei Martsinchyk wrote:
Hello,
I am trying to use MonetDB as a backend for Java application I am developing. I have noticed that inserts the application does have no effect. Statements are executed without exceptions, but data rows do not get into table. I have investigated the problem and have figured out that problem is in transaction and savepoint handling. If transaction had savepoints, it could be silently rolled back when Connection.commit() is called. I have finished with short Java program that demonstrate the bug(?), I am attaching it to the message. Please, take a look. I tested with MonetDB-4.8.2+sql-2.8.2 (release, source tarball) and with MonetDB-4.8.3+sql-2.8.3 (nightly build, August 30). Platforms: 32-bit Linux RedHat 9, 32-bit Linux Ubuntu 5.04 -- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com
------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
Thanks, Fabian
My application is in Beta quality for now, so it is OK for me to have
it fixed in the current brunch. I am looking forward to check it out.
2005/9/2, Fabian
The problem as described here has been fixed in the current branch of MonetDB/SQL. If you pick up the nightlies tomorrow for the "current" branch, this issue should have been fixed. Does this help you Andrei? or do you prefer this fix being backported to the stable branch?
Fabian wrote:
Hi,
Thanks for your interest in MonetDB/SQL.
I looked through your example code. I think it can be summarised as follows:
Client A Client B setAutoCommit(false) setAutoCommit(false) create table X; commit; (1) savepoint s1; insert y into X; release savepoint s1; select * from X; commit; (2) select * from X; commit; (3)
Now the actual problem is that client B doesn't see the inserted row by A. Without commit marked with (1), B does return the row inserted by A.
I could limit this problem to a single user problem as follows:
(pegasus:~/develop/MonetJDBC) fabian% java -jar jars/MonetDB_JDBC.jar Connection warning: database specifier not supported on this server (mserver_sql), protocol version 4 Welcome to the MonetDB interactive JDBC terminal! Database: MonetDB 4.9.2 Driver: MonetDB Native Driver 1.2 (Blunt+) Type \q to quit, \h for a list of available commands auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> create table x (id int); Operation successful
monetdb-> commit; auto commit mode: on monetdb-> start transaction; auto commit mode: off monetdb-> savepoint s1;
monetdb-> insert into x values (1); 1 affected row
monetdb-> release savepoint s1;
monetdb-> select * from x; +------------+ | id | +============+ | 1 | +------------+ 1 row
monetdb-> commit; auto commit mode: on monetdb-> select * from x; +------------+ | id | +============+ +------------+ 0 rows
monetdb->
It appears that on the last commit, the inserted row is gone. Something which has something to do with the savepoint, because if I omit it, the last select does return my 1.
Niels, can you look into this issue?
Andrei Martsinchyk wrote:
Hello,
I am trying to use MonetDB as a backend for Java application I am developing. I have noticed that inserts the application does have no effect. Statements are executed without exceptions, but data rows do not get into table. I have investigated the problem and have figured out that problem is in transaction and savepoint handling. If transaction had savepoints, it could be silently rolled back when Connection.commit() is called. I have finished with short Java program that demonstrate the bug(?), I am attaching it to the message. Please, take a look. I tested with MonetDB-4.8.2+sql-2.8.2 (release, source tarball) and with MonetDB-4.8.3+sql-2.8.3 (nightly build, August 30). Platforms: 32-bit Linux RedHat 9, 32-bit Linux Ubuntu 5.04 -- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com
------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- -- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com
participants (2)
-
Andrei Martsinchyk -
Fabian