开发者社区> azlr2a666ct4i> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL-数据库(5)

简介: 本文主要讲述了MySQL的并发控制与事务的隔离级别。
+关注继续查看

本文主要讲述了MySQL的并发控制与事务的隔离级别。

3.5并发控制与事务的隔离级别

3.5.1事务

3.5.1.1事务基础知识

事务管理的意义:保证数据操作的完整性

事务的特性(ACID):

  • 原子性:事务的整个操作是一个整体,不可以分割,要么全部成功,要么全部失败;
  • 一致性:事务操作的前后,数据表中的数据没有变化(按示例中解释为A转钱给B的前后两个人钱的总金额不会改变);
  • 隔离性:事务操作是相互隔离不受影响的;
  • 持久性:数据一旦提交,不可改变,永久的改变数据表数据。

在 MySQL 命令行的默认下,事务采用自动提交(autocommit=1)模式。意味着,当你执行一个修改sql语句,MySQL 会立刻更新存储到磁盘中。也就是会立马执行commit操作。
因此开启一个手动事务必须使用begin,start transaction或者set autocommit=0:

事务回滚,即在事务运行的过程中发生了某种故障,系统将事务中对数据库的所有已完成的操作(对数据库的更新操作)全部撤销,回滚到事务开始时的状态。(这条语句也标志着事务的结束)

start TRANSACTION;  # 开启事务

commit; # 提交事务

rollback; # 事务回滚

image.png
自定义异常

#定义一个err变量并初始值为0  
declare err int default 0; 

#当有sql异常的时候,sql继续往下执行,并将变量err的值改变为1   
declare continue handler for sqlexception set err =1; 
(事务是并发控制的基本单位
隔离级别越高,一致性程度越高,并发度越低。

3.5.1.2事务隔离

数据的不一致性包括

  • 丢失修改(lost update)
  • 读脏数据(dirty read)
  • 不可重复读(non-repeatable read)
  • 幻读(phantom read)

MySQL的事务隔离级别从低到高分以下四级:

  • 读未提交(READ UNCOMITTED)
  • 读已提交(READ COMMITTED)
  • 可重复读(REPEATABLE READ)
  • 可串行化(SERIALIZABLE)
隔离级别读脏不可重复读幻读
READ UNCOMITTED
READ COMMITTED×
REPEATABLE READ××
SERIALIZABLE×××
上表说明,最低的隔离级别不能避免读脏、不可重复读和幻读,而最高的隔离级别,可保证多个并发事务的任何调度,都不会产生数据的不一致性,但其代价是并发度最低。
# 查询MySQL的事务隔离级别 
select @@GLOBAL.transaction_isolation, @@transaction_isolation; # 前者为全局变量,后者为本会话期内的变量

# 设置事务的隔离级别
set session transaction isolation level read uncommitted;
# REPEATABLE-READ(缺省的事务隔离级别)

# 使得事务暂时休眠(单位:秒)
set @n = sleep(5);
说明:MySQL8的事务隔离级别变量名与之前的版本是不同的,请不要参考的旧版本。

3.5.1.3事务故障和恢复

create procedure pro_transfer(in id_from int, in id_to int, in money int)
modifies sql data
begin
    -- 捕获异常,进行事务回滚
    declare exit handler for sqlexception rollback;
    start transaction;
    update tb_account set balance=balance+money where id=id_to;
    update tb_account set balance=balance-money where id=id_from;
    commit;
end

恢复策略:

基于日志恢复:
image.png
image.png

基于检查点的恢复方法:
image.png
checkpoint之前commit不用管
checkpoint之后:
提交了(commit) -> redo (只要在事故发生前结束了事务,恢复之后的数据应该是redo之后的数据)
没有提交 -> undo(撤销操作,恢复数据之后应该是一开始的数据)

rollback也算事务提交的一种的方式。

日志记录格式

  • 表示事务对数据元素A执行了更新操作。 更新前的值
  • 表示事务已经开始。此时DBMS已经完成对事务的初始化工作,如分配事务工作区等
  • 表示事务已经提交。(该事务对数据库的修改必须永久化)事务提交时其更新的数据都写到了数据缓冲区中,但是由于不能控制缓冲区管理器何时将缓冲块从内存写到磁盘,因此看到该日志记录时,通常不能确定更新是否写到了磁盘上。
  • 表示事务已经中止,即执行失败。此时,若所做的更新已经反映到磁盘上,DBMS必须通过undo来消除对磁盘数据库得到影响。

3.5.2并发控制

3.5.2.1脏读

产生读脏的原因,是事务t1读取数据时,修改该数据的事务t2还没有结束(commit或rollback,统称uncommitted),且t1读取的时间点又恰在t2修改该数据之后。【读了未提交的数据】
image.png

3.5.2.2不可重复读

不可重复读(unrepeatable read),是指一个事务(t1)读取到某数据后,另一个事务(t2)修改(update)了该数据,事务t1并未修改该数据,但当t1再次读取该数据时,发现两次读取的结果不一样。【读两次的结果不同】
image.png

3.5.2.3幻读

幻读是指一个事务(t1)读取到某数据后,另一个事务(t2)作了**insert****delete**操作,事务t1再次读取该数据时,魔幻般地发现数据变多了或者变少了(记录数量不一致);
而不可重复读指事务t2作了**update**操作,致使t1的两次读操作读到的结果(数据的值)不一致。

下图为幻读,查询得到的数据多了一条,是由于事务Binsert了新的数据。
image.png

3.5.2.4丢失更新

如果两事务都在对数据进行修改则会导致另外的问题:丢失更新。【自己的更新被别人覆盖】
image.png

丢失更新使用 行锁 来解决

3.5.3锁

3.5.3.1锁类别

锁的主要作用是管理共享资源的并发访问,锁可以用于实现事务的隔离。
为什么加锁?为了避免多个事务同时操作数据库导致数据异常,一般会通过锁机制解决。
image.png

MySQL的select语句支持for sharefor update短语,分别表示对表加共享(Share)锁和写(write)锁,共享锁也叫读锁写锁又叫排它锁(exclusive locks)

不管share还是update锁,都是在事务结束时才释放。

表锁:偏向MyISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发送锁冲突的概率最高,并发度最低。

3.5.3.2封锁协议

X锁 S锁 一致性保证
操作结束释放事务结束释放操作结束释放事务结束释放不丢失修改不读脏数据可重复读
一级封锁协议
   |   ✔ |  |  |   ✔ |  |  |

| 二级封锁协议 | | ✔ | ✔ | | ✔ | ✔ | |
| 三级封锁协议 | | ✔ | | ✔ | ✔ | ✔ | ✔ |

  • 一级封锁协议:修改数据加x锁直到事务结束才释放。在此协议中,仅仅是读数据是不需要加锁的,所以只能解决丢失修改问题,不能解决脏读和不可重复读。
  • 二级封锁协议:在一级封锁的基础上,加了一条:T事务在读取数据R之前必须先对其加上S锁读完释放S锁。可以解决丢失修改和脏读(加了读锁就可以防止在读的期间其他事务进行修改,但是读完之后,事务结束之前,依然可能会其他事务进行修改,导致不可重复读)。
  • 三级封锁协议:一级封锁协议加上事务T在读取数据R之前必须先对其加S锁,直到事务结束才释放。:解决了丢失修改、脏读和不可重复读的问题。

image.pngimage.png

3.5.3.3加锁

# 加共享锁
select * from t1 for share;
select ... lock in share mode;  

# t1加共享锁  t2加update锁【排他锁】
select * from t1,t2 for share of t1 for update of t2;

# 读锁
lock table 表名 read;

# 解锁
unlock tables;

3.5.4串行化

多个事务并发执行是正确的,当且仅当其结果与按某一次序串行地执行这些事务时的结果相同。两个事务t1,t2并发执行,如果结果与t1→t2串行执行的结果相同,或者与t2→t1串行执行的结果相同,都是正确的(可串行化的)。

serializable这种隔离级别会大大降低并发度,在实践中极小使用。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL-数据库(2)
本文主要讲述了MySQL的基础的CRUD,以及视图和索引。
0 0
MySQL-数据库(4)
本文主要讲述了MySQL的恢复、日志、备份以及关于用户的授权操作。
0 0
初识MySQL数据库
什么是数据库?MySQL是什么?SQL是什么?
0 0
MySQL 数据库简单介绍
一般来说,数据库被分为关系型数据库(RDS)和非关系型数据库(NoSQL),其中,关系型数据库采用关系模型来组织数据,主要的代表有 MySQL、SQL Server、Oracle、PostgreSQL等。非关系型数据库的数据模型是非关系型的、分布式的,且一般不保证 ACID 的事务性,主要代表有 MongoDB、Redis、Memcached等。今天,我们就来简单介绍一下关系型数据库代表 MySQL 数据库。
0 0
《零基础》MySQL 选择数据库(七)
从命令提示窗口中选择MySQL数据库 在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。 实例
0 0
MySQL 选择数据库
MySQL 选择数据库 在你连接到 MySQL 数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库。 从命令提示窗口中选择MySQL数据库 在 mysql> 提示窗口中可以很简单的选择特定的数据库。
688 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
好的 MySQL 兼容可以做到什么程度
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
MySQL 5.7让优化更轻松
立即下载