MySQL的锁机制及排查锁问题

MySQL的锁机制及排查锁问题

MySQL的锁机制

1. 按照锁的粒度可以分为行锁和表锁，按照是否可写可以分为读锁和写锁。
2. 行锁是MySQL中粒度最细的锁，对当前操作的行进行加锁。行锁会大大减少数据库操作的冲突，加锁的粒度最小，但是加锁的开销最大，有可能会出现死锁。
3. 行锁的实现算法有Record Lock记录锁、Gap Lock间隙锁和Next-key Lock。记录锁是对单行记录加锁，对索引项加锁，锁定符合条件的行，其他事务不能修改和删除加锁项。间隙锁锁定的是一个范围，不包含记录本身。对索引项之间的间隙加锁，锁定记录的范围，对第一条记录前的间隙或最后一条记录后的间隙加锁，不包含索引项本身。其他事务不能在锁范围内插入数据，这样可以防止其他事务新增幻影行。Next-key Lock是默认的锁，同时锁住数据和间隙锁。锁定索引项本身和索引范围。
4. 表锁是MySQL中粒度最大的锁，对当前正在操作的整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率比较大。InnoDB默认的是行锁。
5. 读锁也成为共享锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的写锁，直到释放所有的读锁。
6. 写锁也成为排他锁，如果事务T对数据对象A加上写锁，就只允许事务T读取和修改数据对象A。

排查锁的问题

1. 排查锁的问题可以从表锁和行锁考虑。
2. 查看表锁的情况后分析。

-- 查看所有线程
show full processlist;
-- 查看正在使用的表
show open tables;
-- 查看innodb服务器信息
show engine innodb status;
-- 查看表锁：Table_locks_waited-出现表锁争用而发生的等待次数，Table_locks_immediate-可以立即获取锁的次数
show status like 'table%';

3. 查看行锁的情况

-- Innodb_row_lock_current_waits：当前正在等待锁定的数量
-- Innodb_row_lock_time：从系统启动到现在锁定的总时间
-- Innodb_row_lock_time_avg：每次等待所花的平均时间
-- Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
-- Innodb_row_lock_waites：从系统启动到现在总共等待的次数
show status like 'innodb_row_lock%';
-- 查看information_schema库
-- innodb_trx表用来显示当前运行innodb事务情况，不能判断锁的情况
-- innodb_locks表可以查看锁的情况
-- innodb_lock_waits表可以查看锁等待的情况