Bug #65389 MVCC is broken after 5.1 plugin with implicit lock

• Submitted: 22 May 2012 6:29
• Modified: 25 Jun 2012 18:51
• Reporter: Lixun Peng (OCA)
• Status: Closed
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S2 (Serious)
• Version: after 5.5.16
• OS: Any
• CPU Architecture: Any
• Tags: MVCC, regression

[22 May 2012 6:29] Lixun Peng

Description:
Because Primary Key Index use the del-marked entry to store new inserted entry, Secondary Index can't judge implicit lock.

How to repeat:
Fisrt, SET AUTOCOMMIT =0; 

Session 1:

root@localhost : test 02:04:14> drop table test1;create table test1(a int, b int,primary key(a), key idx(b)) engine=innodb;insert into test1 values(1,1);delete from test1;begin;insert into test1 values(1,2);
ERROR 1051 (42S02): Unknown table 'test.test1'
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Session 2: (run it after session 1)
oot@localhost : test 02:04:17> begin;select * from test1 where b=2 lock in share mode;
Query OK, 0 rows affected (0.00 sec)

+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

root@localhost : test 02:04:38> rollback;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
The problem maybe in row0vers.c::row_vers_impl_x_locked_off_kernel

if (vers_del && trx_id != prev_trx_id) {
            mutex_enter(&kernel_mutex);
            break;
}

[22 May 2012 6:35] MySQL Verification Team

I can repeat the problem on all versions of innodb plugin in 5.1 and all versions of 5.5:

session two:
-----------------
mysql> select * from test1 where b=2 lock in share mode;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

With 5.1.63 builtin innodb, session two waits for the lock.

[25 Jun 2012 18:51] John Russell

Added to changelog for 5.1.64, 5.5.26, 5.6.6: 

If a row was deleted from an InnoDB table, then another row was
re-inserted with the same primary key value, an attempt by a
concurrent transaction to lock the row could succeed when it should
have waited. This issue occurred if the locking select used a WHERE
clause that performed an index scan using a secondary index.