MySQL--锁

目录

一、锁的类型

1.共享锁--行级锁-读

2.排他锁--行级锁-写

3.意向锁--表锁

4.记录锁--行级锁

5.间隙锁--行级锁

6.临键锁

二、锁相关的SQL语句

1.查看当前所有事务

2.查看加锁信息

3.查看锁等待

4.查看表锁

三、死锁问题与解决

1.产生原因：

2.解决办法

---

一、锁的类型

1.共享锁--行级锁-读

        S锁，在事务要读取一条记录时，需要先获取该记录的S锁

        加锁方式：select * from t1 where id=1 lock in share mode；

        释放方式：commit，rollback

2.排他锁--行级锁-写

        X锁，在事务需改动一条记录时，需先获取该记录的X锁

        加锁方式：DML语句默认加X锁

                          手动加：select * from t1 where id=1 for update；

        释放方式：commit，rollback

3.意向锁--表锁

        称为I锁，当有事务给表的数据行加了共享锁或排它锁，同时会给表设置一个标识，代表已经有行锁了，其他事务想要对表加锁时，就不用逐行判断有没有行锁可能跟表锁冲突了，直接读这个标记就知道自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低，而这个标识就是意向锁

        加锁方式：无法手动创建

4.记录锁--行级锁

        对表中的记录进行加锁，简称行锁

        比如：select * from t1 where id=1 for update

        它会在id=1的记录上加上记录锁，以阻止其他事务插入、更新、删除这一行。但id列必须为唯一索引或主键列，否则上述语句加的锁就会变为临键锁，同时查询语句必须为=，不能是> < like，否则也会退化为临键锁

        在通过唯一索引或主键列对数据行进行update操作，也会加记录锁，如：update t1 set age=5 where id=1；

        记录锁是锁住索引记录，而不是真正的数据记录，如果要锁的列没有索引，会进行全表记录加锁

        记录锁也是排它锁（X锁）所以会阻塞其他事务对其插入、更新、删除

5.间隙锁--行级锁

        GAP锁是InnoDB在RR隔离级别下为了解决幻读问题引入的锁机制，是InnoDB中行锁的一种，使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的一条数据

        select * from t1 where id>100 for update

6.临键锁

        next_key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁，也可以理解为一种特殊的间隙锁，通过临键锁可以解决幻读的问题，每个数据行的非唯一索引列上都会存在一把临键锁，当某个事务持有该行数据的临键锁时，会锁住一段左开右闭区间的数据。临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）不存在临键锁

---

        InnoDB中行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁

        记录锁存在于包含主键索引在内的唯一索引中，锁定单条索引记录

        间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔

        临键锁存在于非唯一索引中，锁定一段左开右闭的索引区间

二、锁相关的SQL语句

1.查看当前所有事务

        select * from information_schema.innodb_trx\G;

2.查看加锁信息

        begin

        insert into city (id,name,province,population,district) values(6,'抚顺','辽宁','109万','兴隆台区');

        select * from performance_schema.data_locks\G；

ENGINE	表示使用的存储引擎
ENGINE_LOCK_ID	锁ID
ENGINE_TRANSACTION_ID	事务ID
THREAD_ID	线程ID
EVENT_ID	事件ID
OBJECT_SCHEMA	加锁的表空间数据库
OBJECT_NAME	加锁的表名
PARTITION_NAME
SUBPARTITION_NAME
INDEX_NAME	加锁的索引名称，表级锁为null，行级锁为加锁的索引名称
LOCK_TYPE	锁类型，table是表级锁，record是行级锁
LOCK_MODE	IX        意向排他锁
	IS        意向共享锁
	X        排他锁，既锁记录，也锁间隙
	S        共享锁，既锁记录，也锁间隙
	X,REC_NOT_GAP        排他标准记录锁，只锁记录，不锁间隙
	S,REC_NOT_GAP        共享标准记录锁，只锁记录，不锁间隙
	S,GAP        共享间隙锁，只锁间隙
	X,GAP        排他间隙锁，只锁间隙
	INSERT_INTENTION        插入意向锁
LOCK_STATUS	锁的状态，GRANTED已获取，WAITING等待中
LOCK_DATA	加锁的数据，如果值为supernum pseudo_record，表示高于索引中的任何值，锁定正无穷的范围

3.查看锁等待

        select * from performance_schema.data_lock_waits;

4.查看表锁

        show open tables where In_use>0;

三、死锁问题与解决

1.产生原因：

        死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

        查看死锁信息：show engine innodb status；

2.解决办法

[mysqld]
log-error =/var/log/mysqld3306.log
innodb_lock_wait_timeout=60     #锁请求超时时间(秒)
innodb_rollback_on_timeout = 1  #事务中某个语句锁请求超时将回滚真个事务
innodb_print_all_deadlocks = 1  #死锁都保存到错误日志

还可以参考：【mysql】mysql死锁问题解决方案_mysql死锁解决-CSDN博客

深入解析MySQL死锁：原因、检测与解决方案_mysql 死锁检测-CSDN博客