Bug #72072 5.5 Lock

• Submitted: 19 Mar 2014 7:31
• Modified: 14 Apr 2014 13:18
• Reporter: qinglin zhang (OCA)
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Any
• CPU Architecture: Any
• Tags: 5.5 Server Lock

[19 Mar 2014 7:31] qinglin zhang

Description:
insert block when select for update used in another session....

How to repeat:
Session 1:
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

use test;
drop table if exist t1;
create table t1(id int);
begin;
select *from t1 where id < 5 for update;

session 2:
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

use test;
insert into t1 values(8)
...
(block)

[19 Mar 2014 15:11] MySQL Verification Team

Hi,

Couple of questions, for starters ....

1) have you tried 5.6 ??

2) are hyou running your tests in autocommit or non-autocommit mode ???

[24 Mar 2014 9:45] qinglin zhang

HI.

  First, I run testcase in non-autocommit mode;
  Secord, 5.6.16 is OK.

[24 Mar 2014 14:21] MySQL Verification Team

Hi!

Please, confirm !!! You are stating that 5.6 does not behave as 5.5 behaves.

Is that true ???

[14 Apr 2014 5:19] qinglin zhang

yes, it's true

[14 Apr 2014 13:18] MySQL Verification Team

The example that you have provided is the expected behavior. Your first transaction has locked the infimum tuple and your second transaction can not apply the gap lock since the infimum tuple is locked. Once  the SELECT ... FOR UPDATE is over, INSERT will be executed.

You can read more about infimum and supremum tuples in the textbooks on relational databases.