< 返回技术文档列表

replace死锁分析

发布时间:2017-06-27 发布人:hongbin

问题背景

原问题:使用下面的表结构和SQL,为何在多并发场景下,会出现死锁。

表结构

CREATE TABLE `c` (

  `a` int(11) NOT NULL AUTO_INCREMENT,

  `b` int(11) DEFAULT NULL,

  PRIMARY KEY (`a`),

  UNIQUE KEY `b` (`b`)

) ENGINE=InnoDB

测试SQL

replace into c value(NULL, 1)


基本概念

关于replace

1.replace类似insert,区别新记录插入前存在与之相同的旧记录,会先删除此记录再插入。

2.replace返回的rows affected值,包括了deleted和inserted操作之和。如果是1表示只有insert没有deleted,如果大于1表示新记录插入前执行了delete操作。

3.replace只在具有主键或唯一索引的表中有效。


关于InnoDB Lock

InnoDB有Record Locks,Gap Locks,Next-Key Locks, Insert Intention Locks, AUTO-INC Locks。


Record Locks

    行锁只是锁住索引记录本身,即使没有索引的表,也会使用Innodb创建的隐藏聚簇索引操作记录锁。


Gap Locks

    gap lock 锁定一个区间防止其他事务对此区间写入,区间可能是单个或多个索引值、甚至空值。使用唯一索引搜索记录不会上gap lock,但不包括组合列的唯一索引。


Next-Key Locks

    Next-key组合了索引记录的record lock和索引记录前区间的gap lock。  

    

    InnoDB在搜索或扫描表索引时使用row-level lock,在匹配的索引记录上设置共享或者互斥的index-record lock。

   

 如果某会话在索引中的R记录加了一个共享或者互斥锁,其他会话不能立即在R记录之前(按照索引中排序的顺序)的区间中插入新值。


Insert Intention Locks

    是在insert某记录前设置的一种Gap Locks,多个不同事务向同一个区间上互斥锁,如果它们插入的是不同记录,事务间不会相互阻塞。


AUTO-INC Locks

    是一种特殊的表级锁,当事务insert时包含自增字段,其他事务必须等待保持插入记录是单调递增的。


死锁分析

表结构创建后,执行mysqlslap很容易重现

hongbin@MBP ~/w/s/msb_5_7_17> mysqlslap -c 100 -q replace.sql --number-of-queries=1000000000000 -h127.0.0.1 -P5717 -pmsandbox test

mysqlslap: [Warning] Using a password on the command line interface can be insecure.

mysqlslap: Cannot run query replace into c values(NULL,1); ERROR : Deadlock found when trying to get lock; try restarting transaction

mysqlslap: Cannot run query replace into c values(NULL,1); ERROR : Deadlock found when trying to get lock; try restarting transaction


SHOW ENGINE INNODB STATUS 死锁检测的输出

------------------------

LATEST DETECTED DEADLOCK

------------------------

2017-06-23 18:39:48 0x7000049ff000

*** (1) TRANSACTION:

TRANSACTION 1240452, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 

MySQL thread id 113, OS thread handle 123145392713728, query id 30697 localhost 127.0.0.1 root update

replace into c values(NULL,1)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240452 lock_mode X waiting 

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;

*** (2) TRANSACTION:

TRANSACTION 1240409, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2

MySQL thread id 68, OS thread handle 123145379901440, query id 30654 localhost 127.0.0.1 root update

replace into c values(NULL,1)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240409 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;; 

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240409 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32             

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;

*** WE ROLL BACK TRANSACTION (1)


1.replace语句并非原子操作,包含了delete和insert两个操作。

2.表结构有一个聚集索引和二级索引,lock行为会对两个索引加锁。


事务1在执行insert操作,MySQL的insert是对插入记录上exclusive index-record lock,不会上gap lock,状态显示lock_mode X waiting,说明它在等待这把互斥锁,对象是4号页面index b。

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240452 lock_mode X waiting 

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;


事务2在执行delete操作,如果replace的值与唯一索引没有冲突,上锁行为是与insert一致的上exclusive index-record lock,但这里新记录与旧记录存在冲突,就要上exclusive next-key lock。状态显示它持有了该锁lock_mode X, 73757072656d756d; asc supremum表示当前值到无穷大的区间都被锁定,同时还有记录本身和之前的区间,对象也是4号页面index b。

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240409 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;


事务1的exclusive index-record lock在等待事务2的exclusive next-key lock。


状态显示事务2第二个锁,这是个insert intention lock(insert intention lock是一种Gap Lock)。当不同事务在向相同索引区间插入值时,就会使用insert intention lock,如果它们插入的位置不同,就不需要相互等待,但此场景插入的是相同位置,所以事务2的insert intention lock,需要等待事务1的锁。

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 42 page no 4 n bits 72 index b of table `mysqlslap`.`c` trx id 1240409 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32             

0: len 4; hex 80000001; asc     ;;

1: len 4; hex 800077a6; asc   w ;;


事务1的exclusive index-record lock在等待事务2的exclusive next-key lock,事务2的insert intention lock又在等待事务1的exclusive index-record lock。所以产生了死锁。


事务1的undo log entries 1比事务2undo log entries 2小,事务1被回滚。


如果没有自增主键会怎样?

没有自增主键,唯一索引将作为聚集索引,没有二级索引。

CREATE TABLE `c1` (

  `b` int(11) DEFAULT NULL,

  UNIQUE KEY `b` (`b`)

) ENGINE=InnoDB


并发操作时相当于对一条记录频繁做delete和insert操作,只会有exclusive index-record lock的等待,不会出现死锁。

---TRANSACTION 1331355, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 375, OS thread handle 123145382686720, query id 76469 localhost 127.0.0.1 root update

replace into c1 values(1)

------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 43 page no 4 n bits 72 index b of table `mysqlslap`.`c1` trx id 1331355 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000001; asc     ;;

1: len 6; hex 000000000300; asc       ;;

------------------

---TRANSACTION 1331354, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 437, OS thread handle 123145402183680, query id 76468 localhost 127.0.0.1 root update

replace into c1 values(1)

------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 43 page no 4 n bits 72 index b of table `mysqlslap`.`c1` trx id 1331354 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000001; asc     ;;

1: len 6; hex 000000000300; asc       ;;

------------------

---TRANSACTION 1331353, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 383, OS thread handle 123145385472000, query id 76466 localhost 127.0.0.1 root update

replace into c1 values(1)

------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 43 page no 4 n bits 72 index b of table `mysqlslap`.`c1` trx id 1331353 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000001; asc     ;;

1: len 6; hex 000000000300; asc       ;;


死锁和自增锁有无关系?

lock_rec_print和lock_table_print是打印事务锁状态的函数,状态没有显示AUTO-INC Locks,推测和AUTO-INC lock无关。


/*********************************************************************//**

Prints info of a record lock. */

void

lock_rec_print(

/*===========*/

    FILE*       file,   /*!< in: file where to print */

    const lock_t*   lock)   /*!< in: record type lock */

{

    ulint           space;

    ulint           page_no;

    mtr_t           mtr;

    mem_heap_t*     heap        = NULL;

    ulint           offsets_[REC_OFFS_NORMAL_SIZE];

    ulint*          offsets     = offsets_;

    rec_offs_init(offsets_);

    ut_ad(lock_mutex_own());

    ut_a(lock_get_type_low(lock) == LOCK_REC);

    space = lock->un_member.rec_lock.space;

    page_no = lock->un_member.rec_lock.page_no;

    fprintf(file, "RECORD LOCKS space id %lu page no %lu n bits %lu "

        "index %s of table ",

        (ulong) space, (ulong) page_no,

        (ulong) lock_rec_get_n_bits(lock),

        lock->index->name());

    ut_print_name(file, lock->trx, lock->index->table_name);

    fprintf(file, " trx id " TRX_ID_FMT, trx_get_id_for_print(lock->trx));

    if (lock_get_mode(lock) == LOCK_S) {

        fputs(" lock mode S", file);

    } else if (lock_get_mode(lock) == LOCK_X) {

        fputs(" lock_mode X", file);

    } else {

        ut_error;

    }

    if (lock_rec_get_gap(lock)) {

        fputs(" locks gap before rec", file);

    }

    if (lock_rec_get_rec_not_gap(lock)) {

        fputs(" locks rec but not gap", file);

    }

    if (lock_rec_get_insert_intention(lock)) {

        fputs(" insert intention", file);

    }

    if (lock_get_wait(lock)) {

        fputs(" waiting", file);

    }

    mtr_start(&mtr);

    putc('\n', file);

    const buf_block_t*  block;

    block = buf_page_try_get(page_id_t(space, page_no), &mtr);

    for (ulint i = 0; i < lock_rec_get_n_bits(lock); ++i) {

        if (!lock_rec_get_nth_bit(lock, i)) {

            continue;

        }

        fprintf(file, "Record lock, heap no %lu", (ulong) i);

        if (block) {

            const rec_t*    rec;

            rec = page_find_rec_with_heap_no(

                buf_block_get_frame(block), i);

            offsets = rec_get_offsets(

                rec, lock->index, offsets,

                ULINT_UNDEFINED, &heap);

            putc(' ', file);

            rec_print_new(file, rec, offsets);

        }

        putc('\n', file);

    }

    mtr_commit(&mtr);

    if (heap) {

        mem_heap_free(heap);

    }

}


Prints info of a table lock. */

void

lock_table_print(

/*=============*/

    FILE*       file,   /*!< in: file where to print */

    const lock_t*   lock)   /*!< in: table type lock */

{

    ut_ad(lock_mutex_own());

    ut_a(lock_get_type_low(lock) == LOCK_TABLE);

    fputs("TABLE LOCK table ", file);

    ut_print_name(file, lock->trx,

         lock->un_member.tab_lock.table->name.m_name);

    fprintf(file, " trx id " TRX_ID_FMT, trx_get_id_for_print(lock->trx));

    if (lock_get_mode(lock) == LOCK_S) {

        fputs(" lock mode S", file);

    } else if (lock_get_mode(lock) == LOCK_X) {

        ut_ad(lock->trx->id != 0);

        fputs(" lock mode X", file);

    } else if (lock_get_mode(lock) == LOCK_IS) {

        fputs(" lock mode IS", file);

    } else if (lock_get_mode(lock) == LOCK_IX) {

        ut_ad(lock->trx->id != 0);

        fputs(" lock mode IX", file);

    } else if (lock_get_mode(lock) == LOCK_AUTO_INC) {

        fputs(" lock mode AUTO-INC", file);

    } else {

        fprintf(file, " unknown lock mode %lu",

            (ulong) lock_get_mode(lock));

    }

    if (lock_get_wait(lock)) {

        fputs(" waiting", file);

    }

    putc('\n', file);

}



在线咨询
免费电话
返回顶部