mysql学习笔记（三）全局锁、表锁、行锁

根据加锁的范围，MySql的锁大致可以分为三类：全局锁、表锁、行锁。

一、全局锁

全局锁就是对整个数据库实例加锁，获得全局锁后的数据库就无法进行数据的更新操作与表结构修改操作。

当你需要让数据库变为只读状态时，可以使用Flush tables with read lock语句，此时数据的更新操作会被阻塞。

二、表锁

表锁就是对整个表实例加锁，表锁的语法是lock tables 表名 read/write。

假设需要给表A添加读的表锁，给表B添加写的表锁，lock tables A read,B write。

在表A中获得读的表锁的情况下，其他线程对表A的写操作或读写操作都会被阻塞，只能进行读操作。甚至表A也只能进行读操作，直到表A在执行unlock tables进行解锁。

在表B中获得写的表锁的情况下，其他线程对表B的读写操作都会被阻塞。表B自己也只能进行读写操作，直到表B在执行unlock tables进行解锁。

而在Mysql5.5版本后不需要显示的使用lock、unlock来进行加解锁。引入了MDL的表锁概念。即在访问一个表的时候会被自动加上，保证读写的正确性。

MDL的作用：

对表的读操作加读锁，读锁之间不互斥，所以可以进行并发读取表数据。

对表的更新操作加写锁，读写锁、写锁之间互斥，所以并发进行更新操作时会阻塞。

通过表的读锁和写锁来保证读写的正确性。

因此有了表锁的存在，很容易对线上数据库造成影响。

· 表锁的危害

在对表进行加字段时，有以下这些步骤：

（1）select * from user limit 1;

（2）select * from user limit 1;

（3）alter table user add age int;

（4）select * from user limit 1;

执行顺序：步骤（1）获得了MDL读锁，步骤（2）也是读操作，也需要读锁，由于读锁之间不互斥就算是并发也可以读取数据。

步骤（3）获取了MDL写锁，进行表字段的增加。步骤（4）是读操作获取了MDL读锁，读写锁进行互斥，步骤（4）需要等到步骤（3）执行完毕才可以读取数据。如果这时的读请求量较大会导致步骤（4）后面的所有读操作，需要一直等待直到步骤（3）的写锁释放。

相当于有了表锁的存在在请求量大的情况下由于读写锁的竞争会导致表的不可读写。

不过表锁一般是在数据库引擎不支持行锁的情况下才会使用表锁，所以在我们默认的innoDB中使用的是行锁，会对数据的读取和更新更加友好。

三、行锁

行锁就是对数据表中的行记录进行加锁，比如线程A的sql操作为update user set score = score + 1 where id= 1; 线程B的sql操作为update user set score = score - 1 where id = 1; 那么由于行锁的存在，线程B需要等线程A执行完毕释放行锁后才可以更新。

· 行锁什么时候释放

举个例子：

（1）事务A：开启事物。

（2）A：update user set score = score + 1 where id = 1;

（3）事物B：开启事物 。

（4）B：update user set score = score +1 where id = 1;

（5）A：提交事务。

（6）B：提交事务。

运行后发现，在步骤2中事务A获得了行锁，事务B的更新操作被阻塞，直到A提交事务后B才可进行更新操作。所以行锁都是在事务提交后才释放的。

· 减少行锁冲突

如果两个事务中会有多个锁冲突，尽量将有冲突的行锁往后放到事务的最后，以此来减少行锁的竞争。

举个例子：

（1）开启事物A。

（2）A：给同学A加了10分。

（3）A：给同学B加了5分。

（4）A：记录日志 - 给1班总分加15分。

在上述的例子中，如果有个事务B，需要给同学A加20分，那么久会在步骤（2）中产生冲突，导致事务B阻塞，直到事务A提交事务。

如果给事务A的过程换下位置，即步骤（1）（4）（3）（2），那么事务B需要给同学A加20分，就会在事务A的最后一步阻塞住，从而大大的减少了事务之间的锁等待，提升了并发量。

四、总结

行锁应该是我们平常开发sql中最容易遇到的锁，如果在事务中需要锁多个行，我建议是评估后将最可能造成锁冲突或最可能影响并发度的锁的申请时机尽量往后放，以此来提高事务之间的锁等待，提升并发量。