（十二）MySQL学习笔记——锁机制-优快云博客

本文链接：https://blog.youkuaiyun.com/mbytes/article/details/114765274

文章目录

一、锁的分类
二、InnoDB锁
三、MyISAM锁
四、悲观锁、乐观锁

一、锁的分类

按操作分类：
- 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据记录。
- 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
按粒度分类：
- 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
- 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
- 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
按使用方式分类：
- 悲观锁：每次查询数据时都认为别人会修改，所以每次查询时都加锁。
- 乐观锁：每次查询数据时都认为别人不会修改，更新时会判断一下在此期间别人有没有去更新这个数据，无则更新数据，有则放弃。

不同存储引擎支持的锁：

存储引擎	表级锁	行级锁	页级锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

二、InnoDB锁

0.数据准备

-- 锁机制

-- InnoDB的锁机制
-- 数据准备
-- 创建db12数据库
CREATE DATABASE db12;

-- 使用db12数据库
USE db12;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,99),
                           (NULL,'李四',24,95),
                           (NULL,'王五',25,98),
                           (NULL,'赵六',26,97);
SELECT * FROM student;

1.创建共享锁

START TRANSACTION; -- 每次执行语句前，事务1和2同时开启事务。

-- 事务1 查询id=1的数据并加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 事务2 只查询id=1的数据,可以查到
SELECT * FROM student WHERE id=1;

-- 事务2 查询id=1的数据并加入共享锁，也可以查到
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 事务2 修改id=1的数据，此时卡死，只有事务1提交后，事务2的此语句才能执行成功。
-- 此时事务2还未提交，因此事务1查不到修改后的结果。
UPDATE student SET name='大张' WHERE id=1;

-- 事务2 修改id=2的数据修改成功，因为InnoDB默认是行锁。
UPDATE student SET name='大李' WHERE id=2;

-- 查询score=99的数据并加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
-- 此时事务2 修改id=3的数据不成功，因为InnoDB如果不采用带索引的列加锁，则加的是表锁。事务1提交后，事务2才能修改成功。
UPDATE student SET name='大孙' WHERE id=3;
COMMIT;

2.创建排他锁

-- 创建排他锁，加锁的数据不能被其他事务查询或修改
START TRANSACTION; -- 每次执行语句前，事务1和2同时开启事务。
-- 事务1查询id=1的数据并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 事务2此时正常查询可以查到结果
SELECT * FROM student WHERE id=1;

-- 事务2查询id=1的数据并加入共享锁，会卡死
SELECT * FROM student WHERE id=1 FOR SHARE;

-- 事务1提交后，事务2立马查到结果。因此，排他锁和共享锁不兼容。
-- 另外，排他锁和排他锁也不兼容。
COMMIT;

三、MyISAM锁

MyISAM只能加表锁，不能加行锁，且不支持事务。

0.数据准备

-- 数据准备
-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price INT
)ENGINE = MYISAM;  -- 指定存储引擎为MyISAM

-- 添加数据
INSERT INTO product VALUES (NULL,'华为手机',4999),
                           (NULL,'小米手机',2999),
                           (NULL,'苹果',8999),
                           (NULL,'中兴',1999);
SELECT * FROM product;

1.读锁

所有连接只能查询数据，不能修改。

-- 给product表添加读锁
LOCK TABLES product READ;

-- 窗口1查询id=1的数据
SELECT * FROM product WHERE id=1;

-- 窗口2可以正常查询数据
SELECT * FROM product WHERE id=1;
-- 窗口2修改数据会卡死。加锁后不管哪个窗口，都不能修改数据，只能读取。
UPDATE product SET price=999 WHERE id=1;

UNLOCK TABLES;-- 窗口1解锁后，窗口2修改成功

2.写锁

其他连接不能查询、修改数据，只有本连接可以。

-- 写锁
LOCK TABLES product WRITE;

-- 当前连接可以查询
SELECT * FROM product;
-- 当前连接也可以修改
UPDATE product SET price=99 WHERE id=2;

-- 另一连接窗口查询和修改数据都会卡死，直到连接窗口1解锁。
UNLOCK TABLES;

四、悲观锁、乐观锁

1.悲观锁

悲观锁：整个数据处理中需要将数据加锁。行锁，表锁不论是读写锁都是悲观锁。

2.乐观锁

乐观锁：在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

2.1 版本号机制

给数据表中添加一个version列，每次更新后都将这个列的值加1。
读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

数据准备：

- 创建city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
	NAME VARCHAR(20),                   -- 城市名称
	VERSION INT                         -- 版本号
);

-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),
                        (NULL,'上海',1),
                        (NULL,'广州',1),
                        (NULL,'深圳',1);

现在要修改北京为北京市：

-- 1.查询北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京为北京市，版本号+1。并对比版本号
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE name='北京' AND VERSION=1;