我们在使用MySQL数据库过程中，如果数据库的读写并发较高，会面对一系列的数据一致性的问题，此时需要对数据表或记录加锁操作来解决并发的一致性问题。
1. 锁类型
我们从锁的类型和使用两个角度，对数据库锁做有以下几个方面的区分。
1.1 从锁大类角度
- 乐观锁，乐观对待并发的数据修改，假设每次读写数据都不会有冲突，只在提交数据的时候检测有没有别的请求更新了这条记录。常见的乐观锁实现方式有数据版本（对数据加version）和对数据加时间戳等；乐观锁的实现层面更多是在业务层，适合并发量较小的场景；
- 悲观锁，在读写数据时，认为会出现数据冲突，因此每个操作都会加锁来控制并发，悲观锁一般由MySQL实现，我们只需要在sql语句中体现即可；悲观锁的实现层面在数据库里，适合并发量较大的场景；
1.2 从锁的使用角度
- 表锁，开销小、加锁快、无死锁、锁定力度大、并发支持度最低；
- 行锁，开销大，加锁慢、会出现死锁、锁定粒度小、发生锁冲突的概率低、并发支持度高；行锁都是基于索引实现的，如果一条SQL语句用不到索引是不会使用行级锁的，会升级为表锁；
- 页面锁，开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发支持度一般，此锁使用较少；
1.3 从悲观锁的角度
悲观锁是数据库层面实现的，它主要有两类：
- 共享锁，read lock，又称为读锁，为读取操作创建的锁，其他请求可以并发读取数据，但任何事务都不能在此时对数据进行修改，除非所有的共享锁均已释放；如果事务T对数据A加上共享锁后，其他事务只能对A再加共享锁，不能加排他锁，否则可能会出现死锁情况；获得共享锁的事务只能读数据，不能修改数据；在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁，当没有其他请求对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞；
1 | select * from table where id = 1 lock in share mode; |
- 排它锁，writer lock，又称为写锁，若某个事务对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束或释放锁之前，其他事务不能对其进行加任何锁，即其他事务只能读；排它锁会阻塞所有的排它锁和共享锁；
1 | select * from table where id=1 for update; |
事务T对id为1的数据记录加了排他锁，此时其他事务就不能对id=1的记录修改了。
1.4 间隙锁
间隙锁严格来说属于行锁的一种，当我们使用范围查询数据而不是使用相等条件时，并请求共享锁或排他锁是，MySQL会给符合规范的在条件范围内的已有数据记录加锁，对于键值在条件范围内但并不存在的数据记录，叫做“间隙”（GAP），InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。假如一个表student，有150条记录，id是从1到150，则：
1 | select * from student where id>100 for update; |
这是一个带范围条件的检索，InnoDB不仅会对符合条件的student值为130的记录加锁，也会对student大于100（这些记录并不存在）的“间隙”加锁。InnoDB使用间隙锁的主要目的有：
- 防止幻读，我们知道数据库默认的REPEATABLE_READ事务隔离级别是不能解决幻读问题的，对于上面的例子，要是不使用间隙锁，如果其他事务插入了id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读，即发现多了一些原本不存在的记录；
- 符合MySQL的恢复机制；
注意，在使用范围条件检索并锁定记录时，InnoDB间隙锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件，以免造成锁阻塞的情况，也会对执行性能造成影响。
2. MyISAM引擎锁
对使用MyISAM引擎的表进行读操作（加读锁），不会阻塞其他请求对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才能执行其他请求的写操作。
对使用MyISAM引擎的表进行写操作（加写锁），会阻塞其他请求对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
因此：
- 读锁只会阻塞写操作，不会阻塞其他请求的读操作；
- 写锁会把读写操作都阻塞；
2.1 加锁和释放锁
隐式上锁（自动上锁和释放锁）：
1 | select //上读锁 |
1 | insert、update、delete //上写锁 |
手动上锁和解锁：
1 | lock table student read; //上读锁 |
1 | lock table student write; //上写锁 |
2.2 锁调度
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的，假如1个请求某个MyISAM表的读锁，同时另一个请求同一表的写锁，此时MySQL会让写操作先获得锁，因为 MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
2.3 什么情况下使用表锁
- 全表更新，若事务需要更新表的大部分数据，此时行锁会导致执行效率降低，有可能会有更多的锁冲突；
- 多表查询，若事务涉及多个表，此时一次性锁定多个表，有利于事务的快速执行；
3. InnoDB引擎锁
InnoDB引擎默认对行进行加锁和解锁，因此，更多的关注行记录的属性。锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。InnoDB存储引擎有3种行锁的算法，分别是：
- Record Lock，单行记录上的锁；
- Gap Lock，间隙锁，锁定一个范围，但不包括记录本身；解决了事务并发的幻读问题；
- Next-Key Lock，Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身；
三种锁算法的关系可参考下图：
3.1 加锁和释放锁
隐式上锁（自动上锁和释放锁）：
1 | select //不会上锁 |
1 | insert、update、delete //上写锁 |
显式上锁（手动）：
1 | select * from tableName lock in share mode;//读锁 |
解锁（手动）：
1 | 1. 提交事务（commit） |
3.2 MVCC机制
MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。
如果从数据库中读数据的同时，有另外写入数据的请求，那么读数据的人有可能会看到『半写』或者不一致的数据。最简单的方法通过加锁，让所有的读请求等待写请求提交完成，但是这样效率会很差。MVCC机制使用了一种不同的手段，每个连接到数据库的读请求，在某个瞬间看到的是数据库的一个快照，写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读请求来说是不可见的。
MVCC的机制就保证了，对于行记录上了写锁后，别的读请求可以使用快照继续读，而不被阻塞。
3.3 InnoDB索引对锁的影响
- 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁，此时索引失效，行锁变表锁；
- InnoDB通过索引来实现行锁，而不是通过锁住某条记录。因此，当操作的两条不同记录拥有相同的索引时，也会因为行锁被锁而发生等待；
- 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行；
4. 锁使用总结
4.1 常用命令
查看表锁的加锁情况：
1 | show open tables where in_use > 0; |
查看表锁的状态：
1 | show status like 'table_lock%'; |
- table_locks_immediate，产生表锁次数；
- table_locks_waited，出现表级锁定争用而发生等待的次数，此值高说明存在着较严重的表锁竞争情况；
查看行锁的状态：
1 | show status like 'innodb_row_lock%'; |
- innodb_row_lock_current_waits，当前正在等待锁定的数量；
- innodb_row_lock_time，从系统启动到现在锁定总时间长度；
- innodb_row_lock_time_avg，每次等待所花平均时间；
- innodb_row_lock_time_max，从系统启动到现在等待最长的一次所花时间；
- innodb_row_lock_waits，系统启动后到现在总共等待的次数；
4.2 锁使用优化
- 数据的查询尽量通过索引，避免无索引时行锁升级为表锁；
- 合理规划数据检索的范围，避免间隙锁带来的性能损失；
- 合理使用事务，避免长事务过多占用锁；
- 设置合理的事务隔离级别，在支持并发和性能上取得平衡；
5. 参考文档
以上内容就是MySQL数据库锁应用总结的全部内容了，谢谢你阅读到了这里！
Author：zhaoyh