Ref:
https://www.cnblogs.com/itdragon/p/8194622.html
https://blog.51cto.com/lxw1844912514/2938027

行锁

对每一行的数据加锁，这是 MySQL 数据库中最细粒度的锁，右 innodb 引擎支持。对于不能支持行锁的引擎，对于并发操作的处理只能使用表锁锁定整个表，这也是 MyISAM 被 innoDB 所替代的重要原因之一。

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁；对于普通 SELECT 语句，InnoDB 不会加任何锁；当然我们也可以xian shi加锁：
共享锁：select from tableName where … + lock in share more
排他锁：select from tableName where … + for update
InnoDB 和 MyISAM 的最大不同点有两个：

  • InnoDB 支持事务 (transaction)；
  • 默认采用行级锁。加锁可以保证事务的一致性，可谓是有人 (锁) 的地方，就有江湖 (事务)；我们先简单了解一下事务知识。

    MySQL 事务属性

    事务是由一组 SQL 语句组成的逻辑处理单元，事务具有 ACID 属性。
    原子性（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。
    一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。
    隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境执行。
    持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

    事务常见问题

    更新丢失（Lost Update）
    原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比 github 提交冲突。
    脏读（Dirty Reads）
    原因：事务 A 读取了事务 B 已经修改但尚未提交的数据。若事务 B 回滚数据，事务 A 的数据存在不一致性的问题。
    不可重复读（Non-Repeatable Reads）
    原因：事务 A 第一次读取最初数据，第二次读取事务 B 已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。
    幻读（Phantom Reads）
    原因：事务 A 根据相同条件第二次查询到事务 B 提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。
    幻读和脏读有点类似
    脏读是事务 B 里面修改了数据，
    幻读是事务 B 里面新增了数据。

    事务的隔离级别

    数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上 “串行” 进行，这显然与 “并发” 是矛盾的。根据自己的业务逻辑，权衡能接受的最大副作用。从而平衡了 “隔离” 和 “并发” 的问题。MySQL 默认隔离级别是可重复读。
    脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
    1. +------------------------------+---------------------+--------------+--------------+--------------+
    2. | 隔离级别                      | 读数据一致性         | 脏读         | 不可重复 读   | 幻读         |
    3. +------------------------------+---------------------+--------------+--------------+--------------+
    4. | 未提交读(Read uncommitted)    | 最低级别            | 是            | 是           | 是           | 
    5. +------------------------------+---------------------+--------------+--------------+--------------+
    6. | 已提交读(Read committed)      | 语句级              | 否           | 是           | 是           |
    7. +------------------------------+---------------------+--------------+--------------+--------------+
    8. | 可重复读(Repeatable read)     | 事务级              | 否           | 否           | 是           |
    9. +------------------------------+---------------------+--------------+--------------+--------------+
    10. | 可序列化(Serializable)        | 最高级别，事务级     | 否           | 否           | 否           |
    11. +------------------------------+---------------------+--------------+--------------+--------------+
    查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
    1. mysql> show variables like 'tx_isolation';
    2. +---------------+-----------------+
    3. | Variable_name | Value           |
    4. +---------------+-----------------+
    5. | tx_isolation  | REPEATABLE-READ |
    6. +---------------+-----------------+

    行锁优化

    1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
    2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
    3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
    4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

    行锁的使用过程

    对每一行的数据加锁，这是 MySQL 数据库中最细粒度的锁，右 innodb 引擎支持。对于不能支持行锁的引擎，对于并发操作的处理只能使用表锁锁定整个表，这也是 MyISAM 被 innoDB 所替代的重要原因之一。

使用行锁过程中，若一个事务 A 正在更新某一行数据 d，这时候如果事务 B 也想对 d 进行更新操作，那么只能等 A 更新完毕然后再加自己的行锁对 d 进行更新操作。这其中就涉及到一个两阶段锁这个概念。

行锁的两阶段锁协议

两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

其实就是规定了加锁与解锁的时机，两阶段锁协议不仅局限在行锁中。

事务A 事务B
begin
update t1 set k=k+1 where id=1；
update t1 set k=k+1 where id=2；

begin
update t1 set k=k+1 where id=1；
commit

上面的两个事务 AB 执行时候就会使用到两段锁协议：事务 A 先开始执行，id=1 时加锁这一行，id=2 时加锁这一行，事务 A 的两条语句执行完了但是还没有 commit，事务 B 开始执行，但是这个时候事务 B 的 update id=1 会被阻塞，因为 id=1 还被事务 A 加着行锁，虽然事务 A 的 update 执行完了，但是事务 A 还没有 commit，意味着事务 A 所占据着的行锁都没有释放，只有等 A 执行 commit 之后，事务 B 才能继续获得 id=1 的行锁进行 update。

所以我们应该记住两段锁的特点：

  • 在行锁的引擎中，行锁是执行到具体某一行才加上的。
  • 行锁在本本事务 commit 之后才会被释放。

所以根据两段锁协议的特点，我们在开发过程中，应该在事务中把并发大的表放到后面执行，让它被行锁锁定的时间最短。

例如，在减库存，生成订单这样的场景中，我们应该先在事务中生成订单，在减库存。因为库存的 update 并发量会大于订单 insert 的并发量，update 需要使用行锁，如果先 update 库存，会使库存中的这一行一直被行锁锁定，在事务提交时候才能被释放，增加了许多无用的库存行锁锁定时间。

行锁中的死锁

数据库中死锁的概念很清晰，和我们操作系统中的一致：

  • 资源必须互斥访问
  • 请求并保持
  • 不可抢占资源
  • 形成一个环

如果一个项目要新上线一个新功能，如果新功能刚开始的时候 MySQL 就挂了。登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。原因很可能就是死锁。

解决 MySQL 死锁策略

出现死锁以后，有两种解决策略：

设置等待的超时时间。innodb_lock_wait_timeout
主动发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。innodb_deadlock_detect = on，表示开启死锁检测。

innodb_lock_wait_timeout 在 innoDB 引擎中的的默认值是 50s，意味着如果发生死锁的情况，第一个被锁住的线程等待 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，通过设置超时时间通常不是一个好办法，这个更依赖经验值，也依赖不同项目的环境（请求并不均匀）。

所以通常情况下会采用主动死锁检测的策略，innodb_deadlock_detect 默认值就是 on 的状态。主动死锁检测能及时发现并解决死锁，但主动死锁检测会消耗硬件资源。

表锁

表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低
加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：
共享读锁：lock table tableName read;
独占写锁：lock table tableName write;
批量解锁：unlock tables;

什么场景下用表锁

InnoDB 默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL 这样设计并不是给你挖坑。它有自己的设计目的。
即便你在条件中使用了索引字段，MySQL 会根据自身的执行计划，考虑是否使用索引 (所以 explain 命令中会有 possible_key 和 key)。如果 MySQL 认为全表扫描效率更高，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。
第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。
第二种情况：多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

总结

1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。
2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁
3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过 explain 执行计划查询索引是否被实际使用。
4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。
5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。
6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。