| Bug #9354 | Next-key Locking doesn't allow the insert which does not produce phantom | ||
|---|---|---|---|
| Submitted: | 23 Mar 2005 11:53 | Modified: | 1 Jun 2005 15:57 |
| Reporter: | Shuichi Tamagawa | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.10a | OS: | Windows (WinXP Pro) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[30 Mar 2005 9:28]
Heikki Tuuri
Hi! Assigning this to Jan Lindström. Note that the range that MySQL scans, and InnoDB locks, is not exactly the same as what you have in the WHERE condition. If the range is of type 'a' <= uniquecolum, Jan could check if we already have the optimization to lock only the RECORD, if the record with the column value 'a' exists in a CLUSTERED index. For unique secondary indexes, the optimization does not work, because they are internally not really unique on 'a' because of multiversioning. Jan could add that optimization for the clustered index, if we do not have it yet. Regards, Heikki
[13 Apr 2005 9:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/23949
[1 Jun 2005 15:57]
Heikki Tuuri
Fixed in 5.0.6.
Description: DELETE statement with range scan in a transaction prevents the INSERT from another transaction which does not produce 'phantom'. This happens under the following environment. mysql> show variables like 'tx%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from t1; +-----+ | i | +-----+ | 100 | | 101 | | 102 | +-----+ 3 rows in set (0.00 sec) How to repeat: # Case 1 connection1> start transaction; Query OK, 0 rows affected (0.00 sec) connection2> start transaction; Query OK, 0 rows affected (0.00 sec) connection1> delete from t1 where i >= 100; Query OK, 3 rows affected (0.00 sec) Connection2> insert into t1 values(50); /* Lock Wait*/ # Case 2 Connection1> start transaction; Query OK, 0 rows affected (0.00 sec) Connection2> start transaction; Query OK, 0 rows affected (0.00 sec) Connection1> delete from t1 where i <= 102; Query OK, 3 rows affected (0.01 sec) Connection2> insert into t1 values(10000); /* Lock Wait*/ # Case 3 connection1> start transaction; Query OK, 0 rows affected (0.00 sec) connection2> start transaction; Query OK, 0 rows affected (0.00 sec) connection1> delete from t1 where i between 100 and 102; Query OK, 3 rows affected (0.00 sec) Connection2> insert into t1 values(10000); /* Lock Wait*/ Connection3> insert into t1 values(1); /* Lock Wait*/ Suggested fix: These insert should not be blocked because the inserted value is out of scan range of delete operation.