Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: how to generate a phantom read ??

    According to wikipedia Isolation (database systems) - Wikipedia, the free encyclopedia
    i write following to generate phantom reads but failed, please help me to analyze this.


    create table users (id int, name varchar(30),age int) engine=innodb;
    insert into users values (1,'Joe',20),(2,'Jill',25);

    use following

    thread #1
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set transaction isolation level repeatable read;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM users
    -> WHERE age BETWEEN 10 AND 30;
    +------+------+------+
    | id | name | age |
    +------+------+------+
    | 1 | Joe | 20 |
    | 2 | Jill | 25 |
    +------+------+------+
    2 rows in set (0.00 sec)


    then turn to thread#2

    thread#2:
    mysql> INSERT INTO users VALUES ( 3, 'Bob', 27 );
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from users;
    +------+------+------+
    | id | name | age |
    +------+------+------+
    | 1 | Joe | 20 |
    | 2 | Jill | 25 |
    | 3 | Bob | 27 |
    +------+------+------+
    3 rows in set (0.00 sec)

    mysql>


    then back to thread #1

    use select where clause, same row result returned


    mysql> SELECT * FROM users WHERE age BETWEEN 10 AND 30;
    +------+------+------+
    | id | name | age |
    +------+------+------+
    | 1 | Joe | 20 |
    | 2 | Jill | 25 |
    +------+------+------+
    2 rows in set (0.00 sec)

    mysql>



    Any one who can help me ???
    thanks a lot

  2. #2
    Join Date
    Apr 2010
    Posts
    7
    when digging more about this, i found this is caused by mysql configuration, there are two server i tested,
    with same code, server A can generate a phantom reads where another server B cannot, so i copy A's my.cnf to server B and start B mysql server with server A configure then phantom also appear.following is
    the diff output of my.cnf
    /etc/my.cnf server B (can generate phantom reads)
    /root/my.cnf server A (cannot ...)

    csxia-laptop:/home/csxia/Desktop # diff -uNpr /etc/my.cnf /root/my.cnf
    --- /etc/my.cnf 2010-05-06 13:16:54.000000000 +0800
    +++ /root/my.cnf 2010-05-06 13:16:38.000000000 +0800
    @@ -109,7 +109,7 @@ server-id = 1
    #master-port = <port>
    #
    # binary logging - not required for slaves, but recommended
    -log-bin=mysql-bin
    +#log-bin=mysql-bin

    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/
    @@ -126,11 +126,11 @@ innodb_log_group_home_dir = /var/lib/mys
    innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 &#37;
    # of RAM but beware of setting memory usage too high
    -innodb_buffer_pool_size = 16M
    -innodb_additional_mem_pool_size = 2M
    +innodb_buffer_pool_size = 1600M
    +innodb_additional_mem_pool_size = 512M
    # Set .._log_file_size to 25 % of buffer pool size
    -innodb_log_file_size = 5M
    -innodb_log_buffer_size = 8M
    +innodb_log_file_size = 250M
    +innodb_log_buffer_size = 512M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    @@ -148,16 +148,16 @@ no-auto-rehash
    #safe-updates

    [isamchk]
    -key_buffer = 20M
    -sort_buffer_size = 20M
    -read_buffer = 2M
    -write_buffer = 2M
    +key_buffer = 512M
    +sort_buffer_size = 512M
    +read_buffer = 512M
    +write_buffer = 512M

    [myisamchk]
    -key_buffer = 20M
    -sort_buffer_size = 20M
    -read_buffer = 2M
    -write_buffer = 2M
    +key_buffer = 512M
    +sort_buffer_size = 512M
    +read_buffer = 512M
    +write_buffer = 512M

    [mysqlhotcopy]
    interactive-timeout
    csxia-laptop:/home/csxia/Desktop #


    but i have no idea which point in configuration file cause this operation difference ??

Posting Permissions

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