MySQL锁机制及优化方法_mysql释放锁优化-优快云博客

本文链接：https://blog.youkuaiyun.com/javazhangwy/article/details/148217327

锁的定义
计算机为了协调多个线程或进程并发访问同一资源，可以使用锁这种机制。

锁的分类

按照性能分，分为乐观锁和悲观锁。
按照对数据库的操作分，分为读锁和写锁，意识锁。
按照对数据的操作粒度分，分为行锁，页锁和表锁。

读锁、写锁、意向锁
读锁和写锁之间读读，读写不互斥，写读，写写互斥。

即当一条数据先被事务 A 的读锁锁住了，后来事务 B 的操作无论是读还是写都能取到数据；但当被写锁锁住了，后来的操作无论是读还是写都被阻塞。

意向锁
定义：当有事务给表加上了行锁，会给表设置一个标识，代表有锁了，这个标识就是意向锁。

一张表被事务 A 加上了行锁，另一个事务 B 想给这张表加上表锁，如果没有意识锁，事务 B 需要遍历每一行数据判断是否有锁，是否可以加上表锁，有了意识锁，事务 B 只需要判断这个标识就可以判断是否可以加表锁。

当表中记录有很多时，意向锁可以提升加表锁的效率。

行锁，页锁和表锁

行锁
定义：针对一行数据加的锁。

InnoDB 引擎支持行锁，支持事务；MyISAM 引擎均不支持。MyISAM 引擎在执行 select 语句之前会在表上加上读锁，更新操作会给表加上写锁；InnoDB 引擎在执行 select 语句之前不会在表上加锁，更新操作会加上行锁。

InnoDB 行锁锁的是索引的索引项，而不是整张表。并且是对于有索引的字段才能加上行锁，如果字段没有索引，在 RR(可重复读) 隔离级别下就升级为表锁，RC(读已提交) 隔离级别下不会升级为表锁。

页锁
只有 BDB 引擎支持页锁。

表锁
锁了一整张表，一般在数据迁移的时候可以用到。

间隙锁
锁主键值或者唯一值的两个值之间的间隙。例如表中主键值是(1,5,10)，间隙就是 (1,5)，(5,10) ，间隙锁可以让其他事务没有办法在这两个区间内插入数据。针对 RR 隔离级别存在。

在 RR 隔离级别下，会发生幻读问题，间隙锁禁止在某些区间内插入数据，因此可以解决这个幻读问题。

间隙锁实现方式：只要锁住 (1,5) 范围内任意一条不存在的数据就可以为这个区间加上间隙锁。

select * from account where id='3' for update ;

查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status;