一. 锁（二）
1.1. 意向锁介绍
1. 揭示下一层级请求的锁的类型 2. IS：事物想要获得一张表中某几行的共享锁 3. IX：事物想要获得一张表中某几行的排他锁 4. InnoDB存储引擎中意向锁都是 表锁
假如此时有 事物tx1 需要在 记录A 上进行加 X锁 ： 1. 在该记录所在的 数据库 上加一把 意向锁IX 2. 在该记录所在的 表 上加一把 意向锁IX 3. 在该记录所在的 页 上加一把 意向锁IX 4. 最后在该 记录A 上加上一把 X锁锁兼容 X IX S IS
X 冲突 冲突 冲突 冲突 IX 冲突 兼容 冲突 兼容 S 冲突 冲突 兼容 兼容 IS 冲突 兼容 兼容 兼容 意向锁都是 相互兼容 的，因为意向锁表示的是 下一层 在请求什么类型的锁 假如此时有 事物tx3 需要在 记录A 上进行加 S锁 ： 1. 在该记录所在的 数据库 上加一把 意向锁IS 2. 在该记录所在的 表 上加一把 意向锁IS 3. 在该记录所在的 页 上加一把 意向锁IS 4. 发现该记录被锁定（ tx1的X锁 ），那么 tx3需要等待 ，直到 tx1 进行commit 1.2. 意向锁的作用 • 意向锁 是为了实现 多粒度的锁 ，表示在数据库中不但能实现 行级别的锁 ，还可以实现 页级别的锁 ， 表级别的锁 以及 数据库级别的锁 • 如果没有意向锁，当你去锁一张表的时候，你就需要对表下的所有记录都进行加锁操作，且对其他事物刚刚插入的记录（游标已经扫过的范围）就没法在上面加锁了，此时就没有实现锁表的功能。 上述锁的操作都是在 内存 中，不会放在数据库中。且大部分加的都是意向锁，都是兼容的。 释放操作则是从记录锁开始 从下往上 进行释放 InnoDB 没有 数据库级别的锁，也 没有 页级别的锁（InnoDB只能在 表 和 记录 上加锁），所以InnoDB的 意向锁 只能加在 表 上，即InnoDB存储引擎中意向锁都是 表锁
1.3. 加锁以及查看 -- 终端1 mysql> desc t5; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | b | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
mysql> select * from t5; +------+------+ | a | b | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec)
mysql> begin; -- 开始一个事物 Query OK, 0 rows affected (0.01 sec)
mysql> select * from t5 where a=2 for update; -- 加上一个排他锁 +------+------+ | a | b | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec)
-- 终端2 mysql> show engine innodb statusG -- ----------省略其他输出------- ---TRANSACTION 23076, ACTIVE 96 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 3, OS thread handle 139787704108800, query id 44 localhost root cleaning up TABLE LOCK table `burn_test`.`t5` trx id 23076 lock mode IX -- 在表上加上了意向锁IX（ TABLE LOCK） RECORD LOCKS space id 62 page no 3 n bits 72 index GEN_CLUST_INDEX of table `burn_test`.`t5` trx id 23076 lock_mode X locks rec but not gap -- X locks rec but not gap 就是记录锁 （ RECORD LOCK） Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000625; asc %;; 1: len 6; hex 000000001390; asc ;; 2: len 7; hex 610000002c0d5f; asc a , _;; 3: len 4; hex 80000002; asc ;; 4: len 4; hex 80000002; asc ;; -- Record lock : 表示是锁住的记录 -- heap no 2 PHYSICAL RECORD: n_fields 5 : 表示锁住记录的heap no 为2的物理记录，由5个列组成 -- compact format : 表示这条记录存储的格式（ Dynamic其实也是compact的格式） -- info bits : 0 -- 表示这条记录没有被删除; 非0 -- 表示被修改或者被删除（ 32） -- 输出上述信息的前提是 innodb_status_output_locks = 1 -- 可在配置文件中设置打开，不会影响运行时的性能 -- 只有在show engine innodb status时才会使用
-- 终端1 mysql> commit; -- 提交事物 Query OK, 0 rows affected (0.00 sec) -- 提交后就释放了锁，同时在终端2上就看不到锁的信息了 -- 注意如果直接select * from t5 where a=2 for update;终端2是看不到的，因为mysql默认会自动提交事物
• INNODB_TRX
-- 终端1
mysql> begin; -- 开启一个事物 Query OK, 0 rows affected (0.00 sec) mysql> select * from t5 where a=2 for update; -- 继续锁住该记录 +------+------+ | a | b | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec)
-- 终端2 mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
mysql> select * from INNODB_TRXG *************************** 1. row *************************** trx_id: 23077 trx_state: RUNNING trx_started: 2016-01-24 12:42:44 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 13 trx_query: NULL -- 事物运行的SQL语句，这里是NULL -- 因为他是指当前运行的SQL语句 -- 如果运行完了，就看不到了 trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 -- 表锁IX trx_lock_structs: 2 -- 总共有2把锁 trx_lock_memory_bytes: 1136 trx_rows_locked: 1 -- 记录锁 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED -- 当前数据库隔离级别是RC trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0
• INNODB_LOCKS和INNODB_LOCK_WAITS -- 终端2 -- 下面两个表是要一起对比查看才有意义的，当前没有两个事物（仅终端1中一个事物）进行相互阻塞和等待，所以目前两个表是空的 mysql> select * from INNODB_LOCKS; Empty set (0.00 sec) mysql> select * from INNODB_LOCK_WAITS; Empty set (0.00 sec) -- 所以为了演示，需要再开一个会话 终端3
-- 终端3 mysql> set innodb_lock_wait_timeout=60; -- 设置锁等待时间为60秒，方便看到锁的信息，线上根据实际情况自行修改 Query OK, 0 rows affected (0.00 sec) mysql> select * from t5 where a=2 lock in share mode; -- 这里如果超时时间设置短，可能来不及看 -- 可通过innodb_lock_wait_timeout进行设置 -- 此时hang住在这里，进行等待，因为终端1中 for update 还没有commit
-- 终端2 -- 回到终端2中，查看之前两个表的信息 mysql> select * from INNODB_LOCKSG *************************** 1. row *************************** lock_id: 23078:62:3:2 lock_trx_id: 23078 -- 锁ID为23078 lock_mode: S -- S 锁 lock_type: RECORD lock_table: `burn_test`.`t5` lock_index: GEN_CLUST_INDEX lock_space: 62 -- space id lock_page: 3 -- page number lock_rec: 2 -- heap number lock_data: 0x000000000625 *************************** 2. row *************************** lock_id: 23077:62:3:2 lock_trx_id: 23077 -- 锁ID为23077 lock_mode: X -- X 锁（ for update） lock_type: RECORD lock_table: `burn_test`.`t5` lock_index: GEN_CLUST_INDEX lock_space: 62 lock_page: 3 lock_rec: 2 lock_data: 0x000000000625 2 rows in set (0.00 sec) mysql> select * from INNODB_LOCK_WAITSG *************************** 1. row *************************** requesting_trx_id: 23078 -- 请求的事物ID （ S锁），终端3 requested_lock_id: 23078:62:3:2 -- 由txid， space， page_no， heap_no组成 blocking_trx_id: 23077 -- 阻塞上面请求的事物ID（ X锁），终端1 blocking_lock_id: 23077:62:3:2 1 row in set (0.00 sec)
• MySQL5.6 下查看锁信息 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; -- 在mysql中执行上述SQL，得到以下结果：（保证第二个SQL不超时，才能看到） *************************** 1. row *************************** waiting_trx_id: 23078 -- 请求的事物ID waiting_thread: 21 -- 请求的线程ID waiting_query: select * from t5 where a=2 lock in share mode -- 等待的SQL语句 blocking_trx_id: 23077 -- 阻塞上面请求的事物的ID blocking_thread: 22 -- 阻塞的线程ID blocking_query: NULL -- 阻塞的当前的SQL，这个是无法看到的，除非SQL还没有执行完成（不一定是该事物当中的最后一条SQL语句） 1 row in set (0.00 sec)
• MySQL 5.7 下查看锁信息 -- 继续执行 终端1/终端3 内的SQL语句，使其中有一个线程发生阻塞 mysql> use sys Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from innodb_lock_waitsG *************************** 1. row *************************** wait_started: 2016-01-25 21:00:04 -- 开始的时间 wait_age: 00:00:04 -- 等待的时间 wait_age_secs: 4 -- 等待秒数 locked_table: `burn_test`.`t5` -- 锁住的表（意向锁） locked_index: GEN_CLUST_INDEX -- 锁住的是系统生成的聚集索引，锁都是在索引上的 locked_type: RECORD -- 锁的类型，记录锁 waiting_trx_id: 421206427405024 -- 等待的事物ID waiting_trx_started: 2016-01-25 21:00:04 waiting_trx_age: 00:00:04 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 6 waiting_query: select * from t5 where a=2 lock in share mode waiting_lock_id: 421206427405024:62:3:2 -- 事物ID： space： page_No： heap_no waiting_lock_mode: S -- 等待（请求）的锁的类型 blocking_trx_id: 23592 blocking_pid: 7 blocking_query: NULL blocking_lock_id: 23592:62:3:2 blocking_lock_mode: X -- 阻塞的锁的类型 blocking_trx_started: 2016-01-25 20:59:48 blocking_trx_age: 00:00:20 blocking_trx_rows_locked: 2 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 7 -- 给出了建议 sql_kill_blocking_connection: KILL 7 1 row in set (0.01 sec)
MySQL 5.6 通过导入sys库，也可以支持该视图 锁都是 锁在索引 上的，无论是主键还是二级索引，通过 locked_index 进行查看 当超过 innodb_lock_wait_timeout 设置的阈值，等待（请求）的事物就会报锁超时。在某些业务场景下，锁超时是无法避免的。
二. 锁与并发 • locking （锁） • concurrency control （并发控制） • isolation （隔离级别） • serializability （序列化）
以上四个其实在数据库中讲的是同一个概念； 锁 是用来实现 并发控制 ， 并发控制 用来实现 隔离级别 ， 隔离级别 是通过 锁 来控制的， 锁 的目的为了使得事物之间的执行是 序列化 的
2.1. 事物隔离级别 2. READ COMMITTED ◦ Oracle、 DB2、 Microsoft SQL Server （默认） ◦ 解决 脏读 （ANSI SQL）
3. REPEATABLE READ ◦ InnoDB（默认） ◦ 解决 脏读 、 不可重复读 （ANSI SQL） ◦ InnoDB中的RR解决了幻读问题 （实现了事物的隔离级别）
4. SERIALIZABLE ◦ 解决 脏读 、 不可重复读 和 幻读 （ANSI SQL） 隔离级别 越低 ，事物请求的锁 越少 或者保持锁的时间就 越短 。
• 什么是隔离性? 一个事物 所做的 修改 ，对 其他事物 是 不可见 的，好似是 串行 执行的
-- 终端1 mysql> create table test_rc (a int, b int , c int); Query OK, 0 rows affected (0.14 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_rc values(1,2,3); Query OK, 1 row affected (0.00 sec) mysql> select * from test_rc; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | +------+------+------+ 1 row in set (0.00 sec)
-- 终端2 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_rc values(4,5,6); Query OK, 1 row affected (0.00 sec) mysql> commit; -- 事物提交 Query OK, 0 rows affected (0.03 sec)
-- 终端1 mysql> select * from test_rc; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | -- 一个事物的修改出现在了另外一个事物中，其实是不符合隔离性的要求的 +------+------+------+ 2 rows in set (0.00 sec)
mysql> select @@tx_isolation; -- 查看当前事物的隔离级别 +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | -- 当前是RC的，也就是Oracle， MSSQL 的默认隔离级别，其实是不复合隔离性的要求的 +----------------+ 1 row in set (0.00 sec) 线上环境一般使用 READ-COMMITTED
假如此时有 事物tx2 需要对 记录B （假设和记录A在同一个页中）加 S锁 ： 1. 在该记录所在的 数据库 上加一把 意向锁IS 2. 在该记录所在的 表 上加一把 意向锁IS 3. 在该记录所在的 页 上加一把 意向锁IS 4. 最后在该 记录B 上加上一把 S锁 加锁是 从上往下 ， 一层一层 进行加的