mysql 相关（二）锁

Mysql中的锁

锁的粒度
- 表锁
粒度最大的锁，开销小，加锁快，不会出现死锁，但是由于粒度太大，因此造成锁的冲突几率大，并发性能低

Mysql的「MyISAM储存引擎就支持表锁」，MyISAM的表锁模式有两种：「表共享读锁」和「表独占写锁」。当一个线程获取到MyISAM表的读锁的时候，会阻塞其他用户对该表的写操作，但是不会阻塞其它用户对该用户的读操作。相反的，当一个线程获取到MyISAM表的写锁的时候，就会阻塞其它用户的读写操作对其它的线程具有排它性。
- 页锁
「页锁」的粒度是介于行锁和表锁之间的一种锁，因为页锁是在BDB中支持的一种锁机制
- 行锁
「行锁」是粒度最小的锁机制，行锁的加锁开销性能大，加锁慢，并且会出现死锁，但是行锁的锁冲突的几率低，并发性能高。

行锁是InnoDB默认的支持的锁机制，MyISAM不支持行锁，这个也是InnoDB和MyISAM的区别之一。

行锁在使用的方式上可以划分为：「共享读锁（S锁）「和」排它写锁（X锁）」。

当一个事务对MySQL中的一条数据行加上了S锁，当前事务不能修改该行数据只能执行读操作，其他事务只能对该行数据加S锁不能加X锁。

若是一个事务对一行数据加了X锁，该事务能够对该行数据执行读和写操作，其它事务不能对该行数据加任何的锁，既不能读也不能写。
使用方式
- 共享锁
- 排他锁
思想
- 乐观锁
  
  悲观锁的实现是基于MySQL自身的锁机制实现，而乐观锁需要程序员自己去实现的锁机制」
- 悲观锁

每一种锁在不同存储引擎中的运用和实现

MyISAM

MyISAM中默认支持的表级锁有两种：「共享读锁」和「独占写锁」。表级锁在MyISAM和InnoDB的存储引擎中都支持，但是InnoDB默认支持的是行锁。MySQL自动完成了加锁和解锁操作，平时读写操作都是隐式的进行加锁和解锁操作。

# 显式的加锁和解锁
# 显式的添加表级读锁
LOCK TABLE 表名 READ
#  显式的添加表级写锁
LOCK TABLE 表名 WRITE
#  显式的解锁（当一个事务commit的时候也会自动解锁）
unlock tables;

测试

CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE MyISAM；

INSERT INTO employee(name, money) VALUES('张三', 1000);
INSERT INTO employee(name, money) VALUES('李四', 2000);

MyISAM表级写锁

# session1窗口 上锁
LOCK TABLE employee WRITE；
# session2窗口 阻塞
select * from employee；
#  session1窗口 插入查询更新 成功
INSERT INTO employee(name, money) VALUES('王六', 2000)
# > Affected rows: 1
# > 时间: 0.002s

「当一个线程获取到表级写锁后，只能由该线程对表进行读写操作，别的线程必须等待该线程释放锁以后才能操作」。

MyISAM表级读锁

# session1窗口 上锁
LOCK TABLE employee read;
# session2窗口 插入查询更新  阻塞 等session1解锁后才能更新
INSERT INTO employee(name, money) VALUES('王六', 2000);
#  session1窗口 插入查询更新 失败
INSERT INTO employee(name, money) VALUES('王六', 2000);
# > 1099 - Table 'employee' was locked with a READ lock and can't be updated
# > 时间: 0s

MyISAM表级锁竞争情况

可以通过查询变量来查看并发场景锁的争夺情况

show status like 'table%';

1599872576319.png

主要是查看table_locks_waited和table_locks_immediate的值的大小分析锁的竞争情况。

Table_locks_immediate：表示能够立即获得表级锁的锁请求次数；

Table_locks_waited表示不能立即获取表级锁而需要等待的锁请求次数分析，「值越大竞争就越严重」。

实际运用

在实际执行sql时，「解锁和释放锁都是Mysql底层隐式的执行的」。也就是并不用我们手动的去上锁解锁

MyISAM存储引擎中，通过设置内部变量concurrent_insert的值，可以支持并发插入

concurrent_insert的值为NEVER (or 0)表示不支持比并发插入；值为AUTO(或者1）表示在MyISAM表中没有被删除的行，运行另一个线程从表尾插入数据；值为ALWAYS (or 2)表示不管是否有删除的行，都允许在表尾插入数据。

通过sql查看concurrent_insert默认值

show variables like '%concurrent_insert';

锁调度

MyISAM存储引擎中认为写请求比读请求重要。

「假如同时一个读请求，一个写请求过来的话，它会优先处理写请求」

通过设置low-priority-updates参数，设置请求链接的优先级，使得Mysql优先处理读请求。

InnoDB

InnoDB支持「行锁」和「事务」

除了有「表锁」和「行级锁」的概念，还有Gap Lock（间隙锁）、Next-key Lock锁，

「间隙锁主要用于范围查询的时候，锁住查询的范围，并且间隙锁也是解决幻读的方案」

InnoDB行锁和表锁

行锁分为行级「共享读锁（S锁）「和」排它写锁（X锁）」，原理特点与MyISAM的表级锁两种模式相同

# 显式的加锁和解锁
# 给查询sql显式添加读锁
select ... lock in share mode;
# 给查询sql显式添加写锁
select ... for update；

测试

CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE innodb;

INSERT INTO employee(name, money) VALUES('张三', 1000);
INSERT INTO employee(name, money) VALUES('李四', 2000);

上锁

# session1窗口 使用非索引查询
begin
select * from employee where name='张三' for update;
# session2窗口  阻塞 
update employee set name='ldc' where id =2;

# session1窗口  使用索引查询
begin
select * from employee where id=1 for update;
# session2窗口  不阻塞 
update employee set name='ldc' where id =2;

执行非索引条件查询执行的是表锁。
执行索引查询是否是加行锁，一看Mysql的执行计划，可以通过explain关键字来查看。
用普通键索引的查询，遇到索引值相同的，也会对其他的操作数据行的产生影响。

InnoDB间隙锁

当我们使用范围条件查询而不是等值条件查询的时候，InnoDB就会给符合条件的范围索引加锁，在条件范围内并不存的记录就叫做"间隙（GAP）"

我们都知道在事务的四大隔离级别中，不可重复读会产生幻读的现象，只能通过提高隔离级别到串行化来解决幻读现象。

Mysql中通过引入间隙锁的实现来解决幻读，通过给符合条件的间隙加锁，防止再次查询的时候出现新数据产生幻读的问题。

执行下面的sql语句，就会对id大于100的记录加锁，在id>100的记录中肯定是有不存在的间隙：

Select * from  employee where id> 100 for update;

测试间隙锁，新增一个字段num，并将num添加为普通索引、修改之前的数据使得num之间的值存在间隙

alter table employee add num int not null default 0;
update employee set num = 1 where id = 1;
update employee set num = 3 where id = 2;
insert into employee values(4,'test',4000,5);

#session1窗口开启事务
begin;
select * from employee where num = 3 for update ;

#session2窗口测试
#程序出现等待
insert into employee values(5,'ceshi',5000,2);  
#程序出现等待
insert into employee values(5,'ceshi',5000,4);  
#新增成功
insert into employee values(5,'ceshi',5000,6); 
#新增成功
insert into employee values(6,'ceshi',5000,0);

根据索引的有序性，而普通索引是可以出现重复值，那么当我们第一个sesson查询的时候只出现一条数据num=3，为了解决第二次查询的时候出现幻读，也就是出现两条或者更多num=3这样查询条件的数据。为了解决这种问题，mysql在区间（1,3]U[3,5)之间加了锁，是不能够新增数据行，这就是新增num=2和num=4失败的原因，但是在这个区间以外的数据行是没有加锁的，可以新增数据行

主键索引是否会加上间隙锁
- 主键索引具有唯一性，不允许出现重复，那么当进行等值查询的时候id=3，只能有且只有一条数据，是不可能再出现id=3的第二条数据。锁住这个数据就能保证数据的一致性，不用加间隙锁
范围查询是否会加上间隙锁
- 加
使用不存在的检索条件是否会加上间隙锁？
- 假如是查询num>=8的数据行呢？因为employee表并不存在中num=8的数据行，num最大num=6，所以为了解决幻读（6，8]与num>=8也会加上锁。

总结

MyISAM的表锁分为两种模式：「共享读锁」和「排它写锁」。获取的读锁的线程对该数据行只能读，不能修改，其它线程也只能对该数据行加读锁。获取到写锁的线程对该数据行既能读也能写，对其他线程对该数据行的读写具有排它性。MyISAM中默认写优先于去操作，因此MyISAM一般不适合运用于大量读写操作的程序中。
InnoDB的行锁虽然会出现死锁的可能，但是InnoDB的支持的并发性能比MyISAM好，行锁的粒度最小，一定的方法和措施可以解决死锁的发生，极大的发挥InnoDB的性能。
InnoDB中引入了间隙锁的概念来决解出现幻读的问题，也引入事务的特性，通过事务的四种隔离级别，来降低锁冲突，提高并发性能。

mysql 相关（二） 锁