commit doesn't work!
Posted by: Peter Turk
Date: June 03, 2012 03:43AM

I have a problem in an installation of MySQL. In brief, it appears that commit does not work. Here is a what it happening. I have simplified table definitions to remove unneeded details.

1. Assume this table definition and content:

create table T (ID integer not null, D date, I integer)
insert into T values(1,'1900-01-01',100)
insert into T values(2,'1900-01-01',200)
insert into T values(3,'1900-01-01',300)


2. Create Connection A to the database. Perform this statement:

select from T where date='1900-01-01'

and get this result:

1,'1900-01-01',100
2,'1900-01-01',200
3,'1900-01-01',300


3. While maintaining Connection A, create Connection B to the database (on the same or a different computer). Perform the same select statement as in step 2. and the results are identical.


4. In Connection A, perform these statements:

update T set I=101 where ID=1
commit
select from T where date='1900-01-01'

and get this result:

1,'1900-01-01',101
2,'1900-01-01',200
3,'1900-01-01',300


5. In Connection B, perform this statement:

select from T where date='1900-01-01'

and get this result:

1,'1900-01-01',100
2,'1900-01-01',200
3,'1900-01-01',300

which is incorrect because the first line should contain 101, not 100.


ENVIRONMENT

Windows for both server and client.

The program doing this is an application written entirely in Java.

It uses mysql-connector-java-5.1.16 to connect to the MySQL database.

The server is MySQL 5.5 (probably 5.5.13).

The commit statement in step 4. is being done by using the commit() function in the Java Connection object. The Connection has had auto-commit set to false by calling connection.setAutoCommit(false);

When I perform SELECT @@tx_isolation; on the server, the response is REPEATABLE-READ.

At this point, I don't know where to look. Settings in the server? in mysql-connector?

Any help appreciated.

Options: ReplyQuote


Subject
Written By
Posted
commit doesn't work!
June 03, 2012 03:43AM
June 03, 2012 11:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.