mysql专题 - 锁机制

无论是工作还是面试我们都有很多可能性接触mysql。我打算好好梳理一下Mysql的基础，因此打算开启一个专题。有不对的地方也麻烦指正。

mysql锁进行划分

• 按照锁的粒度划分：行锁、表锁、页锁
• 两种思想上的锁：悲观锁、乐观锁。
• InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key LockRecord Lock：在索引记录上加锁Gap Lock：间隙锁Next-key Lock：Record Lock+Gap Lock

行锁

行级锁是颗粒度最细的一种锁，表示对当前操作的行进行加锁。能减少数据操作的冲突，但是加锁开销最大，有可能会出现死锁。行级锁按照使用方式分为共享锁和排他锁。

• 共享锁（读锁 S锁）事务A对表 T的某一行R进行了（以下简称TR） S锁操作，这个时候其他事务只能对 TR加S锁不能加X锁（X锁在下面）。这保证了其他事务可以读，但在A释放TR上的S锁之前不能对TR做任何修改。-- 加 S 锁示例
  SELECT * FROM tmp_table WHERE id = '3' LOCK IN SHARE MODE;
• 排它锁（写锁 X锁）事务A对表 TR 做了X锁操作，这个时候其他事务不能对 TR加锁。这保证了其他事务在A释放TR上的锁之前不能再读取和修改TR。排它锁也成为独占锁-- 加 X 锁
  SELECT * FROM tmp_table WHERE id = '3' for update;

行级锁是如何引起死锁的呢？

单表操作的时候是不会引起死锁的，当存在多表关联的时候就有可能产生死锁。

-- 伪代码，

-- 事务1开始
select * from tmp_table where id = '1' for update;
select * from tmpA a,tmp_table2 b where a.name = b.name;
-- 事务1结束

-- 事务2开始
select * from tmp_table2 where id = '1' for update;
select * from tmpA a,tmp_table1 b where a.name = b.name;
-- 事务2结束

表锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁。这种锁开销小，不会死锁，但是很容易发生数据操作的冲突。大部分的mysql引擎都支持。MyISAM和InnoDB都支持表级锁，InnoDB默认的是行级锁。

同样的表锁也分为共享锁和排他锁

--  共享锁 读锁
LOCK TABLE tmp_table READ;
-- 排它锁 写锁
LOCK TABLE tmp_table WRITE;
-- 解锁
unlock tables;

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多。一次锁定相邻的一组记录。BDB支持页级锁。目前我工作中很少是哟个 BDB引擎，因此这部分就不看如何加的了，了解个大概就可以

乐观锁和悲观锁

首先客观所和悲观锁并仅仅是数据库的概念，很多地方都会有这种概念。

悲观锁总是把事情想的悲观，在处理数据的时候会认为其他事务会处理自己要处理的数据，就有可能产生错误。为了防止这种情况发生，因此悲观锁在处理数据的时候都是先加锁，加锁成功后在处理自己要处理的事务

乐观锁则相反，在处理数据的时候乐观的认为不会有其他事务来处理数据，因此先进行数据处理，在最终提交数据的时候检查数据是否冲突。

从上面的解释来看

• 悲观锁效率较低（因此持有锁的时间比较长），而且也可能产生死锁，降低了并发量，但是保证了数据的准确性
• 乐观锁不会产生死锁，相对效率也较高，但是有可能会产生脏数据

看下mysql中如何模拟悲观锁

-- 取消mysql事务自动提交
set autocommit=0;
-- 开始事务 (三者选一就可以)
begin;/begin work;/start transaction;
-- 加锁操作及事务操作
select ... for update;
-- 提交事务
commit;/commit work;


-- 窗口1
set autocommit=0;
-- 锁表
select * from tmp_table for update;


-- 窗口2：
-- 会显示等待状态，一直到窗口1执行commit提交事务才会出现下面的显示结果
update tmp_table set tmp6='3' WHERE id = '3' ;

InnoDB中的锁

InnoDB锁的特性

• 在不通过索引条件查询的时候，InnoDB使用的是表锁
• MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
• 表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
• 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。

Record Lock

单条索引上加锁，record lock 永远锁的是索引，而非数据本身，如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

Gap Lock

间隙锁，是在索引的间隙之间加上锁，这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。

ps:幻读指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。

接用网上的例子

mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
|  1 | 伊利   |    68 |   1 |
|  2 | 蒙牛   |    88 |   1 |
|  6 | tom    |  2788 |   3 |
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+
其中id为主键 num为普通索引
窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+
1 row in set

窗口B：
mysql> insert into product_copy values(5,'kris',1888,2);
这里会等待  直到窗口A commit才会显示下面结果
Query OK, 1 row affected

但是下面是不需要等待的
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
mysql> insert into product_copy values(5,'kris',1888,5);
Query OK, 1 row affected

我们要关注的是索引字段num。通过上面的例子可以发现 num=2是被锁住的,而操作 num = 1 或者 5的都能正常使用。

即锁的作用是在1,3的间隙之间加上了锁。因此就防止了幻读的产生