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

大家好，欢迎来到IT知识分享网。

1.锁机制

（1）共享锁和排他锁

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

（2）粒度锁

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

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

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

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

mysql> LOCK TABLES 表名 READ|WRITE; mysql> UNLOCK TABLES 

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

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

2.MyISAM表锁

（1）MyISAM表级锁模式

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

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

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

（2）加表锁方法

（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; 

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

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

（3）InnoDB行锁的实现方式

（4）InnoDB间隙锁

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

（5）InnoDB行锁争用情况

4.LOCK TABLES和UNLOCK TABLES

5.死锁和锁优化

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

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

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