mysql的InnoDB没有页锁嘛 mysql页面锁

---

title: MySQL锁

1、概述

不同存储引擎支持不同的锁机制。

• MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
• BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
• InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但是默认情况下采用行级锁。

表级锁、行级锁、页面锁

• 表级锁：开销小，加锁快；不会出现死锁；锁粒度大。发生冲突几率最大，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁，发生锁冲突几率最低，并发度最高。
• 页面锁：介于表级锁和行级锁之间。

查看当前数据库支持的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看当前默认的引擎

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

修改数据库当前引擎

alter table table_name engine=MyISAM;

查看指定表当前的引擎

show table status where NAME ='table_name'

修改mysql默认的数据库引擎

打开配置文件my.ini，将“default-storage-engine=MYISAM”改为你想设定的，然后重启即可

2、MyISAM表级锁

Mysql的表级锁有两种模式：表共享锁（Table Read Lock）和表独占锁（Table Write Lock）.

2.1、共享读锁

lock table 表名 read
unlock table table_name

现在用两个窗口模拟多个client请求mysql,当开启共享读锁时，其他client在修改表数据时，进入等待状态。

解除共享读锁后，其他client才会进行提交操作。

2.2、表独占锁

lock table 表名 write 
unlock table table_name

现在用两个窗口模拟多个client请求mysql,当开启表独占锁时，其他client在查询表数据时，进入等待状态。

解除表独占锁后，其他client才会进行提交操作。

2.3、查询表级锁使用情况

show status like 'table%'
show variables like '%table%'

Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而等待的次数。

两个参数值都是系统启动后开始记录的，出现一次对应的事件，则数量加一。当Table_locks_waited状态值比较高时，那么说明系统中表级锁定争用现象比较明显。

2.4 别名

表名以别名的形式进行操作时，需要在锁操作时添加别名lock table 表名 as 别名 write

2.5 session和锁

一个session使用lock table命令给表加了锁，这个session可以查询锁定表中的记录，但是更新或访问其他表都会提示错误；

同时，另外一个session可以查询表中的记录，但更新就会出现锁等待，查询其他表数据不受影响。

2.6 并发插入

• 当concurrent_insert=0时，此时读不能与insert写共存。
• 当concurrent_insert=1时，如果表中没有空数据块时（即表中没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入数据。（默认设置）。
• 当concurrent_insert=2时，不论有没有空数据块，都允许在表尾插入数据。

2.6.1 查看当前设置状态

mysql> show variables like 'concurrent_insert';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.00 sec)

2.6.2 设置并发插入状态

mysql> set global concurrent_insert = 2;
Query OK, 0 rows affected (0.00 sec)

2.6.3 举例并发插入状态为2（ALWAYS）时

show variables like 'concurrent_insert';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| concurrent_insert | ALWAYS |
+-------------------+--------+
1 row in set (0.00 sec)

现在用两个窗口模拟多个client请求mysql,当client_A开启并发插入时，

mysql> lock table student read local;
Query OK, 0 rows affected (0.00 sec)

client_B在操作表数据时，可以直接修改表中数据。

但是此时client_A对该表进行查询操作时，表中数据依然为未修改数据；而其他client为已修改数据。

2.7 一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一个表的写锁，mysql如何处理？

写进程先获得锁，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁队列。
Mysql认为写请求写请求比读请求更重要。
MyISAM不太适合有大量更新操作和查询操作的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

解决方法：
执行`set low_priority_updates=1`,使该连接发出的更新请求优先级降低。同理insert,delete也可以通过此方法指定。

另外MySQL也提供了一种折中的方法调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定的获取锁的机会

3 Innodb锁

关闭自动提交 `set autocommit=0;`

• 共享锁（S）
• 排它锁（X）
• 意向共享锁（IS）
• 意向排他锁（IX）

	排它锁（X）	意向排他锁（IX）	共享锁（S）	意向共享锁（IS）
排它锁（X）	冲突	冲突	冲突	冲突
意向排他锁（IX）	冲突	兼容	冲突	兼容
共享锁（S）	冲突	冲突	兼容	兼容
意向共享锁（IS）	冲突	兼容	兼容	兼容

3.1 意向锁

3.1.1 意向锁

对于update、delete和insert语句，Innodb会自动给涉及数据集加排他锁(X)；
对于普通的select语句，Innodb不会加任何锁。

事务可以通过以下语句显式的给记录集加锁：

//共享锁
select * from table_name where  ... lock in share mode;
//排它锁
select * from table_name where ... for update;

3.1.2 行锁变表锁

现在用两个窗口模拟多个client请求mysql,当client_A对表中某一行数据进行修改时，触发了索引失效。
client_A未进行commit时，则client_B对该表中其他行数据进行修改时，由于mysql数据库对name类型转换进行优化时，发生了表锁事件。

注释：实例中 `name`字段为`varchar`类型的索引，在更新操作时字段赋值为 666（而不是'666'） ，此时mysql数据库会自动将其类型转化。
转化条件需要设置sql模式为非严格模式

mysql> show session variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

3.1.3 间隙锁(gap lock)

InnoDB使用间隙锁的目的：

• 防止幻读，以满足相关隔离级别的要求；
• 满足恢复和复制的需要；

间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。

例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

防止其他事务将value 15插入column中t.c1，无论该列 中是否已有这样的值，
因为该范围中所有现有值之间的间隙都是锁定的。

间隙可能跨越单个索引值，多个索引值，甚至为空。

对于使用唯一索引来锁定唯一行来锁定行的语句，不需要间隙锁定。
（这不包括搜索条件仅包含多列唯一索引的某些列的情况；在这种情况下，会发生间隙锁定。）
例如，如果该id列具有唯一索引，则以下语句仅使用一个具有id值100 的行的索引记录锁定，
其他会话是否在前面的间隙中插入行并不重要：
SELECT * FROM child WHERE id = 100;

间隙锁定InnoDB是“ 纯粹抑制性的 ”，这意味着它们的唯一目的是防止其他事务插入间隙。

3.1.4 InnoDB使用索引的条件

• 在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
• mysql的行锁是针对索引加的锁。不是针对记录加的锁，虽然是访问不同的行，但是若是相同的索引，会出现锁锁冲突的。
  当表中含有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。
• 即使在条件中使用了索引，但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价决定的，如果MySQL认为全表扫描效率更高，比如很小的表，他也不会使用索引，此时InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突的时候，不要忘记检查SQL的执行计划，以确定是否真正使用了索引。
  关于InnoDB到底是使用行锁还是表锁，我们需要依据索引来决定的，本质上行锁是针对索引加的锁，而非记录！！！
  虽然是访问不同的行，但是若是含有相同的索引，还是会发生锁冲突的！！！
  而且就算条件里面使用了索引，Mysql也不一定走索引，还是要看SQL的执行计划！！！