《MySQL技术内幕:InnoDB存储引擎》6. 锁

MySQL中的锁是为了解决共享资源存在的并发访问问题，不止是表数据，包括操作缓存池
中的LRU列表都会有锁的介入。

1. latch和lock

名称	区别
latch	轻量级锁，锁定的时间短，用来保证临界资源，但没有死锁的检测；分为mutex（互斥锁）和rwlock（读写锁）
lock	锁定数据库中的对象：表，页，行；lock对象仅在commit后rollback后释放；有死锁机制

mysql> show engine innodb mutex;
+--------+-----------------------------+-----------+
| Type   | Name                        | Status    |
+--------+-----------------------------+-----------+
| InnoDB | rwlock: dict0dict.cc:1183   | waits=24  |
| InnoDB | rwlock: log0log.cc:838      | waits=105 |
| InnoDB | sum rwlock: buf0buf.cc:1460 | waits=7   |
+--------+-----------------------------+-----------+
3 rows in set (0.03 sec)

2. 锁lock

2.1 锁的类型

名称	区别
共享锁（S Lock）	允许事务读取一行数据 ；S锁和S锁之间才兼容：兼容即不会阻塞等待
排他锁（X Lock）	允许事务删除或更新一行数据；X锁和其他都不兼容

若是有一个线程通过共享锁锁住了某一行数据，则要在这一行上加上S锁则能成功，而加上X锁则不行，需要
等待S锁释放，因为X锁和其他锁都不兼容

意向锁：想对/意向某个层次粒度的对象加锁；若是想对某页记录加上X锁，则需先对数据库、表、页
加上意向锁IX，最后才加上X锁

名称	区别
意向共享锁（IS Lock）	事务想要获得某几行的共享锁
意向排他锁（IX Lock）	事务想要获得某几行的排他锁

3. 一致性非锁定读：快照数据

概念：读取的行被其他session控制并在执行update或delete操作时，读取操作不会等待锁的释放，而是读取行的一个快照数据。
快照数据：该行之前的历史版本数据，读取快照数据不需要加锁，因为没有事务需要对历史数据进行修改操作
隔离级别不同，读取的快照数据也不同
	read committed：读取最新一份快照数据
	repeatable read(可重复读)：读取该事务开始时的行数据版本

session A	session B
begin
select * from table where id = 1
	begin
	update table set id = 2 where id = 1
select * from table where id = 1
	commit
select * from table where id = 1
commit

read committed:在session B commit后，查到的是最新数据，查询不到id = 1数据

repeatable read：读取该事务开始时的数据版本，则及时session B commit，还能查到id = 1的数据

需实验一下

3.1 通过如下三个表可观察锁的情况

mysql> select * from information_schema.innodb_locks
    -> ;
Empty set (0.00 sec)
mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 4370              | 4370:29:3:2       | 4368            | 4368:29:3:2      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_locks;
+-------------+-------------+-----------+-----------+--------------------+-----------------+------------+-----------+----------+----------------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table         | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+-------------+-------------+-----------+-----------+--------------------+-----------------+------------+-----------+----------+----------------+
| 4370:29:3:2 | 4370        | X         | RECORD    | `test`.`lock_test` | GEN_CLUST_INDEX |         29 |         3 |        2 | 0x000000000200 |
| 4368:29:3:2 | 4368        | X         | RECORD    | `test`.`lock_test` | GEN_CLUST_INDEX |         29 |         3 |        2 | 0x000000000200 |
+-------------+-------------+-----------+-----------+--------------------+-----------------+------------+-----------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

3.2 多版本并发：MVVC

InnoDB默认的读取是不会获得和等待锁，读取的是快照数据，即该行数据的历史版本
，通过undo实现，而一行数据的历史记录有多个版本，这种技术就是多版本技术，由此
带来的并发是多版本并发控制(Multi Version Concurrency Control,MVCC)

4. 一致性锁定读：支持如下两种

select ... from update：加X锁
select ... lock in share mode：加S锁

一致性非锁定读
​ 也是支持上诉两种，需要加上begin,start transaction或者set autocommit = 0;

5. 自增长和锁

• 自增长有一个计数器：select MAX(auto_inc_col) from t for update;
• auto-inc locking：
  • 优点：特殊的表锁设计，锁不是在一个事务完成后才释放，而是自增插入的sql完成后立即释放（提高插入性能）
  • 缺点：大数据插入性能低，每一个插入都得等待前一个完成
• innodb_autoinc_lock_mode：控制自增长模式，比auto-inc locking性能好
• InnoDB:自增长值的列必须的索引且是索引的第一个列

6. 外键和锁

• innobd会自动对外键加一个索引
• 外键值的插入或更新，需要先select父表，使用的是一致性锁定读，select．．．lock in share mode：性能低，父子表耦合会出现阻塞等

7. 修改事务隔离级别

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;	

set session transaction_isolation = 'read-committed';
set global transaction_isolation = 'read-committed';

8. 问题：for update后其他update语句会被阻塞延迟执行

8.1 事务1：加排他锁（悲观锁）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 1 for update;
+----+------+---------------------+---------------------+
| id | name | created_time        | updated_time        |
+----+------+---------------------+---------------------+
|  1 | A    | 2018-11-06 11:57:01 | 2018-11-06 11:57:01 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t set name = 'Q' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t; -- id为1的那么是P，被其他事务修改了
+----+------+---------------------+---------------------+
| id | name | created_time        | updated_time        |
+----+------+---------------------+---------------------+
|  1 | P    | 2018-11-06 11:57:01 | 2018-11-06 11:57:01 |
|  2 | N    | 2018-11-06 11:58:38 | 2018-11-06 11:58:38 |
+----+------+---------------------+---------------------+
2 rows in set (0.00 sec)

8.2 事务2：update已有锁的行，会被阻塞，等其他事务释放锁后才能执行

mysql> update t set name = 'P' where id = 1;
Query OK, 1 row affected (20.71 sec)
Rows matched: 1  Changed: 1  Warnings: 0
等了 20 秒才执行