MySQL锁相关——锁机制、表锁、行锁、死锁、锁优化等_mysql 锁表

1.锁机制

（1）共享锁和排他锁

• 共享锁（S Lock）：其他事务可读，但不可写
• 排他锁（X Lock）：其他事务不能读取也不能写

（2）粒度锁

从粒度上来说，可以将锁分为全局锁、表锁、页锁和行锁

• 全局锁：可以锁定整个数据库系统，阻止其他事务对数据库进行写入或修改操作。当一个事务获取到全局锁时，其他事务将无法执行任何对数据库写入的操作，直到全局锁被释放。

-- 全局锁，整个数据库处于只读状态，其他操作均阻塞
FLUSH TABLES WITH READ LOCK

-- 释放全局锁
UNLOCK TABLES
1
2
3
4
5

• 表锁：用于锁定整个表，控制对表的并发访问。当一个事务获取到表级锁时，其他事务将被阻塞，无法同时对该表进行写操作或修改操作。

mysql> LOCK TABLES 表名 READ|WRITE;

mysql> UNLOCK TABLES
1
2
3

• 行锁：行级锁的类型有 - 记录锁，也就是仅仅把一条（行）记录锁上； - 间隙锁，锁定一个范围，但是不包含记录本身； - 临键锁：记录锁 + 间隙锁的组合，锁定一个范围，并且锁定记录本身。
• 页锁：页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

【注意】MySQL不同的存储引擎支持不同的锁机制，所有存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现。默认情况下表锁和行锁都是自动获得的，不需要额外的命令，但某些情况下用户需要明确的进行行锁或进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

2.MyISAM表锁

（1）MyISAM表级锁模式

• 表共享读锁：不会阻塞其他用户对同一表的读操作，但会阻塞同一表的写请求
• 表独占写锁：会阻塞其他用户对同一表的读写操作
  select自动加读锁，其他DML、DDL自动加写锁。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。（因此MyISAM表不适用于有大量更新操作和查询操作的应用，因为大量的更新操作会造成查询操作很难获取读锁，从而可能永远阻塞）

可以设置参数low-priority-updates，使MyISAM引擎默认给予读请求优先的权利。

（2）加表锁方法

MyISAM在执行select语句之前会自动给涉及的表加读锁；在执行更新操作前会自动给涉及的表加写锁，这个过程不需要用户干预，因此一般不需要直接使用lock table给MyISAM表显示加锁。
自动加锁的情况下，MyISAM总是一次获得SQL语句所需要的全部锁，这也正是MyISAM表不会出现死锁的原因

（3）表级锁争用情况

可以通过检查table_lock_waited和table_locks_immediate状态变量来分析系统上表锁的争夺，如果table_lock_waited的值比较高，则说明存在着较严重的表级锁争用情况。

3.InnoDB行级锁和表级锁

（1）InnoDB锁模式

InnoDB实现了以下两种类型的行锁：

• 共享锁：允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁
• 排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同的数据集的共享读锁和排他写锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

• 意向共享锁：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的意向共享锁
• 意向排他锁：事务打算给数据行加行排他锁，必须先取得该表的意向排他锁。

（2）InnoDB加锁方法

意向锁是InnoDB自动加的，不需用户干预：对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁；对于普通的select语句，InnoDB不会加任何锁。

事务可以通过以下语句显式给记录集加共享锁或排他锁：

select * from table_name where ... lock in share mode;
1

其他session仍然可以查询记录，并也可以对该记录加share mode的共享锁，但如果当前事务需要对该记录进行更新操作，则很有可能造成死锁


select * from table_name where … for update
其他session可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

select ... for update在执行这个语句的时候会将响应的索引访问条目上排他锁，也就是说这个语句对应的锁就相当于update带来的效果。
【使用场景】为确保自己查到的数据是最新数据，并且查到后的数据只允许自己来修改。
【性能影响】业务繁忙的情况下，如果没有及时commit或者rollback可能会造成其他事务长时间等待，从而影响数据库的并发使用效率

select ... lock in share mode作用是将查找的数据加上一个share锁，这个就是表示其他的事务只能对这些数据进行简单的select操作，并不能够进行DML操作
【使用场景】确保自己查到的数据没有被其他事务正在修改，确保查到的数据是最新数据。但自己不一定能修改数据，因为有可能其他事务对这些数据使用了in share mode的方式上了S锁。
【性能影响】如果没有及时commit或者rollback可能会造成其他事务长时间等待

（3）InnoDB行锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的（Oracle是在数据库块中对相应数据行加锁实现）。【意味着】InnoDB只有通过索引条件检索数据才使用行级锁，否则使用表锁。
只有执行计划真正使用了索引才会使用行锁。

（4）InnoDB间隙锁

当使用范围条件而不是相等条件检索数据并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项枷锁；对于键值在条件范围内但不并不存在的记录叫做“间隙”，InnoDB也会对这个间隙加锁，这种机制叫间隙锁。
显然，这种加锁机制会阻塞符合条件范围内键值的并发插入，往往会造成严重的锁等待。因此实际开发中，尤其是并发插入比较多的应用，应尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁的【目的】：防止幻读，满足相关隔离级别要求；满足恢复和复制的需要。

（5）InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁争夺情况：

Innodb_row_lock_waits和innodb_row_lock_avg的值来判断行锁争用情况，值高意味着竞争比较严重。Innodb_row_lock_waits指系统启动到现在总共在等待的次数。

4.LOCK TABLES和UNLOCK TABLES

单表加写锁：lock table tb_name write;
其他线程不能对该表进行读写操作，当前线程可以读写




单表加读锁：lock table tb_name read;（本线程只能读，其他线程也只能读）

5.死锁和锁优化

【产生原因】两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源从而导致恶行循环。多个事务同时锁定同一个资源是也可能会产生死锁。
【死锁检测】

• 超时法：如果一个事务的等待时间超过规定时间则认为发生了死锁，但这种方式可能会误判死锁，并且若超时时间设置过大可能造成死锁不能及时发现
• 等待图法：事务等待动图反映了所有事物的等待情况。并发控制子系统周期性生成事务等待图并进行检测。如果发现图中存在回路则表示出现死锁
  【避免】
• MyISAM避免死锁：在自动加锁的情况下，MyISAM总是一次性获得SQL语句所需的全部锁，因此不会出现死锁
• InnoDB避免死锁：
  ①　事务开始时通过为与其要修改的每行使用select … for update语句来获取必要的锁，即使这些更改语句是在之后才执行的。
  ②　在事务中如果要更新记录，直接申请足够级别的锁（排他锁），不应该先申请共享锁再申请排他锁（容易造成锁冲突）
  ③　事务如果要修改或锁定多个表，应该再每个事务中以相同的顺序使用加锁语句
  ④　通过select … lock share mode获取行的读锁后，如果当前事务再需要对该记录进行更新操作，很有可能造成死锁。

【锁优化】
A.精心设计索引，并尽量使用索引来访问数据，使锁更加精确从而减少所冲突的机会
B.选择合理的事务大小，小事务发生所冲突的几率也更小
C.最好一次性请求足够级别的锁（修改数据直接申请排他锁）
D.不同程序访问一组表时，尽量约定以相同的顺序访问各表
E.尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
F.除非必须，查询时不显示加锁
G.对于一些特定的事务可以使用表锁来提高处理速度或减少死锁的可能

【MySQL的解决方案】
A.增加锁超时时间，MySQL默认的等待时间为50秒，设置较高的锁超时时间可以避免死锁问题的发生
B.优化事务，事务中应避免使用长时间的锁等待
C.使用InnoDB引擎，支持行级锁定，减少锁定范围，可以更精细地控制锁定的资源

【MySQL死锁检测】
A.SHOW ENGINE INNODB STATUS命令，在结果中查找“LATEST DETECTED DEADLOCK”关键字，如果存在该关键字，则表示存在死锁
B.查看当前未提交的事务（如果死锁等待超时，事务可能还没有关闭）
select * from information_schema.INNODB_TRX;
C.查看正在被访问的表
show open tables where in_use > 0;
D.查看当前出现的锁（8.0版本的）
select * from performance_schema.data_locks;
ENGINE：持有或请求锁的存储引擎。
ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID。（ENGINE_LOCK_ID, ENGINE）值对是唯一的。
ENGINE_TRANSACTION_ID：请求锁的事务的存储引擎内部ID。
THREAD_ID：创建锁的会话的线程ID。
EVENT_ID：导致锁的Performance Schema事件。
OBJECT_SCHEMA：包含锁定表的模式（数据库名）。
OBJECT_NAME：锁定的表的名称。
LOCK_TYPE：锁的类型，如RECORD（行级锁）或TABLE（表级锁）。
LOCK_MODE：如何请求锁，如S（共享锁）、X（排他锁）等。
LOCK_STATUS：锁请求的状态，如GRANTED（锁被持有）或WAITING（锁正在等待）。
LOCK_DATA：与锁相关联的数据（如果有）。对于InnoDB，如果LOCK_TYPE是RECORD，会显示被锁记录的主键值或次级索引值。
E.查看锁等待对应关系
select * from performance_schema.data_lock_waits;
F.死锁记录默认只记录最近一个死锁信息，若要将每个死锁信息都保存到错误日志，启用以下参数：

G.查看行锁信息：
show status like ‘innodb_row_lock_%’;

6.乐观锁、悲观锁和其他锁

• 乐观锁：假设并不会发生冲突，只在提交时检查是否违反数据完整行。乐观锁不能解决脏读问题。
• 悲观锁：假设会发生冲突，屏蔽一切可能违反数据完整性的操作
• 其他锁：
  ①　全局锁
  global read lock
  加锁：FLUSH TABLES WITH READ LOCK
  关闭实例下所有表，并加上全局读锁，防止被修改，直到提交UNLOCK TABLES
  【用途】一般用于备份，mysqldump和xtrabackup都会发起。

query cache lock
对QC中的数据有更新时，都会引发query cache lock

②　MDL锁
meta data lock，事务内对库、表、procedure、function、triggers、event、tablespace等多种对象上加的锁。
事务开启后，会锁定表的meta data lock，其他会话对表有DDL操作时，均需等待MDL释放后执行。