一、MySQL锁机制

1、概述

A.定义

锁是协调多个进程或线程并发访问同一共享资源的机制。
在数据库中，除了传统的计算资源（CPU、RAM、IO）的争用以外，共享数据也变成了一种常见的争用资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。应用锁机制是协调并发的一种方式，但是锁冲突也影响着数据库并发访问的性能，同时也导致问题变得更加复杂。

B.锁的分类

1）从对数据操作的类型(读/写)来分

2）从对数据操作的细粒度来分

2、三锁

A.表锁（偏读，MyISAM为主）

1）特点
偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2）案例分析

#加表锁
#语法
/*
lock table 表名字 read(write),表名字2 read(write),其它;
*/
#查看表是否有锁
show open tables;
#给mylock表上把读锁；给book表上把写锁
lock table mylock read,book write;
#释放所有表的表锁
unlock tables;
/*
注：给mylock表加读锁，此时当前session能读不能写，且不能读其它表；其它session能读但写会被阻塞，且可以读其它表。
*/
lock table mylock read;

#加写锁case
lock table mylock write;
/*
注：当前session能读和写该表，但不能读写其它的表；其它session读写都被阻塞，但可以读写其它的表
*/

3）结论
MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁；在执行增删改之前，会自动给涉及的所有表加写锁。其特点为：表共享读锁，表独占写锁。

注：

1. 对MyISAM表的读操作（加读锁），那么该线程只能读该表不能写该表。不会阻塞其它线程对同一表的读请求，但会阻塞其它线程对该表的写请求直到加锁的线程解锁。除此之外，加锁的线程不能访问其它任意表，而其它线程能够访问任意表。
2. 对MyISAM表的写操作（加写锁），那么该线程既能读又能写该表。会阻塞其它线程对同一表的读写请求直到释放锁，除此之外，加锁的线程不能访问其它任意表，而其它线程能够访问任意表。
   总：读锁会阻塞写，写锁会阻塞读写。
   4）表锁分析

#看看哪些表被锁了，1为被锁，0为无锁。
show open tables;
#如何分析表锁定
/*
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
*/
show status like 'table%';

注：MyISAM的读写锁调度是写优先，这也是MyISAM不适合做以写为主表的引擎，因为加表写锁后，其它线程的读写都会阻塞导致不能做任何事情，甚至有些线程抢不到锁而造成永远阻塞。

B.行锁（偏写，InnoDB为主）

1）特点
偏向于InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也高。
InnoDB与MyISAM最大不同有两点，一是支持事务，二是采用了行级锁。
2）行锁支持事务
A）事务及ACID

B）并发事务处理带来的问题

1. 丢失修改
   T1、T2先后读取同一数据，然后做相应操作，先后写入数据库，那么先提交那个线程的修改就被覆盖，称其为丢失修改。
2. 脏读
   T1更新共享数据，T2读更新后的数据，T1 rollback，那么T2就拿到了脏数据。
3. 不可重复读
   T2拿到共享数据，T1更新数据，并commit，T2再读共享数据，两次数据不一样，称其为不可重复读。
4. 幻读
   T1拿到共享数据，T2插入记录，T1虽可重复读，即不会读到新增的数据，但是它可以修改到新增的记录，称其为幻读。
   
   在第一个session中update以下没有的id，一样能成功，其为幻读。
   
   注：cmd乱码解决
   chcp code，code如下，
   

C）事务隔离级别
脏读、不可重复读、幻读，其实都是数据库读不一致问题，必须由数据库提供一定的事务隔离机制来解决相应的读问题。

注：事务的隔离级别的本质是让事务一定程度的“串行化”，这显然是和并发“矛盾的”，具体隔离级别看业务需要，有时候不可重复读和幻读并不误事，所以可以降低隔离级别来提高并发性。
show variables like ‘tx_isolation%’ 查看隔离级别，MySQL默认repeatable-read级别。
3）案例分析
A）行锁定基本演示
InnoDB在repeatable-read隔离级别即以下，只对写加行锁（自动），所以会出现写阻塞。
B）无索引行锁升级为表锁（重要，行锁变表锁，导致并发性能下降）

# session1 SQL，注：id为varchar类型，我们来个隐式转换导致索引失效
set autocommit = 0;
update tableName set id = '11' where id = 1;
# session2 SQL，注：update另一行记录也会遭到阻塞，因为session1的SQL索引失效，会导致行锁变为表锁。

C）间隙锁危害
请求共享或排它锁时，如果给的where条件不是一个定值，而是一个范围，MySQL会锁定整个范围内的索引键值，即使该索引键值不存在，这个范围类而不存在的键值就称为间隙GAP，这种锁机制称为间隙锁（next-Key锁），此时无法插入该范围类的任何数据，这在某下场景下对性能造成很大伤害。

注：session2的insert语句阻塞。

D）面试题，如何锁定一行？

注：
A）InnoDB存储引擎实现的是行级锁定，所以锁资源消耗上比表锁消耗多，但是并发性能远大于实现表锁的M有ISAM存储引擎。
B）InnoDB的行级锁定中，如果SQL使用不当，让行锁变表锁，将会使InnoDB存储引擎的整体性能比MyISAM存储引擎的性能低，甚至更差。
4）行锁分析
通过检查InnoDB_row_lock状态变量来分析系统上行锁的争夺情况。

show status like 'inoodb_row_lock%';

注：根据这些变量，来分析系统为什么有这么多等待，使用show profiles
5）优化建议
A）尽可能所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
B）合理设计索引，尽量缩小锁的范围。
C）尽可能较少的检索条件，并避免间隙锁。
D）尽可能控制事务大小、减少锁定资源量及时间。
E）尽可能使用低事务隔离级别。

C.页锁

了解即可，