【笔记】SQL 6-3

如何排查表锁

查看表锁情况

SHOW OPEN TABLES;

表锁分析

SHOW STATUS LIKE 'table%';

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

Table_locks_immediate：产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加 1

需要排查当前数据库的表锁情况

show status like 'tables%'

如何排查行锁

行锁分析

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：系统启动后到现在总共等待的次数

优化建议

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

需要排查当前数据库的行锁情况

show status like 'innodb_row_lock%'

MySQL 乐观锁与悲观锁

当很多人（一两个人估计不行）同时对同一条数据做修改的时候，那么数据的最终结果是怎样的呢？
这也就是我们说的并发情况，这样会导致以下两种结果：

• 更新错误，你修改之后的数据可能被别人覆盖了，导致你很懵逼，甚至怀疑自己开发的功能是否有问题；
• 脏读，数据更新错误，导致读数据也是错的，查询出一些默认奇妙的数据，看到的不是你自己修改的结果。

这样的问题怎么解决呢？于是乎，锁就这样产生了，锁分为乐观锁和悲观锁，它的目的是用来解决并发控制的问题。

MyISAM 引擎不支持事务，所以不考虑它有乐观锁和悲观锁概念。MyISAM 只有表锁，锁又分为读锁和写锁。在这里我们只讨论InnoDB 引擎。

需要注意的是，乐观锁和悲观锁并不是解决并发控制的唯一手段（也可以使用消息中间件 kafka，MQ 之类的作为缓冲等等），而且乐观锁和悲观锁并不仅限制在 MySQL 中使用，它是一种概念，很多其他的应用，如 redis，memcached 等，只要存在并发情况的，都可以应用这种概念，只是方式上有些差别而已。

乐观锁

乐观锁，简单地说，就是从应用系统层面上做并发控制，去加锁。

实现乐观锁常见的方式：版本号 version

实现方式，在数据表中增加版本号字段，每次对一条数据做更新之前，先查出该条数据的版本号，每次更新数据都会对版本号进行更新。在更新时，把之前查出的版本号跟库中数据的版本号进行比对，如果相同，则说明该条数据没有被修改过，执行更新。如果比对的结果是不一致的，则说明该条数据已经被其他人修改过了，则不更新，客户端进行相应的操作提醒。

使用版本号实现乐观锁

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行 +1 操作。并判断当前版本号是不是该数据的最新的版本号。

-- 1.查询出商品信息 --
select status,version from t_goods where id=#{id};
-- 2.根据商品信息生成订单 --
-- 3.修改商品status为2 --
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};

 注意第二个事务执行 update 时，第一个事务已经提交了，所以第二个事务能够读取到第一个事务修改的 version。

我们知道 MySQL 数据库引擎 InnoDB，事务的隔离级别是 Repeatable Read，因此是不会出现脏读、