mysql事务、锁

工大枸杞微铺

已于 2022-11-11 11:28:45 修改

阅读量88

点赞数 2

分类专栏： Mysql MySQL高级文章标签： mysql

于 2021-08-16 09:47:24 首次发布

原文链接：https://blog.csdn.net/weixin_45466462/article/details/119575906

版权

Mysql 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

MySQL高级

3 篇文章 0 订阅

订阅专栏

事务

原子性：事务包含的所有操作要么全部成功要么全部失败

一致性：举例，转账后两人金额和转账前应当是一样的

隔离性：多用户并发访问数据库时，数据库为每一个用户开启事务。不能被其他事务的操作所干扰。不同的隔离级别会产生不同的问题

持久性：事务一旦提交了，对数据库的改变也是永久性的。即使数据库遇到故障也不会丢失提交事务的操作。

事务的隔离级别

隔离级别越高，安全性越高，效率却越来越低，不建议修改数据库默认隔离级别。
mysql默认可重复读、oracle默认读已提交
4种隔离级别、3种问题。

	隔离级别	名称	出现脏读	出现不可重复读	出现幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle / SQL Server
3	repeatable read	可重复读	否	否	是	MySQL
4	serializable	串行化	否	否	否

串行化：相当于为表添加了锁，事务不提交其他事务不能操作该表

数据库隔离级别的查询、修改

读未提交：事务2读到了事务1没提交的数据。脏读了。危害举例：a给b转账500，a问b要了欠条，b查看钱到了，a收到欠条后回滚了转账500的操作，b晚上查看钱没了。gema over！

不可重复读：两次查询查询出了不同的内容。即在时间间隔中有其他事务对数据做出了修改且提交了事务，导致两次查询结果不一致。危害举例：统计公司盈利情况，上午统计100w，汇报上去了，下午查询80w。game over！

幻读：某事务在插入某不存在的数据时插入失败因为在此期间另一事务插入了该条数据但是未提交事务，删除时却删除成功了同上。仿佛出现了幻觉 .当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

锁

事务的隔离性，可能会出现脏读、不可重复读、幻读的问题，当时我们的解决方式是通过修改事务的隔离级别来控制，但是数据库的隔离级别呢并不推荐修改。锁的作用也可以解决掉之前的问题

锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。

不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

锁的分类

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

共享锁：共享锁和共享锁是兼容的。想要对加锁的数据进行修改则需要提交事务。

排他锁不能和其他锁共存。

语法：
InnoDB引擎对带索引的列默认添加行锁。不带索引的列则添加表锁

悲观锁：认为数据随时会修改。借助数据库锁机制来实现。如添加行锁、表锁等操作。
乐观锁：认为数据不会被修改。会在更新的时候会去判断在此期间数据有没有被修改。实现思想：加标记，通过添加标记字段，每进行修改就更改标记，且对标记进行校验。加版本号、加时间戳。

悲观锁和乐观锁使用前提

对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。

锁的演示

打开两个navicat窗口两个连接数据库

1.演示InnoDB锁

数据准备

-- 创建db13数据库
CREATE DATABASE db13;

-- 使用db13数据库
USE db13;

-- 创建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语句 LOCK IN SHARE MODE;

-- 窗口1
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 同时开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事务
COMMIT;

排他锁

-- 标准语法
SELECT语句 FOR UPDATE;

-- 窗口1
/*
	排他锁：加锁的数据，不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

注意：锁的兼容性
- 共享锁和共享锁兼容
- 共享锁和排他锁冲突
- 排他锁和排他锁冲突
- 排他锁和共享锁冲突

2.演示MyISAM锁

数据准备

-- 创建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);

读锁

-- 标准语法
-- 加锁
LOCK TABLE 表名 READ;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	读锁：所有连接只能读取数据，不能修改
*/
-- 为product表加入读锁
LOCK TABLE product READ;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

写锁

-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	写锁：其他连接不能查询和修改数据
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

3.演示悲观锁和乐观锁

悲观锁的概念
- 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
- 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
- 我们之前所学的行锁，表锁不论是读写锁都是悲观锁。
乐观锁的概念
- 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
- 但是在更新的时候会去判断在此期间数据有没有被修改。
- 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
悲观锁和乐观锁使用前提
- 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
- 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。
乐观锁的实现方式
- 版本号
  - 给数据表中添加一个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;
```
- 时间戳
  - 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
  - 每次更新后都将最新时间插入到此列。
  - 读取数据时，将时间读取出来，在执行更新的时候，比较时间。
  - 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。