Professional Web Applications Themes

Defect Transactions isolation level - MySQL

Hello all.. Have I found a serious error in mysql's handling of transactions and threads? I am using mysql5 and with the v3 mysql connector (the recommended one from mysql's homepage) NONE of the mysql transaction isolations levels seems to work! with the v5 connector the below code works. However, the environment in which I am to be using this code is mysql 4 and the connector v3... the code spawns two threads which each reads a column, decrements it by 1 and writes it back... unfortunately both threads reads e.g. 10 and writes 9... clearly the end result should ...

  1. #1

    Default Defect Transactions isolation level

    Hello all..

    Have I found a serious error in mysql's handling of transactions and
    threads? I am using mysql5 and with the v3 mysql connector (the
    recommended one from mysql's homepage) NONE of the mysql transaction
    isolations levels seems to work! with the v5 connector the below code
    works. However, the environment in which I am to be using this code is
    mysql 4 and the connector v3...

    the code spawns two threads which each reads a column, decrements it by
    1 and writes it back... unfortunately both threads reads e.g. 10 and
    writes 9... clearly the end result should have been 8! Any comment is
    welcome

    import java.sql.*;
    import java.util.*;

    public class AllInOne {
    Connection con1, con2;

    public AllInOne() throws SQLException, ClassNotFoundException {
    Class.forName("org.gjt.mm.mysql.Driver");
    con1 =
    DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",
    "root");
    con2 =
    DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",
    "root");
    }

    /** create DB table and insert a product */
    private void firstRun() throws SQLException {
    Statement stmt = con1.createStatement();
    stmt.executeUpdate("CREATE TABLE stock( id int, physical int,
    primary key(id))");
    stmt.executeUpdate("INSERT INTO stock VALUES (1, 10)");
    stmt.close();
    }


    public static void main(String[] args) throws Exception {
    Test t = new Test();
    //t.firstRun(); // run only first time to create DB
    User w1, w2;

    t.con1.setAutoCommit(false); t.con2.setAutoCommit(false);

    t.con1.setTransactionIsolation(Connection.TRANSACT ION_SERIALIZABLE);

    t.con2.setTransactionIsolation(Connection.TRANSACT ION_SERIALIZABLE);
    w1 = new User(t.con1, 200, true);
    w2 = new User(t.con2, 600, true);
    w1.start(); w2.start();

    }


    public static class User extends Thread {
    int delay;
    Connection conn;
    String sqlFetch, sqlUpdate;
    boolean commit;

    public User(Connection conn, int delay, boolean commit) {
    this.delay = delay;
    this.conn = conn;
    this.sqlFetch = "SELECT physical FROM stock WHERE id=1";
    this.sqlUpdate = "UPDATE stock SET physical=";
    this.commit = commit;
    }

    public void run() {
    Statement stmt = null;
    try {
    // before
    int result = fetch();
    System.out.println("stock is: "+result + " "
    +this.toString());

    // wait a bit
    synchronized(this) { this.wait(delay); }

    // after
    update(result-1);

    System.out.println("end result: "+ fetch() + " " +
    this);

    if(commit)
    conn.commit();


    }
    catch(Exception e) {
    e.printStackTrace();
    }
    finally {
    if(stmt != null) {
    try {
    stmt.close();
    }
    catch(Exception e) {e.printStackTrace();}
    }

    if(conn != null) {
    try {
    conn.close();
    }
    catch (Exception e) {e.printStackTrace(); }
    }
    }
    }


    int fetch() throws Exception {
    Statement stmt = null;
    try {
    stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sqlFetch);
    int result;
    if(!rs.next())
    throw new Exception("No result from query
    '"+sqlFetch+"'");
    result = rs.getInt(1);
    return result;
    }
    finally {
    if(stmt != null)
    stmt.close();
    }
    }

    void update(int value) throws SQLException {
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sqlUpdate+value);
    }
    }
    }

    carlo Guest

  2. #2

    Default Re: Defect Transactions isolation level

    "carlo" <com> wrote:
     

    No. You detected your ignor^Wmisunderstanding of MySQL :-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)

    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().


    HTH, XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Defect Transactions isolation level

    "carlo" <com> wrote:
     
     

    Thanks for your reply, but lets put your comment to the test.. ! ;-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)


    The table is a innoDB, from version 5 of mysql, as default innoDB is
    used.


    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    yes, that is all nice, but the fact is that such updates are not always
    possible. sometimes updates do require many selects and various
    interactions with user input which is hard to place in one
    transaction.. such as getting user input on several screens before the
    update... I just try to simulate real-life situation.



    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    I can hardly believe that should change the functionality of the
    transactions... and no my password is not root on my machine!



    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    its just setting up the test code...



    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().

    no, in java you invoke start() rather than run. Otherwise you do not
    get a parallel execution of the code.


     

    Are you working for mysql? I think you should have a serious look at
    the jdbc driver implementation for various transactions levels... both
    the v3 and v5 drivers are flawed!

    carlo Guest

  4. #4

    Default Defect Transactions isolation level

    "carlo" <com> wrote:
     
     

    Thanks for your reply, but lets put your comment to the test.. ! ;-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)


    The table is a innoDB, from version 5 of mysql, as default innoDB is
    used.


    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    yes, that is all nice, but the fact is that such updates are not always
    possible. sometimes updates do require many selects and various
    interactions with user input which is hard to place in one
    transaction.. such as getting user input on several screens before the
    update... I just try to simulate real-life situation.



    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    I can hardly believe that should change the functionality of the
    transactions... and no my password is not root on my machine!



    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    its just setting up the test code...



    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().

    no, in java you invoke start() rather than run. Otherwise you do not
    get a parallel execution of the code.


     

    Are you working for mysql? I think you should have a serious look at
    the jdbc driver implementation for various transactions levels... both
    the v3 and v5 drivers are flawed!

    carlo Guest

  5. #5

    Default Defect Transactions isolation level

    "carlo" <com> wrote:
     
     

    Thanks for your reply, but lets put your comment to the test.. ! ;-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)


    The table is a innoDB, from version 5 of mysql, as default innoDB is
    used.


    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    yes, that is all nice, but the fact is that such updates are not always
    possible. sometimes updates do require many selects and various
    interactions with user input which is hard to place in one
    transaction.. such as getting user input on several screens before the
    update... I just try to simulate real-life situation.



    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    I can hardly believe that should change the functionality of the
    transactions... and no my password is not root on my machine!



    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    its just setting up the test code...



    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().

    no, in java you invoke start() rather than run. Otherwise you do not
    get a parallel execution of the code.


     

    Are you working for mysql? I think you should have a serious look at
    the jdbc driver implementation for various transactions levels... both
    the v3 and v5 drivers are flawed!

    carlo Guest

  6. #6

    Default Defect Transactions isolation level

    "carlo" <com> wrote:
     
     

    Thanks for your reply, but lets put your comment to the test.. ! ;-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)


    The table is a innoDB, from version 5 of mysql, as default innoDB is
    used.


    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    yes, that is all nice, but the fact is that such updates are not always
    possible. sometimes updates do require many selects and various
    interactions with user input which is hard to place in one
    transaction.. such as getting user input on several screens before the
    update... I just try to simulate real-life situation.



    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    I can hardly believe that should change the functionality of the
    transactions... and no my password is not root on my machine!



    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    its just setting up the test code...



    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().

    no, in java you invoke start() rather than run. Otherwise you do not
    get a parallel execution of the code.


     

    Are you working for mysql? I think you should have a serious look at
    the jdbc driver implementation for various transactions levels... both
    the v3 and v5 drivers are flawed!

    carlo Guest

  7. #7

    Default Defect Transactions isolation level

    "carlo" <com> wrote:
     
     

    Thanks for your reply, but lets put your comment to the test.. ! ;-)
     

    1. Transactions are supported for InnoDB tables only. You do not
    specify the ENGINE in CREATE TABLE. What is the default engine in
    your MySQL installation? (the default default is MyISAM)


    The table is a innoDB, from version 5 of mysql, as default innoDB is
    used.


    2. Nobody does SELECT + decrement + UPDATE separately. Just use
    UPDATE stock SET physical=physical-1 WHERE id=1
    This is atomic and works across all engines.

    Of course you just did a test. But keep in mind: atomic operations
    save some locks and avoid possible deadlock situations.

    yes, that is all nice, but the fact is that such updates are not always
    possible. sometimes updates do require many selects and various
    interactions with user input which is hard to place in one
    transaction.. such as getting user input on several screens before the
    update... I just try to simulate real-life situation.



    3. You connect to the DBMS as superuser. Never do that (except for
    administrative tasks)!

    I can hardly believe that should change the functionality of the
    transactions... and no my password is not root on my machine!



    4. You INSERT into a table without a column list. This is dangerous!
    Especially if CREATE TABLE and INSERT are in different parts of the
    code. You might change one and forget the other.

    its just setting up the test code...



    5. Your cut&paste is broken. Class User does not have a start() method.
    I guess you meaned run().

    no, in java you invoke start() rather than run. Otherwise you do not
    get a parallel execution of the code.


     

    Are you working for mysql? I think you should have a serious look at
    the jdbc driver implementation for various transactions levels... both
    the v3 and v5 drivers are flawed!

    carlo Guest

  8. #8

    Default Re: Defect Transactions isolation level

    "carlo" <com> wrote:

    [snip]

    Well, if the table is InnoDB and if

    t.con1.setAutoCommit(false);
    t.con1.setTransactionIsolation(Connection.TRANSACT ION_SERIALIZABLE);
    conn.commit();

    really do what their names suggest - then something is broken.
    And since MySQL as such handles transactions and the SERIALIZABLE
    isolation mode, the problem seems to be in connector/J.
     

    Did you check the connector/J docs? I'm not in the Java business,
    maybe you used a yet unsupported feature or hit a known deficiency.

    If you're sure you hit a bug, please submit a bug report at
    http://bugs.mysql.com


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Major Defect in Flash
    By PokerSQA in forum Macromedia Flash Player
    Replies: 0
    Last Post: February 12th, 07:48 PM
  2. Product Defect Query
    By Madison Pruet in forum Informix
    Replies: 7
    Last Post: September 5th, 09:50 PM
  3. Product Defect Query no longer available????
    By Malc P in forum Informix
    Replies: 7
    Last Post: September 3rd, 02:22 AM
  4. Replies: 2
    Last Post: August 18th, 11:04 AM
  5. change defect hdisk0 on rs6000
    By mkoster in forum AIX
    Replies: 8
    Last Post: July 10th, 06:29 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139