Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Unanswered: commit not working?

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Change your isolation level to "READ COMMITTED" or end the transaction in connection B by issuing a COMMIT before running the select there.

  3. #3
    Join Date
    Jun 2012
    Posts
    4

    Solved!

    Changing the isolation level to read-commited, as suggested, works.

    Thank you for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •