淘先锋技术网

首页 1 2 3 4 5 6 7

  replace本身就是一个事务,他是通过唯一索引(如果没有指定则为主键)判断当前repalce的数据是否在表中存在,如果存在,则先delete删除,然后再inserte插入新数据覆盖旧数据。如果不存在,则直接Inserte。
  注意:在工程项目中,要尽量少用replace,因为这是一个规范的问题。理由如下:
  ① replace不能记录日志,因为在工程中replace记录会删除原始的再插入,会出现这种情况一般是回滚的时候出现,这样就没有了回滚之前的记录,只有回滚之后的记录。所以一般而言,我们手动执行delete和Inserte操作,在delete之前判断该条记录是否在表中存在,如果存在,则把该条记录插入到另一张日志表中,然后再delete,再inserte,这样日志就有记录了。

  ② replace会导致死锁。其根本原因是replace的插入和删除操作中,如果插入位置的下一条记录上存在记录锁,那么在插入时,当前session需要对其加插入意向锁。

  例如测试表:
  create table t1 (a int auto_increment primary key, b int, c int, unique key (b))   //并发执行SQL:
  replace into t1(b,c) values (2,3)   //使用脚本,超过3个会话
  这里创建的主键是a,二级索引为b,则会创建两棵树,一颗聚集索引b+树根据a来,另一个非聚集索引b+树根据b来。这里的唯一索引uk为(b)。
  聚集索引指的是默认的以你的主键为索引构建的b+,非聚集索引就是指以你指定的字段来作为索引构建b+。每次给字段建一个新索引,字段中的数据就会被复制一份出来,用于生成索引。因此,给表添加索引,会增加表的体积,占用磁盘存储空间。
  非聚集索引和聚集索引的区别在于,通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值,再使用主键的值通过聚集索引查找到需要的数据。
  索引能让数据库查询数据的速度上升,而使写入数据的速度下降,原因很简单的,因为b+这个结构必须一直维持在一个正确的状态,增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构,因此,在每次数据改变时,DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

Replace的逻辑分为四步

  第一步:正常的插入逻辑
  首先在聚集索引b+树上,因为a自增,由于未显示指定自增值,每次Insert前都会生成一个不冲突的新值。然后再在非聚集索引b+树上,由于b是唯一索引,即开始检查duplicate key(是否插入了重复的键)。
  如果此时UK(唯一索引)已经存在,则返回错误。
  如果不存在,则执行正常的insert插入操作。

  第二步:处理错误
  当收到唯一索引存在的错误信息后,把第一步在聚集索引中的插入记录回滚掉。

  第三步:转换操作
  收到duplicate key错误后,检查唯一键冲突的索引,并对冲突的索引记录加排他锁(对于普通的INSERT操作,当需要检查duplicate key时,加LOCK_S锁),随后确认冲突转换方案:
  • 如果发生uk冲突的索引是最后一个唯一索引((a,b)(a,c)(b)则最后一个唯一索引为(b))、没有外键引用、且不存在delete trigger(删除触发器)时,使用UPDATE ROW的方式来解决冲突;
  • 否则,使用DELETE ROW + INSERT ROW的方式解决冲突。

  第四步:更新记录
  对于聚集索引,由于PK(primary key)列发生变化,采用delete + insert 聚集索引记录的方式更新。对于二级uk索引,同样采用标记删除 + 插入的方式。
  我们知道,在尝试插入一条记录时,如果插入位置的下一条记录上存在记录锁,那么在插入时,当前session需要对下一条记录加插入意向锁,具体类型为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION。这也是导致死锁的关键点之一。

  Record Lock(行锁):单个行记录上的锁,也就是LOCK_X和LOCK_S
  LOCK_GAP为间隙锁(行锁,不包括记录本身,也就是左开右开):指定记录之间间隙所加的锁,这个间隙包括:记录与记录之间的间隙,第一条记录之前的间隙以及最后一条记录后面的间隙。举个例子:select c1 from t where c1 between 10 and 20 for update 将防止其他事务插入15这个数字到t.c1字段中,无论是否已经有一个这样的值在这个字段中了,因为值在这个范围内的所有记录都被锁住了。即我们select后,session2就不能在这个范围里面进行插入了,所以防止了幻读。间隙锁针对的是索引,如果没有索引就会全表扫描,就会锁定整张表所有的记录让其他事务不能增删改。
  间隙不是指的到无穷哈,是到前面或者后面的那一条索引之间的范围。例如:下图中为了防止在number=4的幻读,我们就需要使用间隙锁,锁定number=4的间隙。则:
  select * from news where number=4 for update执行成功后:
在这里插入图片描述
  因为4之前和之后的间隙全被锁住了,即2到4和4到5.

  Next-Key Lock锁(NK锁,也是行锁,innodb对于行的查询,默认都是采用该方法,主要目的是解决幻读的问题):NK锁即为间隙锁+记录锁,例如上面的sql=select * from news where number=4 for update,则next-key锁锁定的范围为间隙锁+记录锁,即区间(2,4),(4,5)加间隙锁,同时number=4和number=5的记录加记录锁。(其实就是(2,4],(4,5])。一句话总结就是:Next-key锁是在下一个索引记录本身和索引之前的gap加上S锁或是X锁(如果是读就加上S锁,如果是写就加X锁)
例如:Next-Key的范围如果是(-∞,1] (1,5] (5,10] (10,+∞),即索引code为1,5,10,如果我们在第一个事务中,执行了code>8 for update,在扫描过程中,找到了code=10,此时就会锁住10之前的间隙(5到10之间的gap),10本身(record),和10之后的间隙(next-key)。此时另一个事务插入(6,6),(9,9)和(11,11)都是不被允许的,只有在前一个索引5及5之前的索引和间隙才能执行插入(更新和删除也会被阻塞)。大于就是后面的全部间隙哈。
  LOCK_INSERT_INTENTION:为插入意向锁,主要是用来堵塞,专门针对Insert操作的,也是一种特殊的间隙锁。但是插入意向锁和lock_x锁和lock_s锁不兼容,所以会阻塞等待。
普通的间隙锁不允许在上一条记录到本记录范围内插入数据,而插入意向锁就允许,是为了提高并发插入的性能。

死锁分析

在这里插入图片描述
  唯一索引还是b列。session 1获得自增列值为2100619, session 2 获得的自增列值为2100614, session 3获得的自增列值为2100616。
  Session1:replace into t1 values (2100619, 2, 3); // uk索引上记录(2, 2100612)被标记删除,同时插入新记录(2, 2100619)
  Session2:replace into t1 values (2100614, 2, 3);
  Session 3: replace into t1 values (2100616, 2, 3); // 检测到uk有冲突键,需要获取记录(2, 2100619) 的X锁,该锁被session2获取,则被阻塞等待。此时给(2, 2100619)加上Next-Key Lock锁(NK锁)。

  此时在session2中,在session3的基础上步骤如下:
  a)标记删除记录(2, 2100619),同时插入新记录(2, 2100614);(只是标记删除,还没commit真正的删除)
  b) (2, 2100614) 比(2, 2100619) 要小,因此定位到该记录之前;
  c) (2, 2100614)记录的下一条记录(2, 2100619)上有锁等待,则下一条记录(2, 2100619)需要升级成插入意向X锁(此时为等待状态),等待session3释放(2, 2100619)记录的NK锁后session2才可以插入(2, 2100614),而session3又等待session2释放(2, 2100619)记录的X锁,从而导致死锁发生。
  插入意向锁和S/X Lock锁不兼容,因此需要等待插入意向锁释放
  一句话:死锁锁住的都是同一条记录,并且已经持有锁的线程在等待同一条记录的锁。

  对于NK锁和插入意向锁的例子:
  假设现在有记录 10, 30, 50, 70 ;且为主键 ,需要插入记录 25 。

  找到 小于等于25的记录 ,这里是 10
  找到 记录10的下一条记录 ,这里是 30
  判断 下一条记录30 上是否有锁
  ① 判断 30 上面如果 没有锁 ,则可以插入
  ② 判断 30 上面如果有Record Lock,则可以插入
  ③ 判断 30 上面如果有Gap Lock/Next-Key Lock,则无法插入,因为锁的范围是 (10, 30) /(10, 30] ;在30上增加insert intention lock( 此时处于waiting状态),当 Gap Lock / Next-Key Lock 释放时,等待的事物( transaction)将被 唤醒 ,此时 记录30 上才能获得 insert intention lock ,然后再插入 记录25。
  注意:一个事物 insert 25 且没有提交,另一个事物 delete 25 时,记录25上会有 Record Lock