MySQL：锁问题

最新推荐文章于 2023-06-15 09:48:43 发布

阅读量306

点赞数

分类专栏： # MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/weixin_45730091/article/details/103416344

版权

MySQL 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

锁的概念和分类

在现实生活中是为我们想要隐藏于外界所使用的一种工具,在计算机中，是协调多个进程或线程并发访问某一资源的一种机制,在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源,如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。锁对数据库而言显得尤为重要。

在购买商品时,商品库存只有1个时,两个人同时买时,谁买到的问题，会用到事务,先从库存表中取出物品的数据,然后插入订单,付款后,插入付款表信息。更新商品的数量,在这个过程中,使用锁可以对有限的资源进行保护,实现隔离和并发的矛盾
锁分类

按操作分
- 读锁(共享锁)：针对同一份数据,多个读取操作可以同时进行而不互相影响
- 写锁(排它锁)：当前写操作没有完成前,会阻断其他写锁和读锁
按粒度分
- 表锁
- 行锁
- 页锁

表锁

偏向MyISAM存储引擎,开销小, 加锁快, 无死锁,锁定粒度大, 发生锁冲突的概率最高, 并发最底,整张表就只能一个人使用
示例
1.建立一张Myisam引擎的表

CREATE TABLE ` USER ` (
` ID ` INT ( 11 ) NOT NULL AUTO_INCREMENT,  
` NAME ` VARCHAR ( 255 ) DEFAULT NULL,
PRIMARY KEY ( ` ID ` ) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8;
INSERT INTO USER ( NAME )VALUES( '孙悟空' ),( '猪八戒' );

2.查看表有没有被锁过

SHOW OPEN tables;

3.对表加锁

LOCK TABLE USER READ,USER WRITE;

4.对表进行解锁

unlock tables

表锁读锁

表锁中读写锁对操作和性能产生哪些影响
对user添加读锁 lock table user read;(共享锁)
当前连接
在这里插入图片描述
另一个连接

表锁写锁

对user添加写锁 lock table user write;(排它)
当前连接
在这里插入图片描述
另一个连接

表锁分析

SHOW status LIKE'table%';

Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数
Table_locks_waited:出现表级锁定争用而发生等待的次数
Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎
因为写锁后, 其它线程不能做任何操作,大量更新使用，查询很难得到锁, 从而造成永久阻塞
淘宝：买家（偏向读锁），卖家（偏向写锁）

行锁的概述和事物

偏向InnoDB存储引擎,开销大, 加锁慢, 会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高。
InnoDB与MyISAM的最大不同点:一是支持事务, 二是采用了行级锁。
什么是事务：事物是一批操作，要么同时成功，要么同时失败!
转账的操作

-- 创建数据表 
CREATE TABLE account ( 
ID INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR ( 10 ), 
balance DOUBLE 
);
-- 添加数据
INSERT INTO account ( NAME, balance )VALUES( 'Jack', 1000 ),( 'Rose', 1000 ); 
-- 模拟 Jack给Rose转 500元钱，一个转账的业务操作最少要执行下面的2条语句： 
-- Jack账号 - 500 
-- Rose账号 + 500 
-- 还原
UPDATE account SET balance = 1000;
-- jack转账200给rose
-- jack扣钱
UPDATE account SET balance = balance - 500 WHERE NAME = 'jack';
-- rose加钱
UPDATE account SET balance = balance + 500 WHERE NAME = 'rose';

假设当Jack账号上-500元,服务器崩溃了。Rose的账号并没有+500元，数据就出现问题了。我们需要保证其中一条 SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

手动提交事务

MYSQL中可以有两种方式进行事务的操作

手动提交事务
自动提交事务(5.5以后, 默认事务会自动提交)
手动提交事务的SQL语句

总结:如果事务中SQL语句没有问题，commit提交事务，会对数据库数据的数据进行改变。如果事务中SQL语句有问题，rollback回滚事务，会回退到开启事务时的状态。

取消自动提交

MySQL默认每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提
交事务，MySQL默认开始自动提交事务。
查看MySQL是否开启自动提交事务

SELECT@@autocommit;   
-- 只要是@@开头，表示mysql中的全局变量 SHOW VARIABLES LIKE '%commit%';

取消自动提交事务

-- MySQL数据库默认是自动提交。 
-- 能否让MySQL数据库永远是开启事物。永远需要手工提交呢？ 
SHOW VARIABLES LIKE '%commit%'; 
SELECT @@autocommit; --  1代表了自动开启了提交事物 
SET autocommit = 0;  --  1是自动提交事物，0是开启全局事物手动提交（开启事物）

事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)
在这里插入图片描述
事务的步骤

客户端连接数据库服务器，创建连接时创建此用户临时日志文件
开启事务以后，所有的操作都会先写入到临时日志文件中
所有的查询操作从表中查询，但会经过日志文件加工后才返回
如果事务提交commit则将日志文件中的数据写到表中，rollback否则清空日志文件。

事物回滚点

什么是回滚点
在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。
回滚点的操作语句
在这里插入图片描述
具体操作：

将数据还原到1000
开启事务
让Jack账号减3次钱，每次10块
设置回滚点：savepoint three_times;
让Jack账号减4次钱，每次10块
回到回滚点：rollback to three_times;

总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

事务的四大特性ACID

在这里插入图片描述

四种隔离级别和可能出现的问题

并发事务处理会带来一些问题，所以事物与事物之间需要进行适当的隔离，但是隔离是存在级别的，并不是隔离的越高级越好，隔离越高级，性能越差!
在这里插入图片描述
事物有四种隔离级别。

读未提交：出现脏读（一个事物读到了其他事物未提交的数据）
读已提交：出现不可重复读（一个事物多次读取到别人已提交的数据）
可重复读：出现幻读（一个事物的修改操作，发现新增加了修改的数据）
串行化: 读是共享锁，写是排他锁

四种隔离级别演示

事物的隔离级别（读未提交）：出现脏读

 REPEATABLE-READ：可重复读，是MySQL的默认隔离级别。  
 SHOW VARIABLES LIKE '%isolation%';  
 -- 或  
 SELECT @@tx_isolation;  
 -- 设置隔离级别成为：读未提交。出现脏读  
 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

问题：出现脏读，一个事物读取到了其他事物未提交的数据

事物的隔离级别（读已提交）：不会出现脏读，但是出现不可重复读

SHOW VARIABLES LIKE '%isolation%'; 
-- 或 SELECT @@tx_isolation;
-- 设置隔离级别成为：读可提交，不会出现脏读，但是会出现不可重复读。 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

可以解决脏读问题：不会读取到其他事物未提交的数据。但是会出现不可重复读。问题：出现不可重复读，一次事物中，多次读取到的数据不一样，读取到了别人已提交的数据。

事物的隔离级别(可重复读)：可重复读演示：不会出现不可重复读，会出现幻读

SHOW VARIABLES LIKE '%isolation%'; 
-- 或 
SELECT @@tx_isolation; 
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

可以解决的问题：不会出现不可重复读，一次事物多次读取到数据是一样的。
问题：出现了幻读。

事物的隔离级别(可串行化）：serializable可串行化。所有问题都不会出现

SHOW VARIABLES LIKE '%isolation%'; 
-- 或 
SELECT @@tx_isolation; 
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

行锁

行锁偏向InnoDB存储引擎,开销大, 加锁慢, 会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高。
InnoDB与MyISAM的最大不同点:一是支持事务, 二是采用了行级锁。
接下来演示一下MySQL默认行锁的使用。

执行更新操作（可重复读）
- 自己可以查看到更新的内容
- 连接2看不到更新的内容,只有commit后, 才能看到更新的内容
连接1执行更新操作,连接2也执行更新操作,更新同一条记录
- 连接1没有提交事务时, 连接2更新处于阻塞状态
- 当commit时, 连接2才会继续执行连接2更新也要commit
连接1和连接2同时更新数据,但更新的不是同一条记录
- 不会影响

锁定一行数据

如何在操作时锁定一行操作。

在查询之后添加for update，其它操作会被阻塞,直到锁定的行提交commit;

查看行锁的使用信息

SHOW status LIKE'innodb_row_lock%';

悲观锁,乐观锁

悲观锁
- 就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。
- 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制
- 事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁
乐观锁
- 顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁。
- 但是在更新的时候会去判断在此期间数据有没有被修改
- 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整
悲观锁,乐观锁使用前提
- 对于读操作远多于写操作的时候，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
- 如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险
乐观锁的实现方式
- 版本号
  - 就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段每次更新把这个字段加1
  - 读取数据的时候把version读出来，更新的时候比较version
  - 如果还是开始读取的version就可以更新了
  - 如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到
    一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
- 时间戳
  - 和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间
  - 同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间（timestamp）
  - 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比如果一致则OK，否则就是版本冲突

版本号方式示例
在这里插入图片描述

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL：锁问题

锁的概念和分类在现实生活中是为我们想要隐藏于外界所使用的一种工具,在计算机中，是协调多个进程或线程并发访问某一资源的一种机制,在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源,如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。锁对数据库而言显得尤为重要。在购买商品...
复制链接

扫一扫