mysql实物分批提交_Mysql - Innodb核心特性—事务

Innodb核心特性——事务

1.什么是事务

主要针对DML语句(update，delete，insert)一组数据操作执行步骤，这些步骤被视为一个工作单元:

1)用于对多个语句进行分组

2)可以在多个客户机并发访问同一个表中的数据时使用

所有步骤都成功或都失败

1)如果所有步骤正常，则执行

2)如果步骤出现错误或不完整，则取消

2.事务的通俗理解

伴随着“交易”出现的数据库概念。我们理解的“交易”是什么？

1)物与物的交换(古代)

2)货币现金与实物的交换(现代1)

3)虚拟货币与实物的交换(现代2)

4)虚拟货币与虚拟实物交换(现代3)

数据库中的“交易”是什么？

1)事务又是如何保证“交易”的“和谐”？

2)ACID

3.事务ACID特性

Atomic(原子性)

所有语句作为一个单元,要么全部成功执行或全部取消。

Consistent(一致性)

如果数据库在事务开始时处于一致状态，则在执行该事务结束时也是一致状态。
事务期间将保留一致状态。

Isolated(隔离性)

事务之间不会被其他事务影响。

Durable(持久性)

事务成功完成后，所做的所有更改都会准确地记录在
数据库中。所做的更改不会丢失。

4.事务流程举例

5.事务的控制语句如下:

START TRANSACTION(或 BEGIN)：显式开始一个新事务

SAVEPOINT：分配事务过程中的一个位置，以供将来引用(存档)

COMMIT：永久记录当前事务所做的更改

ROLLBACK：取消当前事务所做的更改

ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改 (读档)

RELEASE SAVEPOINT：删除 savepoint 标识符 (删档)

SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

一个成功事务的生命周期

begin;

sql1

sql2

sql3

...

commit;

一个失败事务的生命周期

begin;

sql1

sql2

sql3

...

rollback;

**结束事务的控制语句： commit 和 rollback **

注意：其实事务开启，不需要执行 begin 或者 start transaction ，只要执行DML 语句，自动开启事务。3.自动提交

#查看自动提交

mysql> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.01 sec)

#临时关闭自动提交

mysql> set autocommit=0;

mysql> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.01 sec)

#永久关闭自动提交(在配置文件修改，修改后重启mysqld)

[root@db01 world]# vim /etc/my.cnf

[mysqld]

autocommit=0

mysql> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

注意：自动提交关闭后，数据不会立即提交，还可以执行rollback回滚回来。4.事务演示

1)成功事务

mysql> create table stu(id int,name varchar(10),sex enum('f','m'),money int);

mysql> begin;

mysql> insert into stu(id,name,sex,money) values(1,'zhang3','m',100), (2,'zhang4','m',110);

mysql> commit;

#查看

mysql> select * from stu;

+------+--------+------+-------+

| id | name | sex | money |

+------+--------+------+-------+

| 1 | zhang3 | m | 100 |

| 2 | zhang4 | m | 110 |

+------+--------+------+-------+

2 rows in set (0.00 sec)

2)事务回滚

mysql> begin;

mysql> update stu set name='zhang3';

mysql> delete from stu;

mysql> rollback;

6.事务隐式提交情况

1)现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。

2)有些情况下事务会被隐式提交例如:在事务运行期间，手工执行begin的时候会自动提交上个事务

在事务运行期间，加入DDL、DCL操作会自动提交上个事务

在事务运行期间，执行锁定语句(lock tables、unlock tables)

load data infile

select for update

在autocommit=1的时候

示例：

# 执行了多少个成功的事务？ 7个

begin;

sql1

sql2

begin; #提交了一个

begin； #提交了一个

create database xxx; #提交了一个

update; #提交了一个

begin; #提交了一个

select * from xxx;

begin; #提交了一个

commit; #提交了一个

rollback; #算是一个失败的事务

7.事务日志redo基本功能

1)Redo是什么？

redo,顾名思义“重做日志”，是事务日志的一种。

2)作用是什么？

在事务ACID过程中，实现的是“D”持久化的作用。

特性:WAL(Write Ahead Log)日志优先写

REDO：记录的是，内存数据页的变化过程

3)REDO工作过程

#执行步骤

#查询

mysql> select * from course;

+-----+--------+-----+

| cno | cname | tno |

+-----+--------+-----+

| 1 | 英语 | 1 |

| 2 | 语文 | 2 |

| 3 | 数学 | 3 |

+-----+--------+-----+

3 rows in set (0.00 sec)

#执行步骤

mysql> update course set cno=6 where cno=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

#再次查询

mysql> select * from course;

+-----+--------+-----+

| cno | cname | tno |

+-----+--------+-----+

| 2 | 语文 | 2 |

| 3 | 数学 | 3 |

| 6 | 英语 | 1 |

+-----+--------+-----+

3 rows in set (0.00 sec)

1)首先将t1表中num=1的行所在数据页加载到内存中buffer page

2)MySQL实例在内存中将num=1的数据页改成num=2

3)num=1变成num=2的变化过程会记录到，redo内存区域，也就是redo buffer page中

#提交事务执行步骤

commit;

1)当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log

2)当写入成功之后，commit返回ok

8.redo数据实例恢复过程

恢复过程的几个步骤：1.首先将t1表中 id=1 的行所在数据页加载到内存中buffer page中

2.实时写入数据undo

3.修改过程加载到redo中

4.redo缓冲区的内容读入数据缓冲区

5.undo找事务日志有没有最后执行commit

6.没有commit, undo会回滚到原数据

注意：不同情况下的恢复结果1.还没有commit的时候，数据也没有定时写入，断电了

数据缓冲区和redo缓冲区都是原状态，undo找不到commit， 会回滚事务日志到原数据。结果：保持不变，不会修改。

2.还没有commit,数据定时写入了，断电了

数据缓冲区是原状态，redo缓冲区是修改后的状态， undo 找不到commit , 会回滚到事务日志到原数据。 结果： 保持不变，不会修改。

3.commit了，但是数据还没有写入到ibd文件中，断电了

数据缓冲区是原状态，redo缓冲区是修改后的状态， undo 找到commit , 会认为要修改数据。 结果：修改。

9.事务日志undo

1)undo是什么？

undo,顾名思义“回滚日志”，是事务日志的一种。

_2)作用是什么？

在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关

10.redo和undo的存储位置

#redo位置

[root@db01 data]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Mar 6 2017 ib_logfile1

#undo位置

[root@db01 data]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1

-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2

在MySQL5.6版本中undo是在ibdata文件中，在MySQL5.7版本会独立出来。

11.事务中的锁

1)什么是“锁”？

“锁”顾名思义就是锁定的意思。

2)“锁”的作用是什么？

在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。



排他锁：保证在多事务操作时，数据的一致性。

共享锁：保证在多事务工作期间，仍然允许被查询，数据查询时不会被阻塞。

乐观锁：谁先提交谁为准(例如：以前买火车票，提交订单，在指定时间还未付款时，票的信息还是谁都能看到，谁抢到算谁的)

悲观锁：谁先修改谁为准(例如：现在买火车票，提交订单，在指定时间还未付款时，票的信息别人看不到，自己优先。除非超过世间)

12.多版本并发控制(MVCC)

1)只阻塞修改类操作，不阻塞查询类操作(排它锁和共享锁)

2)乐观锁的机制(谁先提交谁为准)

13.锁的粒度

MyIsam：低并发锁(表级锁)，不支持事务。

Innodb：高并发锁(行级锁)，必须要有聚集索引才能做到行级锁

14.事务的隔离级别

四种隔离级别：

READ UNCOMMITTED(独立提交) RU级别

允许事务查看其他事务所进行的未提交更改

READ COMMITTED RC级别

允许事务查看其他事务所进行的已提交更改

REPEATABLE READ RR级别

确保每个事务的 SELECT 输出一致

InnoDB 的默认级别

SERIALIZABLE 串行化级别

将一个事务的结果与其他事务完全隔离

#查看隔离级别

mysql> show variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

#修改隔离级别为RU

vim /etc/my.cnf

[mysqld]

transaction_isolation=read-uncommit

mysql> use oldboy

mysql> select * from stu;

mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);

#修改隔离级别为RC

vim /etc/my.cnf

[mysqld]

transaction_isolation=read-commit

15.脏读 幻读 重复读 查询原因，和解决办法 (RR级别)

1.脏读

一个事务读到另一个事务，尚未提交的修改，就是脏读。这里所谓的修改，除了Update操作,还包括Insert和Delete操作。

脏读的后果：如果后一个事务回滚，那么它所做的修改，统统都会被撤销。前一个事务读到的数据，就是垃圾数据。

示例：例子：预订房间。

有一张Reservation表，往表中插入一条记录，来订购一个房间。

事务1：在Reservation表中插入一条记录，用于预订99号房间。

事务2：查询，尚未预定的房间列表，因为99号房间，已经被事务1预订。所以不在列表中。

事务1：信用卡付款。由于付款失败，导致整个事务回滚。

所以插入到Reservation 表中的记录并不置为持久(即它将被删除)。

现在99号房间则为可用。

所以，事务2所用的是一个无效的房间列表，因为99号房间，已经可用。如果它是最后一个没有被预定的房间，那么这将是一个严重的失误。

注：脏读的后果很严重。

2.不可重复读

在同一个事务中，再次读取数据时【就是你的select操作】，所读取的数据，和第1次读取的数据，不一样了。就是不可重复读。

示例：举个例子：

事务1：查询99号房间是否为双人床房间。结果99号是。

事务2：将99号房间，改成单人床房间。

事务1：再次执行查询，99号房间不是双人房了。也就是说， 事务1，可以看到其他事务所做的修改。

在不可重复读，里面，可以看到其他事务所做的修改，而导致2次的查询结果不再一样了。

这里的修改，是提交过的。也可以是没有提交的，这种情况同时也是脏读。

如果，数据库系统的隔离级别。允许，不可重复读。那么你启动一个事务，并做一个select查询操作。

查询到的数据，就有可能，和你第2次，3次...n次，查询到的数据不一样。一般情况下，你只会做一次，select

查询，并以这一次的查询数据，作为后续计算的基础。因为允许出现，不可重复读。那么任何

时候，查询到的数据，都有可能被其他事务更新，查询的结果将是不确定的。

注：如果允许，不可重复读，你的查询结果，将是不确定的。一个不确定的结果，你能容忍吗？

3.幻读

事务1读取指定的where子句所返回的一些行。然后，事务2插入一个新行，这个新行也满足事务1使用的查询

where子句。然后事务1再次使用相同的查询读取行，但是现在它看到了事务2刚插入的行。这个行被称为幻象，

因为对事务1来说，这一行的出现是不可思议的。

示例：事务1：请求没有预定的，双人床房间列表。99号在其中。

事务2：向Reservation表中插入一个新纪录，以预订99号房间，并提交。

事务1：再次请求有双人床的未预定的房间列表，99号房间，不再位于列表中。

注：幻读，针对的是，Insert操作。如果事务2，插入的记录，没有提交。那么同时也是脏读。

以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家

或 者关注我每天分享技术文章PHP架构师之路​www.zhihu.com

来源：https://www.cnblogs.com/gongjingyun123--/p/11832424.html