Mysql锁一般使用

2022-09-23 14:41:15

一些很小的项目一般不会特意使用或注意数据锁，其实在事务操作修改与删除时就已经有隐式加锁。一般所有涉及到共享数据都会考虑下数据的原始性问题，保证数据在使用或修改时原始性没有被破坏就需要锁定数据所有权；除非任何时刻同一时间只有一个进程在运行，但这种业务非常少。锁会增加性能开销，使用不合理容易影响项目性能甚至会造成死锁。

Mysql数据库锁受引擎影响，不同的引擎锁的方式不一样。常用的引擎：MyISAM引擎是仅支持表锁，InnoDB引擎支持表锁或索引锁（行锁）。MyISAM引擎已经不再推荐使用，主是要因为MyISAM引擎不支持事务虽然查询性能略微高点，但InnoDB经过几个版本的升级后各方面已经有很大的提升，其中在MySQL5.6版本后InnoDB开始支持全文索引，到5.7后可以使用全文索引分词插件（不过全文索引在关系型数据库中使用并不多，一般会放到更专业的ES上）。
MySQL还有DBD引擎支持页面锁或表锁，它可以代替InnoDB引擎，但这个引擎很少在MySQL中使用。
数据库锁会影响的操作有：修改、删除、插入（数据、字段、索引）和修改、删除表；
MySQL有防死锁功能，一般业务程序造成的死锁数据库会自动结束与之死锁的会话，比如：会话A先锁定表T1然后再锁定表T2，但同时会话B先锁定了表T2然后再去锁定表T1，这个时候数据库会认定为死锁，会保留最后一个加锁引起死锁的会话结束引起死锁的其它会话连接。一般会报如下错误：

Deadlock found when trying to get lock; try restarting transaction

MySQL加锁方式

MySQL的锁是针对会话连接进程的，当连接断开退出锁将自动解除，就如同事务一样如果连接断开前未提交或回滚就退出后则事务自动回滚（比如PHP程序异常后就会自动断开连接并释放锁和回滚事务）。不论是使用锁或事务都不建议在期间做过多非锁或事务必需工作，比如数据验证、加工、过滤、拼装，查询等操作，因为这些操作会增加锁或事务的有效时间进而增加其它会话等待时间导致影响整个系统的吞吐量。一般建议在锁或事务期间尽量做对数据库的一次性读写操作。

表锁

表锁即全表锁定不允许其它会话进程对锁定表做指定的操作，主要是不可读锁和不可写锁操作，这种锁常用的引擎均支持。这种锁只有业务强烈要求才会使用到，绝多数项目不会使用此种锁。使用这种锁后其它会话的连接进程再使用锁定操作时将进入等待状态，如果锁一直不能释放（或释放不及时）将会造成系统瘫痪。表级锁，加锁快。

加表锁
通常SQL语法

lock table[s] table_name [write|read][, table_name1 [write|read] ...]

同一个会话连接只有最后一次表锁SQL有效，前面加的锁会在下一个加锁SQL中自动释放，所以加锁只会一次即可，否则易容造成锁失效。加锁后所有其它会话在做锁定操作后会无期限的等待。
示例：
Mysql锁一般使用

查看加锁

show open tables where in_use > 0;

Mysql锁一般使用

解表锁
通常SQL语法

unlock table[s]

解锁不需要指定表，直接是解除所有当前会话加的表锁，仅限使用lock加的锁。
Mysql锁一般使用
查看解锁
Mysql锁一般使用

索引锁（行锁）

行锁是比较理想的锁，把需要操作的记录行给锁定不影响其它行的操作，可以增加更多的并行操作空间。MySQL的InnoDB引擎支持的行锁是在索引的基础上加锁，所以也可以说InnoDB的锁是索引锁，如果加锁时没有索引或索引没有命中那就会进入到表锁。索引锁是通过查询SQL来加锁所以在加索前一定要分析下SQL语句。同时索引的命中并不是通过desc或explain就可以绝对确定的，MySQL查询优化处理会判断使用索引与不使用索引的最佳方案，一般通过SQL解析中不会命令索引的就不会使用索引，会使用到索引的需要人为的再去判断下所对应的数据量在整表中占比数（唯一或主键索引一般不需要判断），占比量越小越好（参考 < 30% 以内）并且查询的数量越小越好（参考 < 5000）。InnoDB的行锁在事务内有效，释放锁与事务提交、回滚同时进行，如果没有开启事务加锁则会在执行完SQL后自动释放锁（这个自动释放受autocommit配置影响，autocommit是自动提交事务开关，当打开时所有InnoDB引擎的SQL在没有开启事务时会默认给要执行的SQL开启一个事务并在执行完后提交这个事务，如果是手动开启事务则这个选项无效）。行级锁加锁慢。

InnoDB索在官方文档中已经有说明：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
总体来说有两种锁形式，一个是独占锁、一个是共享锁。这两种锁可以并存，也就是同一个会话可以给同一个查询记录添加这两种锁（需要做两次加锁）。

独占锁
用于锁定指定记录或表不允许其它会话连接进程修改或加任意锁仅允许读。这样可以保证数据在当前会话中具体原始性和绝对的专享（只有释放了独占锁其它会话才可以加锁）。

通用SQL语法

select .... for update;

MySQL8以后升级语法

select .... for update [of table_name [, table_name ...]] [nowait | skip locked];

在MySQL8以后的版本可以在加锁时增加其它选项。

  1. [of table_name [, table_name ...]] 选项是如果不想把所有查询的表加锁就可以通过of来指定要加锁的表。
  2. [nowait | skip locked] 选项是指定在加锁时发现数据已经被其它会话锁定应该如何处理。nowait 是不再等待其它会话释放锁直接返回加锁失败错误；skip locked是跳过锁等待直接强制加锁（这时锁权限将被强制夺回，释放后才会留给其它已经加锁的会话）。

注意：

  1. 独占锁最终是行锁或表锁由内部引擎决定并不一定解析有索引就会是行锁。
  2. 独占锁通过查询条件添加，通过索引来确定锁行量（或改为表锁）。建议查询条件中索引颗粒最小的条件放到前面，否则容易变成表锁。
  3. 命中索引之外的查询条件不会影响行锁量。也就是说如果已经命中某个索引再添加的其它条件不会缩小锁行量，不要以为只锁定条件匹配的数据。
  4. 其它进程在修改被锁定的数据会进入有限等待，等待时长受配置 innodb_lock_wait_timeout 限制，默认50S左右可以修改。
  5. InnoDB独占锁行锁有个区间特点，当插入的数据也在独占锁的索引范围内，也会被锁住，只有待锁的释放才可以完成插入操作。
  6. [nowait | skip locked] 两个选项使用需要谨慎，它们会扰乱正常的锁流程，除非你非常确定要做什么。
  7. 同一个会话可以重复加不同索引或相关索引的独占锁，每次锁定均有效。
  8. 事务内的增、删、改操作会自动增加独占锁（这是一种隐式锁，数据库自动完成的，所以只要操作事务就有可能在加锁）。增加记录会锁定增加行，如果其它会话有意修改可以查询到这条记录会进入锁等待中。

常用框架已经提供锁操作的功能，比如：
Laravel

ModelClass::where(‘id‘, 1)->lock()->first();

ThinkPHP

ModelClass::where(‘id‘, 1)->lock(true)->first();

查看InnoDB锁信息

show engine innodb status;

Mysql锁一般使用

注意：锁定的行数可以直接说明是表锁还是行锁，锁定行数接近或大于表总数说明是表锁，否则就是行锁。

共享锁
用于锁定指定记录或表不允许其它会话连接进程修改或加独占锁仅允许加共享锁或读。也就是说共享锁只有全部释放或只有当前会话持有才可以进行修改被锁的数据。

通用SQL语法

select .... lock in share mode;

MySQL8以后升级语法

select .... for share [of table_name [, table_name ...]] [nowait | skip locked];

注意：

  1. 共享锁只是用于在修改数据前没有其它会话的共享锁或独占锁存在才修改
  2. MySQL8以后升级语法与独占锁加锁功能一样，但同时也支持通用语法，两者在一个SQL里只能使用一个。
  3. 共享锁是可重加锁，应用场景比较少。一般用于共享锁数据只读场景。
  4. 多个事务加相同的共享锁时再修改共享锁下的数据会造成死锁，不过数据库处理自动结束死锁进程。

常用框架已经提供锁操作的功能，比如：
Laravel

ModelClass::where(‘id‘, 1)->sharedLock()->first();

ThinkPHP6

ModelClass::where(‘id‘, 1)->lock(‘lock in share mode‘)->first();

使用锁

锁的概念很多，使用时不需要全部清楚，但使用时需要注意或规避一些问题。

  1. 业务逻辑中多个加锁顺序保持一至性，一张表尽量减少多次加锁，避免出现业务死锁。
  2. 事务内的增、删、改都会产生锁，需要避免操作的一至性，或提前获取锁。
  3. 数据库抛出的死锁会导致事务回滚，业务代码中尽量使用一个事务完成操作。
  4. 查询加锁时一定得保证锁的意义，同时也需要减少锁的数据量，尽量使用唯一索引查询加锁，过度使用锁会严重影响系统性能。

事务隔离等级影响

事务隔离等级对锁产生影响不大，事务隔离只是为了解决事务处理中的读脏问题。MySQL有4种等级的事务隔离分别是：

  1. 可读未提交（read uncommitted）。各事务之间可以读取未提交的数据，如果事务回滚就会导致其它事务读取的是脏数据，这种隔离等级是最低的，一般业务不会采用。
  2. 可读已提交（read committed）。事务处理中可以读取已经提交的数据，这种隔离已经解决读脏的问题，事务处理中不保证前后读取的数据一至性，因为存在其它事务修改提交后再查询就有变化。如果同一个事务内多次读取相同的数据可能会出现不一样的结果，那之前读数据可能是幻读（因为数据已经被修改）。大部分情况下，这种事务隔离已经满足业务需求。
  3. 可重读（repeatable read）。事务处理中读取相关的数据会有相关的结果，不会受其它事务的提交修改而影响。如果同一个事务内多次读取相同的数据不会出现不一样的结果，那之后读取的数据可能是幻读（因为数据已经被修改）。这是MySQL默认的事务隔离等级。
  4. 串行（serializable）。这种事务隔离等级相当于在可重读等级下给每个事务内的查询SQL后增加for update来添加独占锁，保证事务内其它事务不可修改，从而解决了事务内的幻读问题。这种事务隔离等级最高，也最理想，但性能最差，一般业务不会采用。

事务隔离等级会直接影响数据库的事务处理能力，MySQL这4种事务等级提供了更多的选择，等级越低事务处理能力越强。事务的理想状态是事务处理中所有需要的数据都保留了原始状态下进行操作处理，但实际应用中需要做一些取舍，好在数据库已经默认为我们选择了更好的事务隔离等级，如果没有特别的要求一般不建议修改事务隔离等级。

查询当前事务等级
show variables like ‘%isolation‘;

设置事务等级
set [global | session] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]

注意：global 是修改当前会话之后的后续所有会话事务（如果是工具可能需要重新连接数据才有效果），session 是当前会话下后续所有事务，不影响其它会话。

配置文件修改事务等级

[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF

官方文档：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

Mysql锁一般使用

上一篇：MySQL: case when 用法


下一篇：mysql学习教程之mysql管理