文档章节

网易云课堂DBA学习笔记 (三) 事务与储存引擎

HeyS1
 HeyS1
发布于 05/18 18:20
字数 2302
阅读 5
收藏 0

事务

概念说明:

  • DDL语句:特指修改表结构的语句,如增删表字段等
  • DML语句:特指增删改查等sql语句

基本知识

事务的基本属性

原子性 原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚
一致性

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态

拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。

隔离性 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
持久性 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库隔离现象

脏读 事务B读取到事务A尚为提交的数据变更
不可重复读 事务B前后两次读取一条记录之间,该记录被事务A修改并提交,导致事务B读到了不一样的结果
幻读    

事务B按条件匹配到n条记录并修改,但由于修改过程中事务A新插入符合条件的记录,导致事务B更新完成后仍发现有符合条件的数据未被更新。

幻读与不可重复读在理解上差不多,区别是一个是修改数据一个是新增数据

数据库隔离级别

数据库不同的隔离级别对应不同的隔离现象

隔离识别 脏读 不可重复读 幻读

未提交读(Read uncommitted)

可能 可能 可能

已提交读(Read committed)

不可能 可能 可能

可重复读(Repeatable read)

不可能 不可能 可能

可串行化(Serializable)

不可能 不可能 不可能

特别注意的是:

  1. mysql默认的是可重复读,但Mysql对InnoDB有特殊处理,消灭了幻读。
  2. 未提交读会出现脏读(在实际环境中,都应该避免脏读),可串行化会影响性能。所以在选择隔离级别时一般选择已提交读(oracle默认)可重复读(mysql默认)

 

特别说明

1.mysql的事务默认是自动提交的,即每条DML语句执行后都会持久化到磁盘

-- 自动提交参数是默认开启的
-- 该参数可配置全局级别,也可配置session级别
mysql> SHOW GLOBAL VARIABLES LIKE '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set




-- 全局级别
SET GLOBAL SESSION autocommit = 0;
SHOW GLOBAL VARIABLES LIKE '%autocommit%';

-- session级别
SET SESSION autocommit = 0;
SHOW VARIABLES LIKE '%autocommit%';

2.DLL语句是不能回滚的

-- 1.
BEGIN;

ALTER TABLE xxx.xxx ADD COLUMN `name` varchar(50) NULL ;

ROLLBACK; -- 无法回滚


-- 2. 值得注意的是:
BEGIN;

INSERT ...;
UPDATE ...;

ALTER TABLE xxx.xxx ADD COLUMN `name` varchar(50) NULL ;
ROLLBACK;-- 受DLL语句影响,上面的增删改DML语句同样无法回滚

InnoDB事务锁

共享锁与排他锁

InnoDB实现了以下两种类型的行锁。

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。

如何加锁呢?首先一般的select语句是不会加锁,也不会被其他事务锁阻塞的。但执行insert/update/delete前数据库会自动加锁,具体如下

  共享锁 排他锁
自动加锁 insert前 update或delete
手动加锁 SELECT * FROM xxx LOCK IN SHARE MODE; SELECT * FROM xxx FOR UPDATE;

 

下面是这两个锁的兼容矩阵

当前锁模式/是否兼容/请求锁模式 排他锁 共享锁
排他锁(x锁) × ×
共享锁(s锁) ×

解读: 

  1. 某数据被施加x锁后,在释放x锁前,该数据不能再被施加x锁和s锁
  2. 某数据被施加s锁后,在释放s锁前,该数据不能被施加x锁,但可以再施加s锁

实践:

共享锁例子

事务A 事务B

begin;

 

-- 添加共享锁

SELECT * FROM admin LOCK IN SHARE MODE;

begin;
...

-- 这里可以添加共享锁,且可以查询到数据

SELECT * FROM admin LOCK IN SHARE MODE;

-- 更新数据会被阻塞

update admin set xx=xxx;

 

commit;

 

 

--等待A提交或回滚后,B才能提交

commit;

 

排他锁例子

事务A 事务B

begin;

 

-- 添加排他锁

SELECT * FROM admin for UPDATE;

begin;
...

-- 这里加共享或排他锁都会被阻塞。

SELECT * FROM admin for UPDATE;

-- 当然了,普通的select语句是可以查到数据的,不会被阻塞

 

commit;

 

 

--等待A提交或回滚后,B才能提交

commit;

上面例子中,B事务会被A事务的事务锁阻塞。等待n秒后会抛出异常。这个秒是可以动态配置的

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set

 

死锁

当两个链接互相等待对方释放的锁,就会造成死锁,如图

如何避免死锁

 

锁的细粒度

 

 

 

 

存储引擎

mysql主要有以下存储引擎,InnoDB会重点介绍。其他都是不常用的

MyISAM

MEMORY

黑洞存储引擎

TokuDB

InnoDB

-- innodb相关参数

SHOW GLOBAL VARIABLES LIKE '%innodb%'

体系架构与文件结构

MYISAM储存文件的结构是堆表,就是说合插入的顺序是一样的,与数据本身的逻辑没有关系。

InnoDB存储文件的结构是索引组织表(聚簇表),就是存放数据的顺序是根据主键对应的逻辑储存的。如图

因此,

1.根据主键查询数据的效率会更好

2.自增的主键的插入效率较好,而随机主键插入的效率较低

3.InnoDB必须指定主键(设计的时候不声明主键,Mysql会自动分配一个字符串类型的主键)。建议自增

InnoDB缓存池

在InnoDB中所有读写操作都必须经过缓存,如图

用户读取某数据,并非直接读取磁盘里面的数据,而是先查询缓冲池里面是否有该数据,没有就去磁盘取,并储存到缓存池中。写操作也一样。而缓存策略是LRU,也就是最近最少使用次数,不被经常使用的数据会优先替换出缓缓存池

缓存池调优:

-- 查看缓存池容量。假设服务器内存是16G且只开启了一个mysql实例,那将innodb_buffer_pool_size设置成10G 是比较合理的
SHOW GLOBAL VARIABLES LIKE '%innodb_buffer_pool_size%';

数据持久化与事务日期

刚刚讲到,读写操作是必须经过缓存池的,而写操作实际上是对缓存池里面的数据进行修改。

那mysql是怎么样把数据持久化到磁盘呢。

持久化步骤:

1.用户发起修改数据,mysql首先会修改缓存池里面的数据,并实时把该操作记录在redo log文件中

2.然后直接告诉用户数据修改成功了,然后Mysql会慢慢的异步把redo log文件中的操作持久化到磁盘

这样做的好处是把操作记录 记录在redo log文件效率是很高的。

但是这样会有问题就是万一数据还在redo log文件中,还没持久化到磁盘的情况下,mysql宕机。

那数据是不是就丢失了呢,答案是不会。重启mysql的时候,mysql会自动处理的redo log里面未处理的记录

InnoDB持久化相关参数:

SHOW GLOBAL VARIABLES LIKE '%innodb_flush_log_at_trx_commit%'

这个参数有3种值

说明 安全性与性能
0 每隔1s写入并持久化一次日志 有可能丢失最多1s的事务修改记录
1 每次commit都持久化日志 所有数据都是安全的,一条都不会丢,频繁写操作会对影响新能
2 每次提交都持久化到系统的内容中,且每1s持久化一次日志 这和0最大区别是,当mysql宕机而服务器主机没有宕机,数据是不会丢的。但是主机宕机,如断电,那效果和设为0是一样的

 

© 著作权归作者所有

共有 人打赏支持
HeyS1
粉丝 12
博文 49
码字总数 29535
作品 1
广州
程序员
私信 提问
《deeplearning.ai》 课程一第四周 | Deep neural networks

deeplearning.ai 是机器学习领域大牛Andrew Ng在Coursera上公布的新的深度学习的课程,相比之前机器学习的课程,本课程更偏重于深度学习的领域。 本文是课程一《Neural Networks and Deep Le...

V怪兽
01/08
0
0
吴恩达机器学习练习1:线性回归

吴恩达机器学习课程:网易云课堂 此文转载于:黄海广博士,haiguang2000@qq.com,感谢吴恩达先生、黄海广博士及所有奋斗在AI一线的前辈们,努力学习,自此刻始。 注意:此文只是方便本人学习...

yanwucao
05/13
0
0
《deeplearning.ai》 课程一第二周 | Logistic Regression

deeplearning.ai 是机器学习领域大牛Andrew Ng在Coursera上公布的新的深度学习的课程,相比之前机器学习的课程,本课程更偏重于深度学习的领域。 本文是课程一《Neural Networks and Deep Le...

V怪兽
2017/12/14
0
0
网易云课堂推了个“行家计划”,要2年培育100位收入超500万的知识行家

12月20日,网易云课堂在北京开了“2017年内容伙伴大会”,除了媒体外,还邀请了众多入驻平台的教育机构和名师到场,大会现场,网易云课堂发布精品化战略,启动IP培育“行家计划”。2017年12月...

小芹菜
2017/12/23
0
0
Go语言学习资料整理

整理网上找到的Golang语言学习资料 基础 基础教程 书籍在线版 Go 指南-A Tour of Go Go语言圣经(中文版) Effective Go中文版 Go Web编程 build-web-application-with-golang Go入门指南 Th...

nothingfinal
2017/11/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

MYSQL事务处理

INNODB 事务里,所有表引擎必须为INNODB,在非实务表上操作,不会警告,不会报错,但没有效果

关元
44分钟前
3
0
cmake 编译脚本

#!/bin/sh test -d build || mkdir -p build cd build cmake .. make

shzwork
今天
2
0
从零开始实现Vue简单的Toast插件

概述: 在前端项目中,有时会需要通知、提示一些信息给用户,尤其是在后台系统中,操作的正确与否,都需要给与用户一些信息。 1. 实例 在Vue组件的methods内,调用如下代码 `this``.$toast({...

前端小攻略
今天
1
0
yaf和yaconf

pecl install yafpecl install yaconf [yaf] yaf.environ = dev yaf.use_spl_autoload = On yaf.use_namespace = 1 [yaconf] yaconf.directory = /data/wwwroot/yaconf......

果树啊
今天
0
0
day01:shell基础(shell基础、alias及重定向)

1、shell基础介绍: shell是一个命令解释器,用于用户与机器的交互: 也支持特定的语法(逻辑判断,循环): 每个用户都有自己特定的shell:Centos7的shell是bash(bourne agin shell): shel...

芬野de博客
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部