| Bug #28570 | handler::index_read() is called with different find_flag when ORDER BY is used | ||
|---|---|---|---|
| Submitted: | 21 May 2007 17:09 | Modified: | 31 Aug 2007 1:56 |
| Reporter: | Vasil Dimov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.1.20-BK | OS: | Any |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_31, bfsm_2007_06_21, bfsm_2007_06_28 | ||
[22 May 2007 12:57]
Vasil Dimov
Bug#28591 may be related to this.
[26 May 2007 7:49]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with latest 5.1.20-BK on Linux.
With ORDER BY:
#0 0x0841704c in ha_innobase::index_read (this=0x8eff140, buf=0x8eff278 "э",
key_ptr=0x8f16be0 "", key_len=5, find_flag=HA_READ_KEY_OR_NEXT)
at ha_innodb.cc:3972
Without ORDER BY:
#0 0x0841704c in ha_innobase::index_read (this=0x8eff178, buf=0x8eff2b0 "э",
key_ptr=0x8f16ae8 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
at ha_innodb.cc:3972
[30 May 2007 13:17]
Martin Hansson
Repeatable in 5.0.42-debug also.
[4 Jun 2007 11:54]
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/commits/28051 ChangeSet@1.2518, 2007-06-04 13:55:30+03:00, mhansson@linux-st28.site +4 -0 bug#28570: handler::index_read() is called with different find_flag when ORDER BY is used Problem 1: When called with a range representing a ref access, the handler calls the engine with the find flag HA_READ_KEY_OR_NEXT when it should use HA_READ_KEY_EXACT, which causes lock waits on InnoDB. This happens when ORDER BY is used along with an equality predicate. Problem 2: The range analysis module does not correctly signal to the handler that a range represents a ref (EQ_RANGE flag) Problem 2 fixed by setting EQ_RANGE for all range accesses that represent an equality predicate. Problem 1 fixed by making the handler call the storage engine with HA_READ_KEY_EXACT when a range really represents a ref.
[13 Aug 2007 9:49]
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/commits/32436 ChangeSet@1.2502, 2007-08-13 11:50:02+02:00, mhansson@linux-st28.site +4 -0 bug#28570: handler::index_read() is called with different find_flag when ORDER BY is used The range analysis module did not correctly signal to the handler that a range represents a ref (EQ_RANGE flag). Fixed by setting EQ_RANGE for all range accesses that represent an equality predicate.
[14 Aug 2007 7:10]
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/commits/32491 ChangeSet@1.2502, 2007-08-14 09:10:20+02:00, mhansson@linux-st28.site +4 -0 bug#28570: handler::index_read() is called with different find_flag when ORDER BY is used The range analysis module did not correctly signal to the handler that a range represents a ref (EQ_RANGE flag). This causes non-range queries like SELECT ... FROM ... WHERE keypart_1=const, ..., keypart_n=const ORDER BY ... FOR UPDATE to wait for a lock unneccesarily if another running transaction uses SELECT ... FOR UPDATE on the same table. Fixed by setting EQ_RANGE for all range accesses that represent an equality predicate.
[24 Aug 2007 7:20]
Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 7:22]
Bugs System
Pushed into 5.0.48
[31 Aug 2007 1:56]
Paul DuBois
Noted in 5.0.48, 5.1.22 changelogs. Non-range queries of the form SELECT ... FROM ... WHERE keypart_1=const, ..., keypart_n=const ORDER BY ... FOR UPDATE sometimes were unneccesarily blocked waiting for a lock if another transaction was using SELECT ... FOR UPDATE on the same table.
Description: Hi, handler::index_read() is called with find_flag=HA_READ_KEY_OR_NEXT when ORDER BY is used and a WHERE clause like secondary_key='exact_value' but it should be called with find_flag=HA_READ_KEY_EXACT as it is when no ORDER BY is specified. This causes unexpected lock waits, see "How to repeat". How to repeat: CREATE TABLE `t` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `bkey` (`b`) ) ENGINE=InnoDB; INSERT INTO t VALUES (1,10), (2,20), (3,30); transaction 1: -- lock one row, no matter if ORDER BY is used SELECT * FROM t WHERE b=20 FOR UPDATE; transaction 2: -- lock wait occurs, without ORDER BY it does not occur SELECT * FROM t WHERE b=10 ORDER BY a FOR UPDATE; Here is snippet from gdb: /* without ORDER BY */ Breakpoint 3, handler::index_read (this=0x805b3d838, buf=0x805b3da20 "�\002", key=0x805a40c58 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at handler.h:1242 /* with ORDER BY */ Breakpoint 3, handler::index_read (this=0x805b3d838, buf=0x805b3da20 "�\002", key=0x805a056c8 "", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT) at handler.h:1242 (notice the value of find_flag)