Bug #56716 InnoDB locks a record gap without locking the table
Submitted: 10 Sep 2010 15:19 Modified: 10 Dec 2010 0:39
Reporter: Vasil Dimov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0, 5.1, 5.5 OS:Any
Assigned to: Marko Mäkelä CPU Architecture:Any
Tags: crash

[10 Sep 2010 15:19] Vasil Dimov
Description:
mysqld crashes with this backtrace, I will add more analysis later:

#0  0x0000000801631abc in thr_kill () from /lib/libc.so.7
#1  0x00000008016cc3cb in abort () from /lib/libc.so.7
#2  0x0000000000952dd9 in lock_sec_rec_read_check_and_lock (flags=0, 
    block=0x81a6135e0, rec=0x81a894070 "supremum", index=0x81b4ebb78, 
    offsets=0x7ffffe5b0200, mode=LOCK_X, gap_mode=512, thr=0x81ba43220)
    at /usr/local/devel/mysql.git/storage/innobase/lock/lock0lock.c:5336
#3  0x00000000008adc29 in sel_set_rec_lock (block=0x81a6135e0, 
    rec=0x81a894070 "supremum", index=0x81b4ebb78, offsets=0x7ffffe5b0200, mode=3, 
    type=512, thr=0x81ba43220)
    at /usr/local/devel/mysql.git/storage/innobase/row/row0sel.c:983
#4  0x00000000008b25b9 in row_search_for_mysql (buf=0x81b4fae10 "ÿ", mode=4, 
    prebuilt=0x81b4ed378, match_mode=0, direction=0)
    at /usr/local/devel/mysql.git/storage/innobase/row/row0sel.c:3763
#5  0x000000000088fdeb in ha_innobase::index_read (this=0x81b4fac10, 
    buf=0x81b4fae10 "ÿ", key_ptr=0x81b53c040 "", key_len=2, 
    find_flag=HA_READ_PREFIX_LAST_OR_PREV)
    at /usr/local/devel/mysql.git/storage/innobase/handler/ha_innodb.cc:5631
#6  0x00000000007138b6 in handler::index_read_map (this=0x81b4fac10, 
    buf=0x81b4fae10 "ÿ", key=0x81b53c040 "", keypart_map=1, 
    find_flag=HA_READ_PREFIX_LAST_OR_PREV) at handler.h:1487
#7  0x00000000007f6861 in QUICK_SELECT_DESC::get_next (this=0x81a5b3880)
    at /usr/local/devel/mysql.git/sql/opt_range.cc:8966
#8  0x000000000081069e in rr_quick (info=0x7ffffe5b0f50)
    at /usr/local/devel/mysql.git/sql/records.cc:335
#9  0x000000000064a09b in mysql_update (thd=0x81b191000, table_list=0x81b4de140, 
    fields=@0x81b1931b0, values=@0x81b193588, conds=0x81b4de978, order_num=1, 
    order=0x81b4debe0, limit=2, handle_duplicates=DUP_ERROR, ignore=false, 
    found_return=0x7ffffe5b1ed0, updated_return=0x7ffffe5b1ec8)
    at /usr/local/devel/mysql.git/sql/sql_update.cc:477
#10 0x00000000005b4851 in mysql_execute_command (thd=0x81b191000)
    at /usr/local/devel/mysql.git/sql/sql_parse.cc:2812
#11 0x00000000005b9d19 in mysql_parse (thd=0x81b191000, 
    rawbuf=0x81b4de010 "UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2", length=64, parser_state=0x7ffffe5b27d0)
    at /usr/local/devel/mysql.git/sql/sql_parse.cc:5594
#12 0x00000000005ba8b4 in dispatch_command (command=COM_QUERY, thd=0x81b191000, 
    packet=0x81b4cc001 "UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2", packet_length=64) at /usr/local/devel/mysql.git/sql/sql_parse.cc:1139
#13 0x00000000005bbca8 in do_command (thd=0x81b191000)
    at /usr/local/devel/mysql.git/sql/sql_parse.cc:811
#14 0x000000000068847c in do_handle_one_connection (thd_arg=0x81b191000)
    at /usr/local/devel/mysql.git/sql/sql_connect.cc:1192
#15 0x000000000068857d in handle_one_connection (arg=0x81b191000)
    at /usr/local/devel/mysql.git/sql/sql_connect.cc:1131
#16 0x000000000086fb82 in pfs_spawn_thread (arg=0x81a568f10)
    at /usr/local/devel/mysql.git/storage/perfschema/pfs.cc:1015
#17 0x0000000800e60511 in pthread_getprio () from /lib/libthr.so.3
#18 0x0000000000000000 in ?? ()

How to repeat:
Compile mysql-5.5 with WITH_DEBUG (this will enable InnoDB's UNIV_DEBUG), then run:

./mtr main.single_delete_update
(mysqld crashes)

Suggested fix:
not yet
[10 Sep 2010 15:25] Vasil Dimov
5298 lock_sec_rec_read_check_and_lock(
5299 /*=============================*/
5300         ulint                   flags,  /*!< in: if BTR_NO_LOCKING_FLAG
5301                                         bit is set, does nothing */
5302         const buf_block_t*      block,  /*!< in: buffer block of rec */
5303         const rec_t*            rec,    /*!< in: user record or page
5304                                         supremum record which should
5305                                         be read or passed over by a
5306                                         read cursor */
5307         dict_index_t*           index,  /*!< in: secondary index */
5308         const ulint*            offsets,/*!< in: rec_get_offsets(rec, index) */
5309         enum lock_mode          mode,   /*!< in: mode of the lock which
5310                                         the read cursor should set on
5311                                         records: LOCK_S or LOCK_X; the
5312                                         latter is possible in
5313                                         SELECT FOR UPDATE */
5314         ulint                   gap_mode,/*!< in: LOCK_ORDINARY, LOCK_GAP, or
5315                                         LOCK_REC_NOT_GAP */
5316         que_thr_t*              thr)    /*!< in: query thread */
5317 {
5318         enum db_err     err;
5319         ulint           heap_no;
5320 
5321         ut_ad(!dict_index_is_clust(index));
5322         ut_ad(block->frame == page_align(rec));
5323         ut_ad(page_rec_is_user_rec(rec) || page_rec_is_supremum(rec));
5324         ut_ad(rec_offs_validate(rec, index, offsets));
5325         ut_ad(mode == LOCK_X || mode == LOCK_S);
5326 
5327         if (flags & BTR_NO_LOCKING_FLAG) {
5328 
5329                 return(DB_SUCCESS);
5330         }
5331 
5332         heap_no = page_rec_get_heap_no(rec);
5333 
5334         lock_mutex_enter_kernel();
5335 
5336         ut_ad(mode != LOCK_X
5337               || lock_table_has(thr_get_trx(thr), index->table, LOCK_IX));

it crashes on line 5337

(gdb) ins mode
$5 = LOCK_X

(gdb) ins lock_table_has(thr_get_trx(thr), index->table, LOCK_IX)
$6 = (ib_lock_t *) 0x0

the table has no locks on it at all:
(gdb) ins index->table->locks.end
$7 = (ib_lock_t *) 0x0

in frame 4:

                        err = sel_set_rec_lock(btr_pcur_get_block(pcur),
                                               next, index, offsets,
                                               prebuilt->select_lock_type,
                                               LOCK_GAP, thr);

(gdb) ins prebuilt->select_lock_type
$8 = 3  // LOCK_X

// the table does not have any locks on it:
(gdb) ins index->table->locks.end
$9 = (ib_lock_t *) 0x0
[10 Sep 2010 15:27] Vasil Dimov
Could be related to Bug#24919 InnoDB: lock_clust_rec_read_check_and_lock() leads to slave crash
[10 Sep 2010 15:39] Vasil Dimov
Easier way to reproduce:

CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  c1_idx CHAR(1) DEFAULT 'y',
  c2 INT,
  PRIMARY KEY (pk),
  INDEX c1_idx (c1_idx)
) ENGINE=InnoDB;

UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;

(must be compiled with UNIV_DEBUG)
[22 Sep 2010 7:08] Marko Mäkelä
The "easier way to reproduce" does not crash in 5.1 (builtin or plugin), but does in 5.5.
[22 Sep 2010 11:06] Marko Mäkelä
The InnoDB function row_search_for_mysql() should acquire a table lock before acquiring a gap lock. This bug was introduced in MySQL 5.0 and 5.1 when fixing Bug #27197:

------------------------------------------------------------------------
r1452 | vasil | 2007-04-20 18:41:06 +0300 (Fri, 20 Apr 2007) | 6 lines

Fix phantom reads (http://bugs.mysql.com/27197) following Heikki's
patch in the bug followup.

Approved by:	Heikki
------------------------------------------------------------------------
r1456 | vasil | 2007-04-21 20:14:07 +0300 (Sat, 21 Apr 2007) | 6 lines

branches/5.0: merge r1452 from trunk:

Fix phantom reads (http://bugs.mysql.com/27197) following Heikki's
patch in the bug followup.
[22 Sep 2010 11:28] Marko Mäkelä
In MySQL 5.5, this bug was triggered because of Bug #56925. It should be possible to trigger this bug in 5.0 and 5.1 as well, but not with that test case.
[27 Sep 2010 12:44] Marko Mäkelä
Here is a way to reproduce the bug on MySQL 5.1 (built-in InnoDB and InnoDB Plugin) and probably 5.0, compiled with UNIV_DEBUG.

CREATE TABLE bug56716 (a INT PRIMARY KEY,b INT,c INT,INDEX(b)) ENGINE=InnoDB;

SELECT * FROM bug56716 WHERE b<=42 ORDER BY b DESC FOR UPDATE;

This demonstrates that this InnoDB bug is independent of the MySQL 5.5 optimizer Bug #56925.
[4 Oct 2010 10:04] 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/119803
[4 Oct 2010 10:04] 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/119804
[4 Oct 2010 10:39] 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/119812
[4 Oct 2010 10:39] 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/119813
[4 Oct 2010 11:51] 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/119846
[6 Oct 2010 19:32] John Russell
Adding to change log:

A SELECT ... FOR UPDATE statement affecting a range of rows in an
InnoDB table could cause a crash.
[11 Oct 2010 8:31] James Day
The fix for this bug introduced bug #57345.
[11 Oct 2010 9:32] Marko Mäkelä
A correction to documentation: InnoDB would only crash when built with UNIV_DEBUG. Normally users run release binaries, because debug binaries are slow. Also note that this bug fix introduced Bug #57345.
[19 Oct 2010 16:43] Jon Stephens
Updated changelog entry to mention that this issue was limited to the debug server (per mail from Hery/;Marko).
[26 Oct 2010 0:42] John Russell
Confirmed that Jon's update is OK, closing the bug.
[1 Nov 2010 19:01] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
[9 Nov 2010 19:48] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:08] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)