MySQL锁机制

一定要自己手动去数据库执行, 这样你的印象才是最深的!!!

演示表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 03/08/2022 13:59:03
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_no` char(4) NOT NULL,
  `name` varchar(10) NOT NULL,
  `gender` tinyint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_id_no` (`id_no`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (1, '01', '小庭', 0);
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (2, '02', '小英', 0);
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (3, '03', '小慧', 0);
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (4, '04', '小钟', 1);
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (5, '05', '小丁', 1);
INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (6, '06', '小易', 1);

每执行一步记得使用以下sql看看具体信息哦, 后面就不重复写啦, 尤其是第二个sql

-- 当前运行的所有事务 ，还有具体的语句
select * from information_schema.INNODB_TRX; 
-- 当前出现的锁
select * from performance_schema.data_locks; 
-- 锁等待的对应关系
select * from performance_schema.data_lock_waits;
-- 后面的不用每次执行 --
-- 锁等待时间 - 默认50s
show variables like 'innodb_lock_wait_timeout';
-- 锁信息
show status like 'innodb_row_lock_%';
-- Innodb_row_lock_current_waits：当前正在等待锁定的数量；
-- Innodb_row_lock_time ：从系统启动到现在锁定的总时间长度，单位 ms；
-- Innodb_row_lock_time_avg ：每次等待所花平均时间；
-- Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间； 
-- Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。
show OPEN TABLES where In_use > 0;

关于死锁现象

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student WHERE id = 1 FOR UPDATE;
	SELECT * FROM student WHERE id =2 FOR UPDATE;
UPDATE student set name='小英英' WHERE id = 2;
	UPDATE student set name='小庭庭' WHERE id = 1;
ROLLBACK;
	ROLLBACK;

-- 事务1
BEGIN; 
    SELECT * FROM student WHERE id = 1 FOR UPDATE;
    UPDATE student set name='小英英' WHERE id = 2;
    
ROLLBACK;

------------------------华丽分割线------------------------

-- 事务2
BEGIN; 
    SELECT * FROM student WHERE id =2 FOR UPDATE;
    UPDATE student set name='小庭庭' WHERE id = 1;
    
rollback;

update xxx_table set name='xxx' where id=1; 只需要将主键上 id=1 的记录加上X锁即可(X锁称为互斥锁，加锁后本事务可以读和写，其他事务读和写会被阻塞)

避免死锁的办法:
1. 以固定的顺序访问表和行。简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；
2. 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4. 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5. 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

关于唯一键索引

一共两把锁，一把加在唯一索引上，一把加在主键索引上

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student WHERE id_no='01' FOR UPDATE;
	SELECT name FROM student WHERE id_no='02' FOR UPDATE;
	SELECT id FROM student WHERE id=1 FOR UPDATE;(阻塞)
COMMIT;
	SELECT id FROM student WHERE id=1 FOR UPDATE;(加锁成功)
	ROLLBACK;

-- 事务1
BEGIN; 
    SELECT * FROM student WHERE id_no='01' FOR UPDATE;
    
ROLLBACK;

------------------------华丽分割线------------------------
-- 事务2
BEGIN; 
    SELECT name FROM student WHERE id_no='02' FOR UPDATE;
    SELECT id FROM student WHERE id=1 FOR UPDATE;
    
ROLLBACK;

update xxx_table set name='xxx' where id_no='01'; 这里假设id_no是唯一的。id_no='01'的索引项(id=1)并加上加上X锁，然后根据id=1再到主键索引上找到对应的叶子节点并加上X锁。

一共两把锁，一把加在唯一索引上，一把加在主键索引上。这里需要说明的是加锁是一步步加的，不会同时给唯一索引和主键索引加锁(这种情况在并发的时大家想想有没有什么问题?)。这种分步加锁的机制实际上也是导致死锁的诱因之一。

关于普通索引

一共两把锁，一把加在唯一索引上，一把加在主键索引上

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student WHERE gender=0 FOR UPDATE;
	SELECT * FROM student WHERE gender=1 FOR UPDATE;
SELECT * FROM student WHERE id=6 FOR UPDATE;
COMMIT;
	ROLLBACK;

-- 考虑一下以下操作会出现什么?
-- 事务1
BEGIN; 
    SELECT * FROM student WHERE gender=0 FOR UPDATE;
    SELECT * FROM student WHERE id=6 FOR UPDATE;
    
ROLLBACK;

------------------------华丽分割线------------------------
-- 事务2
BEGIN; 
    SELECT * FROM student WHERE gender=1 FOR UPDATE;
    
ROLLBACK;

下面这个情况, 大家思考一下?

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student WHERE gender=0 FOR UPDATE;
	INSERT INTO test.student(id, id_no, name, gender) VALUES (10, 10, 小十, 0);
COMMIT;(提交事务)
	ROLLBACK;

-- 考虑一下以下操作会出现什么?
-- 事务1
BEGIN; 
    SELECT * FROM student WHERE gender=0 FOR UPDATE;
    
ROLLBACK;

------------------------华丽分割线------------------------

-- 事务2
BEGIN; 
    INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (10, '10', '小十', 0);
    
ROLLBACK;

-- 如果使用下面的方式呢?
-- 事务1
BEGIN; 
    SELECT * FROM student WHERE gender=0 FOR UPDATE;
    
ROLLBACK;
------------------------华丽分割线------------------------

-- 事务2
BEGIN; 
    INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (10, '10', '小十', 1);
    
ROLLBACK;

关于锁升级(非索引列)

MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student WHERE name='小庭' FOR UPDATE;
	INSERT INTO test.student(id, id_no, name, gender) VALUES (10, '10', '小十', 0);(阻塞)
COMMIT;
	INSERT INTO test.student(id, id_no, name, gender) VALUES (10, '10', '小十', 0);(插入成功)
	ROLLBACK;

-- 事务1
BEGIN; 
    SELECT * FROM student WHERE name='小庭' FOR UPDATE;
    
ROLLBACK;
------------------------华丽分割线------------------------

-- 事务2
BEGIN; 
    SELECT name FROM student WHERE name='小英' FOR UPDATE;
    SELECT id FROM student WHERE id=1 FOR UPDATE;

ROLLBACK;

通过 查询当前锁sql : select * from performance_schema.data_locks; 

此时一条记录LOCK_MODE为X, LOCK_DATA也出现了一个新值supremum pseudo record，翻译过来为“上确界伪纪录”。
supremum pseudo record：上确界伪记录。即在id索引范围外的数据。假如id索引值的最小值为1，最大值为20，所以id索引的范围为[1，20]。而“上确界伪记录”代表小与1和大于20的id索引，即（-∞，1）∪（20，+∞）。此时其他事务无法插入id在这个区间的数据，也无法更新现有数据的id到这个区间。

在MVCC机制中能完完全全解决幻读吗?

事务1	事务2
BEGIN;	BEGIN;
SELECT * FROM student; -- ①
	INSERT INTO test.student(id, id_no, name, gender) VALUES (7, '07', '小LIU', 1);
	COMMIT;
UPDATE student set name='小七' where name like '%LIU%';
SELECT * FROM student;
COMMIT; -- ②

-- 事务1
BEGIN;

SELECT * FROM `student`;
-- 6条数据

-- T1第一次执行到这里
-- 等T2事务执行之后, 再执行后面的2条语句

UPDATE student set name='小七' where name like '%LIU%';
SELECT * FROM `student`;
-- 7条数据

COMMIT;

------------------------华丽分割线------------------------

-- 事务2
BEGIN;

INSERT INTO `test`.`student`(`id`, `id_no`, `name`, `gender`) VALUES (7, '07', '小LIU', 1);

COMMIT;

-- 第二次执行整个T2事务

在①处, 查询出来的数据里面是没有id=7这一行数据的, 执行事务2将数据插入并提交之后, 事务1去做更新数据, 在事务1的第②处能查到id=7到这条数据并且是被更新了;

在RR隔离级别下, 事务1第一次执行普通的SELECT语句时生成了一个ReadView, 之后事务2向student表中插入一条新的记录并提交, ReadView并不能阻止事务1执行update或者delete语句来改动这个新插入的记录(事务2已经提交, 并不会造成阻塞), 这样就会改变事务1中原来trx_id指向的是新记录的trx_id, 之后再去使用普通的SELECT语句去查询就可以查询到新插入的记录了, 出现了幻读, 可以理解为InnoDB中MVCC机制并不能完完全全禁止幻读