mysql锁详解

一、锁分类

        按锁的粒度来分，有全局锁、表级锁和行级锁；按锁的模式来分，有乐观锁和悲观锁；按锁的属性来分，有共享锁和排他锁；按锁的状态来分，有意向共享锁和意向排他锁；按锁的算法来分，有间隙锁、记录锁和临键锁。

二、全局锁

        全局锁是针对整个数据库的锁，分为全局读锁（共享锁）和全局写锁（排他锁），读锁阻止其他用户更新数据，允许他们读数据，写锁阻止其他用户读取和更新数据。典型应用场景有全库备份和导出。可以使用FLUSH TABLES WITH READ LOCK语句来添加全局读锁，使用UNLOCK TABLES语句来释放锁定。

三、表级锁

        在mysql中，MyISAM引擎对表的读操作会自动加上读锁，对表的写操作会自动加上写锁，适用于读操作多、写操作少的应用，当并发不是特别激烈，以及记录锁并发控制开销大于访问冲突开销的情况。mysql如下命令会发生表级锁：

1、ALTER TABLE更改表结构

2、DROP TABLE删除表和TRUNCATE TABLE删除表中的所有数据

3、LOCK TABLES T1 WRITE给表加上读锁或写锁

4、全表扫描或大范围扫描

5、FLUSH TABLES WITH READ LOCK

        InnoDB引擎主要使用行锁，并在一些情况下使用表级锁，如ALTER TABLE命令或者LOCK TABLES命令等，MyISAM只支持表级锁。

四、行锁

        共享行锁（S锁）允许一个事物读取一行数据，当一行数据被共享锁锁定时，其他事务可以读取这行数据，但不能修改；排他行锁（X锁）允许一个事务读取和修改一行数据，当一行数据被排他锁锁定时，其他事物不能读取也不能修改这行数据。行锁只在事务中有效，只有在一个事务BEGIN后并在事物COMMIT或ROLLBACK之前才能对数据锁定。如果在非事务环境中执行SQL语句，那InnoDB会在语句执行结束后立即释放所有锁。Mysql如下命令会导致发生行锁：

1、SELECT ... FOR UPDATE：对选定行添加一个排他锁，则其他事务不能修改这些行，也不能对这些行添加共享锁；

2、SELECT ... LOCK IN SHARE MODE：对选定的行添加一个共享锁，则其他事务不能修改这些行，但可以对这些行添加共享锁；

3、INSERT：插入操作对新添加的行添加一个排他锁；

4、UPDATE：更新操作会对被更新的行添加一个排他锁；

5、DELETE：删除操作会对被删除的行添加一个排他锁；

        加锁的粒度和范围取决于WHERE子句中用到的索引，如果WHERE子句用到了唯一索引，那么只会锁定匹配的行，如果没用到唯一索引，那么可能会锁定更多的行，InnoDB还支持间隙锁和临键锁，在某些情况下提供更好的并发控制。

五、乐观锁和悲观锁

        乐观锁是一种处理并发问题的技术，假设多个事务同时访问同一条数据时冲突发生的概率较低，在操作数据时不会立即进行锁定，而是在提交数据更改时检查是否有其他事务修改了这条数据，如果没有则提交更改，否则回滚。乐观锁实现方法如下：

1、使用版本号或时间戳字段，每当一条记录被修改时，就增加版本号或更新时间戳；

2、在更新记录时，先检查版本号或时间戳是否和读取记录时的版本号一致；

3、如果一致则执行更新，否则拒绝更新，这样保证只有记录没有被其他事务修改时当前事务才会被提交；

        乐观锁优点在于大部分时间都不需要锁定，所以冲突较少时可以获取较高的并发性能，冲突较多时大量事务回滚会影响性能。

 -----------------------------------------------------------------VS-----------------------------------------------------------

        悲观锁假设在并发处理过程中会发生冲突，因此在任何读写操作前，都会先加锁，适合写操作多和并发冲突高的场景，悲观锁主要通过以下两种SQL语句实现：

1、SELECT ... FOR UPDATE加上排他锁干；

2、SELECT ... LOCK IN SHARE MODE加上共享锁；

六、意向锁

        意向锁是表锁，支持多粒度（表锁和行锁）的锁并存。当事务A有行锁时MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁而增加性能。表一旦被上了一个表级的写锁肯定不能再上一个行级的锁。意向锁的兼容互斥性如下：

	意向共享锁IS	意向排他锁IX
共享锁S	兼容	互斥
排他锁X	互斥	互斥

此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks

 七、间隙锁

        间隙锁主要是为了解决幻读的问题，幻读是指在一个事务内读取某个范围的记录值时，另外一个事务在该范围内插入了新的记录，当再次读取时会出现原本不存在的记录。间隙锁在可重复读和序列化这两个隔离级别才会使用，在读已提交和读未提交这两个隔离级别下不会使用。

START TRANSACTION;

SELECT * FROM MS_CATEGORY WHERE ID BETWEEN 1 AND 100 FOR UPDATE;

COMMIT;

        另外一个事务执行会堵塞：

DELETE FROM MS_CATEGORY WHERE ID = 1;

 八、临键锁和记录锁

        一种特殊的间隙锁，通过临键锁可以解决幻读的问题，每个数据行上的非唯一索引上都会存在一把临键锁，会锁住一段左开右闭区间的数据，在唯一索引列包括主键列上不存在临键锁。

START TRANSACTION;

select * from user_integ where id = 7 for update;

COMMIT;

 另外一个事务执行会堵塞，因为(3,7]被锁住了。

  insert into user_integ values('ll',6,10,'2022-09-10');