作者：赵博钧石博 | 来源：互联网 | 2023-10-17 09:28
===MySQL锁的概述===
1.针对不同的引擎，采用不同的锁机制；（表锁，页面锁，行锁）
myisam和memory存储引擎： 表级锁；
BOB存储引擎： 页面锁，表级锁；
innodb存储引擎：行级锁（默认），表级锁；
2.三种锁的特性：
开销、加锁速度、死锁、粒度、并发性能
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 （更适用于查询为主，少量的按照索引条件更新的，如Web应用；行级锁更适合含有大量按照索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线失误处理OLTP）
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
3.mysql行锁模式
共享锁S:允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
排他锁X:允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
===实际操作中的问题===
场景：一张千万级大表table_A（若数据量过少，很难观察到具体的现象）
相关现象：
SELECT * FROM information_schema.`PROCESSLIST` WHERE info IS NOT NULL and db = 'DB_A' ORDER BY TIME DESC;
查询当前的process，可以发现如下截图中state
根据以上截图可以发现：在执行alter table_A （管理端）和select table_A（客户端的一条慢sql）这两条sql的时候，alter的状态是wating for table metadata lock。
从时间上判断可知，select 是客户端发起的一个慢sql，分析该sql发现缺少索引index，于是给该表添加索引执行alter 。
1 相关解释：
2
3 alter 语句的状态是wating for table metadata lock。
4 select 语句是首先获取到共享锁，与其他select语句并无冲突。但是共享锁跟排他锁互斥的， alter语句需要获取到排他锁，也就是表锁，那么需要先等待select结束。
解决方案：
未避免alter 语句在执行过程中需要等待select语句从而获得排他锁，那么在执行alter table等DDL语句的时候（definition），需要先将对该表的其他查询kill掉。
补充：
1 意向锁：意向共享锁和意向排他锁，这两种都是表锁；
2 事务在对 行 进行共享锁的时候，需要先对 表 进行意向共享锁；
3 同理意向排他锁。
4.innodb行锁的实现方式===
innodb行锁是通过给索引加锁实现的；注意：
若是innodb不通过索引条件查询时，则会锁表。分3种情形
record lock：对索引加锁；
gap lock ：索引项之间的间隙（first之前，或者last最后）；
next-key lock ：对记录和间隙加锁；
==案例
1 insert into tab1 select * from tab2 where ~;
2 create table tab1 select * from tab2 where ~;
这两个操作需要给tab2加锁，否则在binlog的记录，利用他进行数据库恢复或者主从，则会导致数据不一致；
在rds实际操作中，执行以上两个操作的同时，对tb2执行alter table等DDL语句，则会导致DDL状态为wating for metadata table lock ；而若是对tb2执行insert等DML语句，当两个事务都commit之后，tb1中的数据是未执行DML操作的tb2那一时刻的数据；这个并不涉及到锁，但是和隔离级别以及redo undo有一定的关系。
5.死锁deadlock
myisam是不会出现死锁的，因为它会一次性获取全部锁。
innodb有个自动检测死锁的机制，参数 innodb_lock_wait_timeout可以帮助解决死锁和并发高引起的锁问题。参数在rds上默认设置为50s；（锁冲突和死锁是很难避免的）
京公网安备 11010802041100号