工程项目中：replace的慎用

  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