[MySQL Bug] bug#61579碎碎念

转载请署名:印风

———————————————-

MySQL bug#61579

changelog:

InnoDB: For an InnoDB table with a trigger, under the setting innodb_autoinc_lock_mode=1, sometimes auto-increment values could be interleaved when inserting into the table from two sessions concurrently. The sequence of auto-increment values could vary depending on timing, leading to data inconsistency in systems using replication. (Bug #12752572, Bug #61579)

根据changelog的描述,当某个表上存在触发器时,并且innodb_autoinc_lock_mode=1,当两个session并发插入表时,自增列的值可能会产生交错。当使用statement记录时,会导致duplicate key错误,导致复制停止。

把触发器移除掉,则一切正常。

简单的gdb一把可以看出来,对于一条简单的插入语句,在函数handler::update_auto_increment中,当有trigger时,预估值 estimation_rows_to_insert值为0, 当没有trigger时,则为1.

那么在哪里设置 estimation_rows_to_insert的值呢?

在handler.h里定义了以下两个函数

  void ha_start_bulk_insert(ha_rows rows)

  {

    estimation_rows_to_insert= rows;

    start_bulk_insert(rows);

  }

  int ha_end_bulk_insert()

  {

    estimation_rows_to_insert= 0;

    return end_bulk_insert();

  }

从mysql_insert的函数逻辑来看

822 #ifndef EMBEDDED_LIBRARY

 823   if (lock_type != TL_WRITE_DELAYED)

 824 #endif /* EMBEDDED_LIBRARY */

 825   {

 826     if (duplic != DUP_ERROR || ignore)

 827       table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);

 828     /**

 829       This is a simple check for the case when the table has a trigger

 830       that reads from it, or when the statement invokes a stored function

 831       that reads from the table being inserted to.

 832       Engines can't handle a bulk insert in parallel with a read form the

 833       same table in the same connection.

 834     */

 835     if (thd->locked_tables_mode <= LTM_LOCK_TABLES)

 836       table->file->ha_start_bulk_insert(values_list.elements);

 837   }

 

对于没有触发器的表,

lock_type=TL_WRITE_CONCURRENT_INSERT

thd->locked_tables_mode=LTM_NONE

对于有触发器的表,

thd->locked_tables_mode=LTM_PRELOCKED  因此不会设置estimation_rows_to_insert

跑完test case后很快就可以观察到备库duplicate key错误(statement模式)

从Binlog来看

SET INSERT_ID=1/*!*/;

insert into t (a) values (1)                    —–sql1

SET INSERT_ID=65/*!*/;       

insert into t (a) values (2)                    —–sql2

SET INSERT_ID=1026

insert into t (a) values (3)                    —–sql3

SET INSERT_ID=2/*!*/;                         —–sql4

insert into t (a) values (10000),(10001),(10002),(10003),……(10938),(10939)

 

由于在存在trigger时,estimation_rows_to_insert值为0,因此在handler::update_auto_increment中走按批分配的逻辑,按照1,2,4,8,16……这样预留保留数

从binlog来看,执行了如下过程

sql1/sql2/sql3串行,并和sql4并行

sql1获得自增值 1

sql4获得自增值,2-64共63个值,也就是1+2+4+8+16+32=63

sql2获得自增值65

sql4继续获得自增值66-1025

sql3获得自增值1026-10004

另外这个bug,还会导致大量的自增列浪费

mysql> select max(id) from t;

+---------+

| max(id) |

+---------+

|   10004 |

+---------+

1 row in set (0.00 sec)

CREATE TABLE `t` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16387 DEFAULT CHARSET=gbk

官方补丁来看,在函数handler::update_auto_increment做了些修改,即对于这种特殊情况作了处理,对estimation_rows_to_insert赋予当前插入的记录数量

— sql/handler.cc  2012-04-12 11:04:12 +0000

+++ sql/handler.cc  2012-05-16 08:40:18 +0000

@@ -2528,6 +2528,17 @@

         reservation means potentially losing unused values).

         Note that in prelocked mode no estimation is given.

       */  

+

+      /*

+        For multi-row inserts, if the bulk inserts cannot be started, the

+        handler::estimation_rows_to_insert will not be set. Set it here.

+      */

+      if ((estimation_rows_to_insert == 0) &&

+          (thd->lex->many_values.elements > 0))

+      {

+        estimation_rows_to_insert= thd->lex->many_values.elements;

+      }

 

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL Bug] bug#61579碎碎念

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *