Bug #8507 Inconsistent snapshot after commit

• Submitted: 14 Feb 2005 22:19
• Modified: 16 Feb 2005 8:02
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 4.1.9
• OS: Linux (Linux)
• CPU Architecture: Any

[14 Feb 2005 22:19] [ name withheld ]

Description:
The InnoDb transaction engine is highly inconsistent in case of multiple sessions. 

How to repeat:
First issue:  If concurrent transaction fails in another session, the SELECT does not show correct data.
Steps to reproduce:
1. Open shell, login to mysql client. 
2. Create an InnoDb table
Session 1:
3. Insert 3 rows into the newly created table
4. Update one row in the table with new value

Session 2:
5. Update same row updated in session 1 step 4, to a different value
6. Wait until it times out

Session 1:
7. Commit the transaction

Result:
1. Perform SELECT on session 1. This will show the new values
2. Perform SELECT on session 2. It will retain old values. Perform commit/rollback, still shows old values.

[14 Feb 2005 22:34] Heikki Tuuri

Hi!

I am not able to repeat this with 4.1.10 on Linux. Are you using the MySQL query cache?

Please post the complete printout of the problematic runs, as well as your my.cnf.

Session 1:

Your MySQL connection id is 2 to server version: 4.1.10-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t(a int not null primary key, b int) type = innodb;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> insert into t values (1, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (2, 11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (3, 12);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set b = 100 where a = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |  100 |
| 3 |   12 |
+---+------+
3 rows in set (0.01 sec)

mysql>

Session 2:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.10-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> update t set b = 200 where a = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |  100 |
| 3 |   12 |
+---+------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |  100 |
| 3 |   12 |
+---+------+
3 rows in set (0.00 sec)

mysql>

Regards,

Heikki

[16 Feb 2005 8:02] Heikki Tuuri

Hi!

Since there has not been feedback, I am changing the status of this report to Can't repeat.

Regards,

Heikki