MySQL InnoDB Locking and Transaction
事情起源于同事的一个问题，读表-修改-写表，在事务内完成，是否有并发问题？我想当然的以为没问题，不然要事务干什么？缺乏对事务的深入思考。事实是，这样有并发问题，如果不使用锁，是没法保证并发安全的。那么锁和事务是什么关系呢？
1 事务
一般而言，如果数据库支持ACID属性，那么它就支持事务。事务只是数据库的一个特性而已，不是说有了事务，一些问题被神奇的解决了。还需要思考问题的内在逻辑。
1.1 A（atomicity）
原子性，事务内的所有操作，要么都做（ COMMIT ），要么都不做（ ROLLBACK ）。但做的对不对，不管。
例如订单系统，减库存和生成订单，要么库存减少，订单生成。要么库存没变化，订单没生成。但是，两个人同时买库存为一的商品，可能把库存减成负的，这是逻辑的问题，不是事务的问题。
1.2 C（consistency）
一致性，事务结束时，数据库的主键约束，外键约束等其它特性，必须满足。（理解不深，觉得理所当然就该这样）
1.3 I（isolation）
隔离性，同时运行的事务，应该相互隔离。并发控制和隔离性有关。
1.4 D（durability）
持久性，完成的事务数据必须持久存储。
就ACID四个属性而言，AC的可选择性较小，ID的自由度比较大。例如D，数据落盘是个很复杂的过程，真要每次事务提交都实打实的写硬盘，性能是无法接受的。通常借助日志，RAID卡缓存提高性能。并且有时候降低D来提升性能。I有4个级别，不同级别对性能和业务的影响是不一样的。
2 锁
MySQL的读分 锁读 和 一般读 ，不特殊强调的话，都是指一般读，一般读不加锁。锁分读锁 S 和写锁 X 。另外，同样的SQL，在不同的隔离级别下，加的锁也不同。下面的论述相当不专业，只是为了辅助理解事务隔离级别。可以通过 SHOW ENGINE INNODB STATUS 看到锁的详细信息，但也依赖mysql的版本，我在percona 5.6.22-72.0中能看到每个锁的信息，但是percona 5.6.24-72.2中却看不到。
2.1 Intention Locks (意向锁)
Intention Locks，仅仅表明了加锁的意向，它是表级锁，并且无法单独存在。 SELECT ... LOCK IN SHARE MODE 加 IS 锁， SELECT ... FOR UPDATE 和写操作加 IX 锁。 IS IX S 和 X 的兼容情况是：
| X | IX | S | IS | |
| X | Conflict | Conflict | Conflict | Conflict |
| IX | Conflict | Compatible | Conflict | Compatible |
| S | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
例1：
SELECT * FROM students WHERE id = 75 LOCK IN SHARE MODE; 加了IS锁和S锁，S锁是行锁
TABLE LOCK table `test`.`students` trx id 1886524 lock mode IS RECORD LOCKS space id 842 page no 3 n bits 72 index `PRIMARY` of table `test`.`students` trx id 1886524 lock mode S locks rec but not gap
更新操作后升级为写锁 UPDATE students SET age = 15 WHERE id = 75
TABLE LOCK table `test`.`students` trx id 1886532 lock mode IS RECORD LOCKS space id 842 page no 3 n bits 72 index `PRIMARY` of table `test`.`students` trx id 1886532 lock mode S locks rec but not gap TABLE LOCK table `test`.`students` trx id 1886532 lock mode IX RECORD LOCKS space id 842 page no 3 n bits 72 index `PRIMARY` of table `test`.`students` trx id 1886532 lock_mode X locks rec but not gap
例2：
SELECT * FROM students WHERE id = 75 FOR UPDATE; 加了IX锁和X锁，X 是行锁
TABLE LOCK table `test`.`students` trx id 1886526 lock mode IX RECORD LOCKS space id 842 page no 3 n bits 72 index `PRIMARY` of table `test`.`students` trx id 1886526 lock_mode X locks rec but not gap
例3：
SELECT * FROM students WHERE age > 15 LOCK IN SHARE MODE; 虽然结果集为空，但还是锁住了整张表，因为where子句没有索引
TABLE LOCK table `test`.`students` trx id 1886537 lock mode IS RECORD LOCKS space id 842 page no 3 n bits 72 index `PRIMARY` of table `test`.`students` trx id 1886537 lock mode S
2.2 Record Locks
记录锁，可以理解为每个锁对应一个记录。例如 UPDATE students SET age = 15 WHERE id = 75; 这里id是primary key，如果id是unique，也是记录锁。
2.3 Gap Locks
应该是锁住一个范围的意思。不是特别理解。
2.4 Next-Key Locks
用于防止幻读的，等于 record lock + gap lock，下界开区间，上界闭区间。但是我没想明白这个是如何解决幻读的。例如事务A： SELECT * FROM students WHERE id > 75 ，然后事务B：新增了id=79并提交，此时事务A再次执行刚才的查询，结果是一样的，而事务A执行期间没加任何锁。
3 事务的隔离级别
下面的陈述按照常用级别排序，从实例中可以看出，各个隔离级别锁数据的量是不一样的，这导致了性能的差异。同时，在并发时，理解SQL背后的锁，有助于理解业务逻辑是不是并发安全的。
3.1 可重复读（REPEATABLE READ）
所谓可重复读，就是在事务内部，所有查询都和第一次查询的结果相同（能看到首次查询前其它事务的提交，看不到之后其它事务的提交），但是可以看到本事务内的修改（虽然看不到其它事务的提交，但是如果修改，则可以看到）。写操作加写锁， 锁读 加锁。这是MySQL的默认级别。
例如：
Session A | Session B
SELECT * FROM testt; |
+----+------+ |
| id | n | |
+----+------+ |
| 7 | 777 | |
| 9 | 900 | |
+----+------+ |
| START TRANSACTION;
START TRANSACTION; |
DELETE FROM testt WHERE id = 9; |
COMMIT; | -- 看到之前的事务
| SELECT * FROM testt;
| +----+------+
| | id | n |
| +----+------+
| | 7 | 777 |
| +----+------+
START TRANSACTION; |
INSERT INTO testt(id, n) VALUES(8, 800); |
COMMIT; | -- 查询结果和首次查询一样
| SELECT * FROM testt;
| +----+------+
| | id | n |
| +----+------+
| | 7 | 777 |
| +----+------+
|
| -- 虽然看不到id=8的记录，但是可以更新
| UPDATE testt SET n = 888 WHERE id = 8;
|
| -- 更新之后可以看到id=8的记录
| SELECT * FROM testt;
| +----+------+
| | id | n |
| +----+------+
| | 7 | 777 |
| | 8 | 888 |
-- 看不到未提交的修改 | +----+------+
SELECT * FROM testt; |
+----+------+ |
| id | n | |
+----+------+ |
| 7 | 777 | |
| 8 | 800 | |
+----+------+ | -- 更新加写锁 Record Locks
| UPDATE testt SET n = 7 WHERE id = 7;
START TRANSACTION; |
-- 无法加写锁，超时 |
UPDATE testt SET n = 77 WHERE id = 7; |
ERROR 1205 (HY000): Lock wait timeout... | -- 加 Next Key Lock
| SELECT * FROM testt SET n >= 8 FOR UPDATE;
-- 因为Next Key Lock，超时 |
INSERT INTO testt(id, n) VALUES(9, 999); |
ERROR 1205 (HY000): Lock wait timeout... |
| ROLLBACK; START TRANSACTION;
| -- 所有的行都加上了锁，虽然只有id = 8符合条件
| UPDATE testt SET n = 0 WHERE n >= 800;
UPDATE testt SET n = 77 WHERE id = 7; |
ERROR 1205 (HY000): Lock wait timeout... |
3.2 读已提交（READ COMMITTED）
事务内部可以读到其它事务提交的结果。这个导致 幻读 （我理解幻读就是读到的数据不确定的意思），取决于业务逻辑，幻读不一定是个问题。它的另一个问题是 锁读 没有 Next-Key Locks。测试前需要设置隔离级别， SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 。
例如：
Session A | Session B
| START TRANSACTION;
| SELECT * FROM testt;
| +----+------+
| | id | n |
| +----+------+
| | 7 | 777 |
| | 8 | 888 |
| +----+------+
START TRANSACTION; |
UPDATE testt SET n = 700 WHERE id = 7; |
COMMIT; | -- 看到其它事务的提交，不同于首次查询
| SELECT * FROM testt;
| +----+------+
| | id | n |
| +----+------+
| | 7 | 700 |
| | 8 | 888 |
| +----+------+
|
| -- 加Gap Locks
| SELECT * FROM testt WHERE id >= 7 LOCK IN SHARE MODE;
START TRANSACTION; |
-- 有IS锁，更新失败 |
UPDATE testt SET n = 800 WHERE id = 8; |
ERROR 1205 (HY000): Lock wait timeout... |
|
-- 没有Next-Key Locks，插入成功 |
INSERT INTO testt(id, n) VALUES(9, 900); |
| ROLLBACK; START TRANSACTION;
| -- 只有符合条件的id = 8才加锁
| UPDATE testt SET n = 0 WHERE n >= 800;
UPDATE testt SET n = 77 WHERE id = 7; |
3.3 读未提交（READ UNCOMMITTED）
可以读到未提交的数据。导致 脏读 ，因为数据最终可能未提交。
3.4 串行化（SERIALIZABLE）
和重复读类似，除了所有的读都是 LOCK IN SHARE MODE ，写性能大大降低。
4 读-修改-写
回到最初的问题，默认隔离级别。读-修改-写，不是并发安全的，多个线程可以并发的读到相同的数据，然后各自修改，这不是业务需要的。可以使用 SELECT ... FOR UPDATE 防止并发读写。虽然可以使用 锁读 来控制锁，但是死锁的概率也大大提升。注意： 通过FOR UPDATE加锁不能脱离事务独立存在
5 锁冲突
锁冲突会降低并发，例如使用mysql做计数器。
6 死锁
偶尔死锁不是大问题，只要重试即可。减低死锁的方法：
- 不同事务，操作表的顺序尽量相同
- 事务尽可能小（把大事务分拆成小事务），执行时间尽可能短，事务中不要有耗时操作，例如http请求，读写文件等。
- 如果确信不影响业务，可以降低事务的隔离级别。例如 spring 中
@Transactional(isolation = Isolation.READ_COMMITTED)
6.1 单纯INSERT引起的死锁
这个死锁是工作中碰到的，参考：Locks Set by Different SQL Statements in InnoDB
CREATE TABLE students (id int(11) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32), phone VARCHAR(15), UNIQUE KEY(phone));
session A | session B | session C
START TRANSACTION; | |
-- INSERT 加了 X 锁 | START TRANSACTION; |
INSERT INTO students VALUES(null, 'xw', '15810183239'); | | START TRANSACTION;
| -- phone重复，等待 S 锁 | -- phone重复，等待S锁
| INSERT INTO students VALUES(null, 'xw', '15810183239'); | INSERT INTO students VALUES(null, 'xw', '15810183239');
ROLLBACK; | |
| -- A回滚，获取S锁，插入需要S锁升级为X锁，引发死锁 | -- A回滚，获取S锁，插入需要S锁升级为X锁，引发死锁
这里主要是事务A回滚，B和C同时获取了S锁，因此它们无法将S锁升级为X锁，于是死锁。在编程实践中，回滚可能是框架做的，例如：INSERT后的某个操作抛出异常，框架回滚了事务。
死锁信息
(1) TRANSACTION: TRANSACTION 1886591, ACTIVE 7 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 5028, OS thread handle 0x7f1468c6f700, query id 1917874 localhost root update insert into students values(null, 'xw', '15810183239') (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 842 page no 4 n bits 72 index `phone` of table `test`.`students` trx id 1886591 lock_mode X insert intention waiting (2) TRANSACTION: TRANSACTION 1886590, ACTIVE 20 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 5013, OS thread handle 0x7f145f53b700, query id 1917863 localhost root update insert into students values(null, 'xw', '15810183239') (2) HOLDS THE LOCK(S): RECORD LOCKS space id 842 page no 4 n bits 72 index `phone` of table `test`.`students` trx id 1886590 lock mode S (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 842 page no 4 n bits 72 index `phone` of table `test`.`students` trx id 1886590 lock_mode X insert intention waiting WE ROLL BACK TRANSACTION (2)