6-Mysql-表锁，行锁

本文知识点来源于网络，仅限学习笔记使用，记录学习过程与心得，如有不适，请联系。

一、表锁

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

先看几条常用sql：

#查看表有没有被锁
SHOW OPEN TABLES;
SHOW OPEN TABLES WHERE in_use > 0; 
#给表加读锁
LOCK TABLE 表名 READ;
#给表加写锁
LOCK TABLE 表名 WRITE;
#对表解锁
UNLOCK TABLES;

新建表table_locks_test，使用MyISAM存储引擎。

1.1 表锁之共享锁（读锁）

首先利用Navicat 连接mysql数据库，模拟一个用户（数据库连接）我们成为用户1
然后，我们打开命令提示符，创建一个连接，我们成为用户2

用户1先执行加读锁

#给表加读锁
LOCK TABLE table_locks_test READ;

我们查看用户1能不能查询table_locks_test表

SELECT * FROM table_locks_test;

我们发现可以

再看用户1能不能查询其它表

SELECT * FROM employee;

我们通过提示发现不能

再看用户1能不能进行写操作

INSERT INTO table_locks_test(name,age) VALUES('张三',20);

我们发现也不能

再看用户2
先看用户2能不能查看被读锁锁的table_locks_test

SELECT * FROM table_locks_test;

我们发现可以只是该表没有记录

我们再看用户2能不能查看其它表

我们发现用户2可以查询其它表
我们再看用户2能不能对table_locks_test进行写操作

我们发现用户2执行insert操作后，阻塞在了这里
下面我们让用户1释放锁，注意观察用户2的变化

我们发现用户一解锁的同时，用户2的阻塞也释放开，并且成功的执行了insert操作，我们在让用户2查询该表，发现确实数据插入了。

总结：
现有A,B两个数据库连接，A连接给表加共享锁（读锁）时：
1. A连接可以查看被锁的表，B连接也可以查看，即共享
2. A连接不可以执行写操作，B连接也不可以执行写操作，但是会处于阻塞状态，等待解锁后，会立即执行被阻塞的写操作。
3. A连接不可以查看其它表，B连接可以查看其它表。

1.2 表锁之排他锁（写锁）

首先我们让用户1给table_locks_test上写锁

#给表加写锁
LOCK TABLE table_locks_test WRITE;

我们看用户1能否查询table_locks_test表数据

SELECT * FROM table_locks_test;

没有问题，用户1可以执行可以查询table_locks_test表数据
再看用户1能否对table_locks_test执行写操作

INSERT INTO table_locks_test(name,age) VALUES('李四',25);

没问题，用户1可以对table_locks_test执行写操作
再看用户1能否查询其他表数据

SELECT * FROM employee;

显然用户1不能查询其他表数据
我们再针对用户2
首先查看其能不能查询table_locks_test表数据

我们发现执行语句后处于阻塞状态，然后我们释放用户1的写锁，注意观察用户2的变化

用户1解锁后，用户2从阻塞状态恢复并完成了查询操作
让用户1给table_locks_test加写锁，再看用户2能不能执行插入操作

我们发现用户2执行insert写操作被阻塞，我们让用户1释放写锁，看用户2变化

我们看到用户1释放锁的同时，用户2从阻塞状态恢复并完成了insert写操作

让用户1给table_locks_test加写锁，再看用户2能不能查询其他表数据

我们发现用户2可以查询其他表数据

总结：
现有A,B两个数据库连接，A连接给表加排他锁（写锁）时：
1. A连接可以查询该表，B连接查询该表进入阻塞，直到A连接释放锁，B连接恢复并执行查询该表操作。
2. A连接可以对该表进行写操作，B连接对该表写操作后进入阻塞状态，直到A连接释放锁，B连接恢复并执行对该表的写操作。
3. A连接不可以查询其他表，B连接可以查询其他表。

MyISAM引擎默认是write Lock优先于read Lock的，也就是说如果一堆写请求和一堆读请求同时要一张表的锁，那读请求只能在所有的写请求执行完成后才能获得执行机会。这也是MyISAM引擎不适用于以写为主的表，因为对表加写锁后，其他线程不能做任务操作，大量更新会使查询很难得到锁，从而造成永久阻塞。

二、行锁

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，多订力度小，发生所冲突的概率最低，并发也最高。

#查看行锁的状态：
SHOW STATUS LIKE 'innodb row_lock%';

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

#共享锁
select * from 表名 where 条件 lock in share more;
#排他锁
select * from 表名 where 条件 for update;

InnoDB和MyISAM的最大不同点有两个：

1. InnoDB支持事务(transaction)；
2. InnoDB默认采用行级锁。

2.1 事务相关

2.1.1 ACID属性

1. 原子性（Automicity）：
   事务包含的所有操作要么全部成功，要么全部失败。
2. 一致性（Consistency）：
   指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态，即让数据保持一定的合理性。
3. 隔离性（IsoIation）：
   是指当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
4. 持久性（Durability）：
   持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2.1.2 并发事务处理带来的问题

1. 更新丢失：
   两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事物的存在，就会发生丢失更新问题，最后的更新覆盖了其他事务所做的更新。
2. 脏读：
   一个事务读到了另外一个事务已修改尚未提交的数据。
3. 不可重复读：
   针对update操作，一个事务操作范围内两个相同的查询返回了不同的数据，这就是不可重复读。
   例：
   员工发起事务1，查询工资为1W，此时事务1尚未关闭
   财务发起事务2，给员工涨薪1000，并提交该事物
   员工再次在事务1中查询工资发现变为1.1w
4. 幻读：
   针对insert和delete，在同一个事务范围内，两次相同查询，后一次查询看到了前一次查询没有看到的行。
   例：
   助理通过事务1查询所有工资为1W的员工数为10人，事务尚未关闭
   人事部此时发起事务2，将两位薪资1w的新员工录入系统，并提交事务。
   此时助理在事务1中再次查出，发现有12人，多了两条记录。

2.1.3 事务的隔离级别

1. Read uncommitted（读未提交）：
   就是一个事务可以读取到另一个事务未提交的数据
2. Read committed（读已提交）
   一个事务要等另外一个事务提交后才能读取数据
3. Repeatable read（可重复读，mysql默认）
   就是在开始读取数据（事务开启）时，不再允许修改操作
4. Serializable（串行化）
   在改级别下，事务串行化顺序执行，可以避免脏读，不可重复读，幻读。

查看事务的隔离级别可以使用如下语句:

#查看实物的隔离级别，全局和会话
SELECT @@global.tx_isolation,@@tx_isolation;

设置隔离级别：

#全局的，修改了全局的事务隔离级别，那么针对此后所有的会话有效，当前已经存在的会话不受影响
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
#当前会话，修改了会话的事务隔离级别，比如MyBatis，getSqlSession()的时候，只针对这一次拿到的Session有效；比如CMD命令行，只对这一次的窗口有效
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

接下来我们开始漫长的测试：
新建表：

CREATE TABLE `row_locks_test` (
`id`  int(4) NOT NULL AUTO_INCREMENT ,
`name`  varchar(10) NOT NULL ,
`age`  int(3) NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;

我们用navicat连接数据库，模拟事务1操作
我们用命令提示符窗口连接相同的库，模拟事务2操作
先对事务1执行：

#当前会话 隔离级别设置为 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL Read uncommitted;

查询当前会话隔离级别

#查看当前会话事务的隔离级别
SELECT @@tx_isolation;

事务2（黑窗口）执行如下sql，注意不提交

#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('李四',30);
#执行查询操作
SELECT * FROM row_locks_test;

我们在事务1（navicat）中执行如下sql：

#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;

我们发现事务2中尚未提交的操作，被事务1读取到了

提交事务1，2，继续测试：

#当前会话 隔离级别设置为 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL Read committed;

查看当前会话隔离级别

#查看当前事务的隔离级别
SELECT @@tx_isolation;

让事物2（黑窗口）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('王五',35);
#执行查询操作
SELECT * FROM row_locks_test;

让事物1（navicat）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;

我们发现事务1并没有查询到事务2尚未提交的insert数据，避免了脏读。
此时提交事务2，让事务1再次查询

我们发现事务1查询到事务2已经未提交的insert数据，发生了幻读，不可重复读也不能避免（自行验证）。

提交事务1，继续测试：

#当前会话 隔离级别设置为 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable read;

查看当前会话隔离级别

#查看当前事务的隔离级别
SELECT @@tx_isolation;

让事物2（黑窗口）执行如下sql：

#开始一个事务
#开始一个事务
START TRANSACTION;
#执行更新操作
UPDATE row_locks_test SET age = 38 WHERE id = 3;
#执行查询操作
SELECT * FROM row_locks_test;

让事物1（navicat）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;

我们发现事务1并没有读到事务2已经修改（update）但尚未提交的事务，Repeatable read隔离界别避免了脏读。
此时让事务1提交事务

#让事物1（navicat）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;

我们发现事务1并没有读到事务2已经修改（update）并且提交的事务，Repeatable read隔离界别避免了不可重复读。
注意此处幻读可能会发生，因为不好测直接附上结论。

继续测试：

#当前会话 隔离级别设置为 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL Serializable;

#查看当前事务的隔离级别
SELECT @@tx_isolation;

我们先看一下本次测试前的数据：

让事物2（黑窗口）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('田七',50);
#执行删除操作
DELETE FROM row_locks_test WHERE id = 2;
#执行更新操作
UPDATE row_locks_test SET age = 66 WHERE id = 3;
#执行查询操作
SELECT * FROM row_locks_test;

让事物1（navicat）执行如下sql：

#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;

我们发现事务1并没有读到事务2已经插入（insert），删除（delete），修改（update）但尚未提交的事务，Serializable隔离级别避免了脏读。

提交事务2，让事务1再次查询该表数据

我们发现事务1并没有读到事务2已经插入（insert），删除（delete），修改（update）且已提交的事务，Serializable隔离级别避免了不可重复读，幻读。

总结：事务的隔离级别与存在问题的对应关系（是，存在）

事务的隔离级别	脏读	不可重复读	幻读
Read uncommitted	是	是	是
Read committed	否	是	是
Repeatable read	否	否	是（可能）
Serializable	否	否	否

三、 索引失效行锁变表锁

首先要明确两点：

1. 关于行锁，事务1对表中一条数据上锁，事务2同时对该表中另外一条数据做写操作，是不会有阻塞的。
2. 关于表锁，事务1对表中一条数据上锁，事务2同时对该表中另外一条数据写操作，会被阻塞。

测试索引失效行锁变表锁：
原始数据如下：

#正确使用索引
EXPLAIN UPDATE row_locks_test SET `name` = '王八' WHERE age = 3;

#没有正确使用age索引，默认走了主键索引
EXPLAIN UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%6';

首先让navicat执行,但不提交：

#开始一个事务
START TRANSACTION;
#对id=3数据执行更新
UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%6';

然后让黑窗口执行对id=4，id=5的数据执行update：

#执行对id=4，id=5的数据执行update
UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%0';

我们观察黑窗口发现该条update操作被阻塞：

我们提交navicat的事务，注意观察黑窗口：

navicat的事务提交瞬间，黑窗口更新操作也执行成功。
由上述测试得出：索引失效，行锁变表锁

四、 间隙锁

准备数据，原始数据如下

UPDATE row_locks_test SET `name` = '间隙锁' WHERE id > 3 AND id < 6;

间隙锁顾名思义会对区间范围内的数据上锁