mysql的锁分类：表锁和行锁和页面锁_mysql表锁、行锁和页锁

一 锁的概念

1.1 锁的作用

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

https://www.cnblogs.com/tod4/p/17384668.html

1.2 锁的分类

​ 为了提高数据库的并发度，每次锁定的数据范围越小越好，理论上只锁定当前操作的数据的方案会获得最大的并发度，但是管理锁也是很耗费资源的事情（涉及锁的获取、检查和释放），因此需要在并发度和性能之间做取舍，这样就产生了锁粒度的概念。

锁所影响的数据的范围称作锁的粒度，锁按照操作粒度从小到大可以划分为行锁、页面、表锁。

从对数据操作的类型

1.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2.写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分：​ 锁所影响的数据的范围称作锁的粒度，锁按照操作粒度从小到大可以划分为行锁、页面、表锁。

1.3 页面中各种锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

二  表锁的操作

2.1 表锁的作用

1.表级锁会锁定整张表，是MySQL最基本的锁策略，能够避免死锁

2.并不依赖于存储引擎（所有的存储引擎对于表锁的策略都是一样的）

3.表锁是开销最小的锁（因为粒度大），但同时出现资源竞争的概率也高，并发性大打折扣

2.2 表级锁的S锁和X锁的使用场景 

LOCK TABLES t READ; --对表t添加表级别的S锁

LOCK TABLES t WRITE; --对表t添加表级别的X锁

​ 一般情况下，不会使用InnoDB存储引擎提供的S锁和X锁，只会在一些特殊情况下，比如说崩溃恢复中用到，在autocommit = 0 、innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表的S锁和X锁可以这么写。

 在对某个表执行INSERT、SELECT 、UPDATE、DELETE操作的时候，并不会对表添加表级别的X锁或者S锁。

简而言之：表级下锁的读写锁就是自己，他人都可以读，都不能写；写锁就是自己可以读写；其他人不能读写。

2.3 表锁的特点

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.4  加锁和解锁的API

2.4.1 上锁的过程

lock table 表名字 read(write), 表名字2 read(write), 其他;

2..4.2 查看表加过的锁

show open tables;

2.4.3释放表加过的锁

     unlock tables;

2.5 表锁的使用场景案例

2.5.1 加读锁情况

1.A可以查自已的表，但不能更新或者增加自已的表数据，不能查看其他表的数据。

2.B可以查A的表，但不能更新或者增加A的表数据（会阻塞），B会话中的update语句一直处于闪烁状态，也即阻塞。

3.执行unlock  tables 命令，直到A释放了读锁，B才可以进行修改动作的提交。

2.5.2 加写锁情况

1.A可以查自已的表，也可以更新或者添加自已的表数据。

2. B不可以查A的表，也不能更新或者增加A的数据（会阻塞），B会话中，查询语句一直处于闪烁状态，即阻塞。

3.直到A释放了写锁，B会话中的查询即可进行执行。

2.5.3 总结

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之：表级下锁的读写锁就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

2.6 意向锁

2.6.1 意向锁的作用

1.InnDB存储引擎支持多粒度锁，它允许行级锁和表级锁共存冲突

2.意向锁就是一种不与行级锁冲突的表级锁，表示"某个事务在该表的某些行持有锁或者准备持有锁"

意向锁分为两种：

1.意向共享锁：表示事务有意向对表中的某些行添加S锁。

SELECT ... FROM t LOCK IN SHARE MODE; -- 事务想要获取行级别S锁，必须先获取表级别的IS锁。

2.意向排它锁：表示事务有意向对表中的某些行添加X锁。
SELECT ... FROM t FOR UPDATE; - 事务想要获取行级别X锁，必须先获取表级别的IX锁

2.6.2 意向锁的结论

1.InnoDB支持多粒度锁，在特定场景下，行级锁和表级锁可以并存

2.意向锁之间互不排斥，但除了IS和S锁兼容外，意向锁会和 表级别的 共享锁、排它锁互斥

3.IS和IX是表级别的锁，不会和行级别的S、X锁冲突，只会和上面的表级别的锁冲突

4.意向锁在保证并发性的前提下，实现了表锁和行锁共存，并且满足事务隔离性的要求

2.7 表锁的优化

具体可以通过如下进行优化：

1尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

2合理设计索引，尽量缩小锁的范围

3尽可能较少检索条件，避免间隙锁

4尽量控制事务大小，减少锁定资源量和时间长度

5尽可能低级别事务隔离

三  行锁的操作

3.1 行锁的作用

1.行锁也称记录锁，就是锁住某一行的记录

2.MySQL服务器层面没有实现行级锁，只在存储引擎层实现

3.行锁的优点是粒度小，发生冲突的概率低，并发度高

4.缺点是粒度小造成的锁数量多，因此开销比较大，加锁比较慢，存在死锁问题

​ InnoDB与MyISAM的主要不同：1.InnoDB支持事务；2.nnoDB支持行级锁

3.2  X锁和S锁

-- S锁
select * from t where id = x in share mode;
select * from t where id = x for share;
-- X锁
select * from t where id = x for update;

如何锁定一行的数据（排他锁）：

1.begin

2.sql数据  for  update

3.commit

一个是查询： select * from test_innodb_lock where a=8 for update

另一个是修改： update test_innodb_lock set b='8001' where a=8

只有查询session的会话commit 才不会阻塞，修改的session会话才能提交。。。。

添加共享锁是：
加共享锁可以使用select ... lock in share mode语句

3.3  间隙锁

3.3.1 间隙锁概述

间隙锁存在的条件是必须在RR隔离级别下并且检索条件必须有索引 否则就是表锁了

​ MySQL在Repeatable Read隔离级别下是可以解决幻读问题的，解决的方案有两种：

1）通过MVCC： 读操作利用多版本并发控制（MVCC），写操作加锁

MVCC就是生成一个ReadView，通过ReadView能够找到符合条件的记录版本（历史版本由undo log提供查询），查询语句执行查询已经提交的事务做出的更改，对于没由提交的事务和ReadView创建之后的事务做出的更改是看不到的。而写操作肯定是针对的最新版本的记录，因此读记录的历史版本和写操作的最新记录版本并不会冲突，也就是采用MVCC时，读写操作并不会冲突。

普通的SELECT语句在READ COMMITTED 和 REPEATABLE READ隔离级别下的读操作就是利用MVCC进行的读

1.READ COMMITTED：由于不会读取没有提交的事务修改的数据版本，因此避免了脏读问题

2.REPEATABLE READ：由于不会读取Read View创建之后的事务更改的数据(一个事务只有在第一次执行SELECT语句才会生成一个Read View，之后的SELECT语句都在复用)，因此避免了可重复读和幻读问题。

2）通过加锁的方式： 读、写操作都采用加锁的方式

在一些业务场景中，不允许读取数据的历史版本，即每次都需要去读取磁盘中最新的数据，这样也就意味着读操作也需要和写操作一样排队执行。如此一来，脏读和不可重复读问题都得到了解决，因为读操作和写操作变为串行执行。

但是，幻读问题有些尴尬，试想一个事务在进行读操作，因此给表中的一定范围内的数据加锁，但是另一个事务要写的这个幻影数据可不在这个范围里面，也就是两个读写操作并不会冲突，仍然会出现幻读问题

正如上面所说，由于幻影记录并不存在，所以无法给这些幻影记录添加记录锁，因此针对幻影记录的插入提出了间隙锁(Lock_gap)，如下图id为8的记录添加了Lock_gap锁，就意味着不允许别的记录在id值为8的记录前面添加记录，其实就是(3, 8)这个开区间内不允许插入，直到拥有这个间隙锁的事务提交释放掉锁之后。

3.3.2 操作案例

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。加了间隙锁，但是想添加间隙的东西，发现不可修改。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

缺点是：

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

3.4 临键锁(next-key locks)

1.临键锁(next-key locks)，又称LOCK_ORIDINARY，

2.临键锁是在InnoDB存储引擎、隔离级别在可重复读情况下默认使用的数据库锁

3.临键锁的本质是记录锁和间隙锁的组合，既能对行记录添加记录锁，又能在记录插入前面的间隙添加间隙锁

​ sql语句如下，会针对stu_no = 8的记录添加写锁，针对区间(8, 15)添加间隙锁。

select   * from  student1 where  stu_no>=8 and stu_no <15 for update;

会话1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student1 where stu_no>= 8 and stu_no < 15 for update;
+--------+--------+-------+
| stu_no | name   | class |
+--------+--------+-------+
|      8 | wangwu | erban |
+--------+--------+-------+
1 row in set (0.00 sec)
 
mysql> 

会话2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
-- 获取记录锁堵塞
mysql> select * from student1 where stu_no = 8 for share;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 间隙锁堵塞
mysql> insert into student1 values(12 , '12', '12');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

3.5 插入意向锁

1.插入意向锁指的是一个事务在插入的过程中，检测到别的事务在插入位置添加了gap锁而进入堵塞。

2.插入意向锁是一种特殊的间隙锁，可以锁定开区间内的部分数据

3.插入意向锁互不排斥，只要记录本身（主键、唯一索引等）不冲突，那么事务就不会出现冲突等待

3.6 行锁操作案例

3.6.1 数据表准备

1.新建一张表

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

2.初始化数据

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

3.创建两个索引

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);   --- 对a字段创建索引

CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b); -- 对b字段创建索引

4.开启事务

通过命令行的 SET autocommit=0;该命令是开启手动提交事务，只需要将其自动提交关闭即可，即可操作行锁。

3.6.2 情况1：修改同一行数据

1.两个进程都开启手动提交，而且修改同一行数据，假设A进程修改了事务，B事务也进行修改的话，进程会被阻塞。

 2.只有当A提交了事务之后，B进程才会解除阻塞

3.6.3 情况2：修改不同行数据

1. 两个进程都开启手动提交，修改不同行数据

2.在各自会话中都进行修改后，只能看到自己的修改，别人修改的数据看不到；只有各自事务都自己提交后，才会更新，两者才能看到自己的修改和对方的修改。

 

3.6.4 情况3：行锁变表锁 

类型转换导致索引失效，失效之后只能全表锁定，因为innodb是索引加锁。行锁变表锁由于表锁了，即使A提交了事务，B想修改事务（即使不同行也回阻塞），也会被阻塞。只有等他到表锁他自动解除。索引失效行锁变为表锁，出现锁表的情况，其他进程无法操作。

四  页面锁

4.1 页面锁的作用

1.页级锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要高

2.开销介于表锁和行锁之间，并发度一般

每个层级的锁数量是有限的，因为锁会占用内存空间，锁空间大小是有限的。当某个层级的锁数量超过了这个层级的阈值之后，就会进行锁的升级，即采用更高粒度的锁，比如行锁升级为表锁，好处是空间占有变小，但并发度也降低了。

3.6.4 情况3：行锁边表锁