mysql锁分析

锁的分类

本文参考尚硅谷视频

从对数据操作的类型（读/写）

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分类

• 表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度较低。
• 行锁（偏写）：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页锁：偏向BDB存储引擎,开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表锁

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

案例分析

1.建表

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

#查看所有表的上锁状态

show open tables

#手动增加表锁

#加读锁
lock table mylock read
#加写锁
lock table mylock write

mylock表被加了一个读锁

释放锁

unlock tables;

读锁分析（共享锁）

假设两个使用数据库的窗口A，B

A对mylock表加了读锁，此时

• A和B都可以读mylock表
• A不能读除了mylock表以外的表，因为当前没有把mylock表的锁解除，B可以读所有表
• A修改mylock表会报错，B修改被A加了读锁的mylock，线程会进入阻塞队列，直到A解除读锁才运行

写锁分析(排他锁)

A对mylock表加了写锁，此时

• A可以读取修改mylock，
• A不可以读取其他表，需要先解锁
• B对mylock的所有操作都会进入阻塞队列，直到A解锁

案例结论

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改查前，会自动给涉及的表加写锁

MySQL的表级锁有两种模式：表共享读锁，表独占写锁

结论：

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

2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一张表的所有操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读锁会阻塞写，不会阻塞读，而写锁会把读和写都阻塞

表锁分析

查看那些表被加锁了

show open tables

如何分析表锁定

可以通过table_locks_immediate和table_lock_waited状态变量来分析系统上的表锁定；

show status like 'table%'

table_locks_immediate:产生表级锁定的次数，表示可以立即获取的查询次数，每立即获取锁 次数加1。

table_lock_waited:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高说明存在着较严重的表级锁争用情况。

此外，MyISAM的表锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

意思就是一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 是写进程先获得锁。

不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

• 通过执行命令SET LOWPRIORITYUPDATES=1,使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
• 另外,MySQL也 供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count 设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低, 给读进程一定获得锁的机会。

行锁

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

行锁支持事务，且是建立在索引上的，如果字段未添加索引，则会全表扫描，行锁无法实现

案例分析

行锁的基本演示

create table test_innodb_lock(
	a int(11),
	b varchar(16)
)engine=innodb;
#为a字段建立单值索引
create index test_innodb_a_ind on test_innodb_lock(a);
#为b字段建立单值索引
create index test_innodb_b_ind on test_innodb_lock(b);

InnoDB默认使用行锁，为了演示行锁，先把自动提交管理set autocommit = 0;

两个窗口A，B，都设置set autocommit = 0;

第一种情况：

假设A修改第四行还未提交，此时

• A能正常读取修改后的第四行记录，B窗口查看记录还是原来的记录
• B修改第四行，会阻塞，直到A提交后B的语句才能运行
• A提交后，B也提交后，B才能看到A修改后的记录

读己之所写：自己只能读到自己修改过的，别人读不到。

第二种情况：

A修改第4行，B修改第9行均未提交

• A能读到自己改的第四行，B也能读到自己写的第九行，互不影响
• A，B提交后，两边的4，9行都更新

出现了幻读的现象，即两次读取的值不一样

索引失效行锁升级为表锁

对于上面建立的表test_innodb_lock，a和b字段均建立了索引

两个窗口A，B设置set autocommie=0;

A执行命令：update test_innodb_lock set a = 41 where b = 4000;

这里b是varchar类型的，但是如果给b赋整形，mysql也能识别，但是索引会失效，变成全表扫描

索引失效后行锁也变为表锁，本来此时b如果修改其他行，是能够修改成功的，但是此时行锁变为表锁，会把整张表锁住，B线程进入阻塞状态，直到A窗口commit提交

间隙锁

间隙锁：当我们用范围条件而不是想等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但是并不存在的记录，叫做 “间隙(GAP)”；

InnoDB也会对这个“间隙”加锁，这就是间隙锁（Next-Key锁），就是宁错杀不放过。

举例：假设现在a字段是1,2,3,5,6,7,没有a=4的那一行记录

A，B两个窗口，均set autocommit = 0;

A窗口执行：

update table set b = '0629' where a>1 and a< 7

B窗口执行：

insert into table values(4,'2000')

运行结果：A窗口运行，未提交时，B窗口运行会阻塞。

A窗口的范围在1到7之间，但是a=4这条记录并不存在，在行锁的情况下，还是给第四行加了锁

间隙锁的危害

因为查询执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据，在某些场景下可能会造成很大危害。

面试题：如何锁定一行？

A,B两个窗口，都设置set autocommit = 0;

A把第8行加锁

begin;
select * from table where a = 8 for update;
commit;

在A窗口commit之前，B窗口做的所有修改操作都会被阻塞

行锁分析

显示行锁状态的命令

show status like 'innodb_row_lock%'

参数详解（2.3.5比较重要）：

Innodb_row_lock_current_waits:当前正在等待锁定的数量;

Innodb_row_lock_time(等待总时长）:从系统启动到现在锁定总时间长度;

Innodb_row_lock_time_avg(等待平均时长）:每次等待所花平均时间;

Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;

Innodb_row_lock_waits(等待总次数):系统启动后到现在总共等待的次数;

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离