INNODB 锁说明：
锁机制是事务型数据库中为了保证数据的一致完整性的特性；在对MySQL进行性能调优时，往往需要重点考虑锁机制对整个事务的影响。MySQL 5.5 版本后InnoDB存储引擎已是默认存储引擎，这里重点介绍InnoDB存储引擎的锁机制。
InnoDB 主要使用两种级别的锁机制： 行级锁和表级锁 。
Innodb 的行级锁分为两种类型: 共享锁和排他锁 。
而在锁机制的实现过程中为了 让行级锁定和表级锁定共存 ，Innodb使用了 意向锁（表级锁） 的概念，分 意向共享锁 和 意向排他锁 这两种。
对于这几种锁模式解释的场景如下：
事务A想读某一行数据，需要给这行数据添加一个共享锁， 此时如果发现这行数据上有一个共享锁锁着，事务A可以继续添加一个共享锁 ，但无法加排他锁。 如果发现这行数据有个排他锁锁着 ，事务A则需要等该锁释放才能进行锁定，此时， 事务A可以在该行数据的表上添加一个意向锁 ，如果需要共享锁，则添加 意向共享锁 ；如果需要排他锁，则需要 添加 意向排他锁 。
所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），可以通过以下表格来总结上面这四种所的共存逻辑关系：
| | 共享锁（S） | 排他锁（X） | 意向共享锁（IS） | 意向排他锁（IX） |
| 共享锁（S） | 兼容 | 冲突 | 兼容 | 冲突 |
| 排他锁（X） | 冲突 | 冲突 | 冲突 | 冲突 |
| 意向共享锁（IS） | 兼容 | 冲突 | 兼容 | 兼容 |
| 意向排他锁（IX） | 冲突 | 冲突 | 兼容 | 冲突 |
根据对数据上锁所锁定的范围不同，又分了以下三种锁算法类型：
· 记录锁（Record Locks）:记录锁锁定索引中 一条记录 。（比如id=5的记录）
· 间隙锁（Gap Locks）: 锁定一个范围，但不包含记录本身 （比如本身是id=5,锁住了id=1,2,3,4的记录）
· Next-Key Locks:Next-Key 锁是索引记录上的记录锁和在索引记录之前的间隙锁的 组合，包含记录本身 。（前两种的组合，比如id=1,2,3,4，5的记录）
MYSQL 中如何查看锁：
通过show engine innodb status\G; 查看是否存在锁表情况：
通过information_schema.innodb_trx 查看事务情况。
select * from information_schema.innodb_trx\G;
通过information_schema.INNODB_LOCKS表查询锁情况：
select * from information_schema.INNODB_LOCKS\G;
通过information_schema.INNODB_LOCK_waits查看锁阻塞情况：
select * from information_schema.INNODB_LOCK_waits\G;
无索引引起行锁升级表锁实验：
会话1：update t表上i=4记录，字段i上没有索引
mysql> start transaction;
会话2：update t表上i=1记录，但此时发现在等待锁。
mysql> start transaction;
由于b列上没有索引，所以在update的时候自动升级为表锁，导致后面的update会话虽然操作的不是同一行，但是仍被堵塞。
通过查询锁情况，这两个会话的情况都对表t请求x模式的锁。
| mysql> select * from information_schema.innodb_locks\G; *************************** 1. row *************************** lock_id: 8710:107:3:2 lock_trx_id: 8710 lock_mode: X lock_type: RECORD lock_table: `cwdtest`.`t` lock_index: GEN_CLUST_INDEX lock_space: 107 lock_page: 3 lock_rec: 2 lock_data: 0x000000000300 *************************** 2. row *************************** lock_id: 8709:107:3:2 lock_trx_id: 8709 lock_mode: X lock_type: RECORD lock_table: `cwdtest`.`t` lock_index: GEN_CLUST_INDEX lock_space: 107 lock_page: 3 lock_rec: 2 lock_data: 0x000000000300 2 rows in set, 1 warning (0.00 sec)
|
尝试通过GDB调试，抓取会话被阻塞时执行的代码，可以发现最终处于隔离级别的判定，此次需要深入解读具体源代码。
| (gdb) p *(trx_sys->rw_trx_list->start->lock->trx_locks->start) $1 = {trx = 0x7f2c0823c980, trx_locks = {prev = 0x0, next = 0x0}, index = 0x0, hash = 0x0, un_member = {tab_lock = {table = 0x7f2b98013468, locks = {prev = 0x0, next = 0x0}}, rec_lock = {space = 2550215784, page_no = 32555, n_bits = 0}}, type_mode = 17} (gdb) p trx_sys->rw_trx_list->start->lock->trx_locks->start->un_member->tab_lock->table->name $2 = {m_name = 0x7f2b98010c88 "cwdtest/t"}
5467 } else if (match_mode == ROW_SEL_EXACT_PREFIX) { (gdb) 5516 if (prebuilt->select_lock_type != LOCK_NONE) { (gdb) 5533 || dict_index_is_spatial(index)) { (gdb) 5530 || srv_locks_unsafe_for_binlog (gdb) 5531 || trx->isolation_level <= TRX_ISO_READ_COMMITTED (gdb) // 隔离级别小于等于读已提交 5532 || (unique_search && !rec_get_deleted_flag(rec, comp)) // 唯一键扫描 (gdb) 5533 || dict_index_is_spatial(index)) { (gdb)
|
此时需要会话1提交完成后，会话2才能正常获取到锁。
因为间隙锁被堵塞：
会话1：
mysql> update t set i=i+9 where i < 4 and i > 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话2：
mysql> insert into t values(4);
| ------------ TRANSACTIONS ------------ Trx id counter 8727 Purge done for trx's n:o < 8720 undo n:o < 0 state: running but idle History list length 16 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421302068564688, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 8726, ACTIVE 194 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 139826788828928, query id 76 localhost root update insert into t values(4) ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 107 page no 4 n bits 80 index i of table `cwdtest`.`t` trx id 8726 lock_mode X locks gap before rec insert intention waiting ------------------ ---TRANSACTION 8725, ACTIVE 208 sec 4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 139826788558592, query id 71 localhost root -------- |
|
mysql> select * from information_schema.innodb_locks\G; *************************** 1. row *************************** lock_id: 8726:107:4:3 lock_trx_id: 8726 lock_mode: X,GAP lock_type: RECORD lock_table: `cwdtest`.`t` lock_index: i lock_space: 107 lock_page: 4 lock_rec: 3 lock_data: 5, 0x000000000304 *************************** 2. row *************************** lock_id: 8725:107:4:3 lock_trx_id: 8725 lock_mode: X lock_type: RECORD lock_table: `cwdtest`.`t` lock_index: i lock_space: 107 lock_page: 4 lock_rec: 3 lock_data: 5, 0x000000000304 2 rows in set, 1 warning (0.00 sec)
ERROR: No query specified
|
由于行级锁产生时是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的，因此在过滤条件中含有范围查询，会造成范围内的行都被锁定。
从上述的实验来看，Innodb引擎行级锁的实现效果远远不及Oracle，由于行级锁的实现严重依赖于索引，所以在SQL优化时需要重点关注索引的信息。
因此，针对Innodb的优化建议，可以简单归纳为以下几点：
（一）尽可能让所有的查询都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
（二）避免大事务的产生，减少被锁的数据、索引和锁定时间长度；
（三）减少基于范围的数据查询过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
死锁实验：
mysql> use cwdtest;
Database changed
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> use cwdtest;
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> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29863023/viewspace-2216730/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29863023/viewspace-2216730/
308
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
