MYSQL 常见锁机制详解，常见锁问题排查及分析

1，锁分类

锁冲突是影响数据库性能的重要指标，本章节介绍MYSQL常见锁，及各种说的常用示例，mysql锁的分类如下：

        从操作类型分类：读锁、写锁；

        从操作粒度分类：表锁、页锁、行锁；

        1.1，读锁（共享锁）

        事务T 对某行数据A 加读锁后，所有事务（包括T）的读操作可同时进行且互不影响，但都不可对数据进行修改。加读锁如下：

SELECT * FROM t_account WHERE id = 1 LOCK IN SHARE MODE;

        1.2，写锁（排他锁）

        事务T 对某行数据A 加写锁后，事务T可 读取A 也可修改A，其他事物即不能读取A也不能修改A。所有对数据的增/删/改操作都会为数据加上写锁，读操作可通过for update添加写锁：

SELECT * FROM t_account WHERE id = 1 FOR UPDATE;

        1.3，表锁

        每次操作锁住整张表。操作快，开销小，粒度大，并发度低。表锁细分为：表读锁、表写锁。

                1.3.1，表读锁（表共享锁）：事务T对表加读锁后，其他事务（包括T）都可对该表加读锁，不能加写锁。即：读操作可同时进行，写操作都不能进行。

                1.3.2，表写锁（表独占锁）：事务T对表加写锁后，事务T可对表数据读/写，其他事务不可读/写。需等事务T释放表锁后才可添加表锁。

        表读锁/写锁及释放表锁方式：

-- 表读锁
LOCK TABLE t_account READ;
-- 表写锁
LOCK TABLE t_account WRITE;
-- 释放表锁
UNLOCK TABLES;
-- 查看加锁的表
SHOW OPEN TABLES;

        1.4，页锁

        日常开发极少用到。在第一篇索引数据结构中，聚簇索引的叶子结点包含了主键及对应数据，叶子结点在磁盘中是分页存储的，页锁即锁定一片叶子结点数据页。

        1.5，行锁（重要）

        相比较表锁，每次操作锁住一行。操作慢，开销大，粒度小，并发度高，但是会出现死锁。行锁是InnoDB特有特性，InnoDB相比MYISAM存储引擎，有如下优势：

•         InnoDB支持事务；
•         InnoDB支持行锁；

        行锁示例

        结论：

        1，行锁只锁定当前行的写操作，其他事务对当前行的非加锁的读操作可正常执行，加锁读操作或写操作均不可执行；

        2，其他行的读写操作不受影响；

        3，SQL命中主键索引(id)、非主键单个字段索引(idx_name)、组合索引(idx_name_balance)都可实现锁行。组合索引未命中时，行锁将升级为表锁；

        以下语句都是行锁：

        select * from t_account where id = 5 for update;

        select * from t_account where name = 'lilei' for update;

        select * from t_account where name = 'lilei'  and balance = 1000 for update;

        下述语句行锁将升级为表锁：

        select * from t_account where balance = 1000 for update;

        4，非索引字段查询，行锁直接升级为表锁；

        5，行锁升级为表锁，只在RR级别发生，RC级别不会升级为表锁；

 1.6，间隙锁

        间隙锁只有在RR级别才会生效，指锁住数据间的间隙数据。

如上图，表中数据有共有三个间隙（4～10），（10～20），（20～+∞），只要在间隙范围内，锁住一条不存在的记录，即会锁住整个间隙（不包含边界），间隙锁发生后，其他事务无法在该间隙内插入新数据。示例如下：

锁住ID= 7的数据行，实际改行并不存在。此时新增插入间隙内id = 6的数据，事务会被阻塞。

其他间隙可正常插入数据，将id改为11，换成其他间隙内数据立马插入成功。

锁总结：

        1，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞；

        2，MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会 自动给涉及的表加写锁。

2，锁问题分析及排查

原文地址：https://blog.csdn.net/aquriushu/article/details/142717382

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！