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

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 ??