Description:
Select returns an unexpected result.
How to repeat:
Establish two sessions with client to MySQL server, naming SessionA, SessionB respectively.
Execute SQLs in the following order:
1. on SessionA
mysql> create table t1 (a int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
2. on SessionB
mysql> update t1 set a=2 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set a=4 where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+---+
| a |
+---+
| 4 |
+---+
1 row in set (0.00 sec)
3. on SessionA
mysql> update t1 set a=3 where a=2;
# Now the session pending, no output
4. on SessionB
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
5. on SessionA
# Now the pending sql returns
#mysql> update t1 set a=3 where a=2;
Query OK, 1 row affected (5.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 3 |
+---+
2 rows in set (0.00 sec)
## Here, 'select * from t1' returns two records, why?
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
Suggested fix:
In the step 5, 'select * from t1' firstly returns two records,
and after committed, one record is returned.
This is very puzzling.
