commit doesn't work!
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.