Innodb核心特性——事务
Innodb核心特性——事务
一、什么是事务？
主要针对DML语句（update，delete，insert）
一组数据操作执行步骤，这些步骤被视为一个工作单元:
1）用于对多个语句进行分组
2）可以在多个客户机并发访问同一个表中的数据时使用
所有步骤都成功或都失败
1）如果所有步骤正常，则执行
2）如果步骤出现错误或不完整，则取消
二、事务通俗的理解
伴随着“交易”出现的数据库概念。
我们理解的“交易”是什么？
1）物与物的交换（古代）
2）货币现金与实物的交换（现代1）
3）虚拟货币与实物的交换（现代2）
4）虚拟货币与虚拟实物交换（现代3）
数据库中的“交易”是什么？
1）事务又是如何保证“交易”的“和谐”？
2）ACID
# 10.0.0.51的第一个窗口
# 1.创建一个转账表
mysql> use test
mysql> create table jiaoyi;
mysql> insert into jiaoyi values('msy',1000),
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1000 |
| hjm | 1000 |
+------+-------+
# 2.开始事务
mysql> begin;
# 3.更改数据（测试手动更改 其实都是自动的）
mysql> update jiaoyi set money=1500 where name='msy';
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1500 |
| hjm | 1000 |
+------+-------+
mysql> update jiaoyi set money=500 where name='hjm';
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1500 |
| hjm | 500 |
+------+-------+
# 4.在10.0.0.51的另一个窗口查看
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1000 |
| hjm | 1000 |
+------+-------+
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1000 |
| hjm | 1000 |
+------+-------+
并没有发生变化
# 5.回刚才begin的第一个窗口
mysql> commit;
# 6.在10.0.0.51的另一个窗口查看
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1500 |
| hjm | 500 |
+------+-------+
数据变化
mysql> update jiaoyi set money=2500 where name='msy';
mysql> update jiaoyi set money=-500 where name='hjm';
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2500 |
| hjm | -500 |
+------+-------+
#错误行为 正常情况下是不允许操作的 money会加上无符号 现在只能手动演示 哈哈
#如果不成功就会回滚
mysql> rollback;
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 1500 |
| hjm | 500 |
+------+-------+
`注意：当你执行完commit之后 是不能回滚到的
三、事务ACID特性
A：原子性，将一个事务视为一个单元，要么全部成功（commit），要么全部失败（rollback）
C：一致性，在事务执行前后，数据保持一致
I：隔离性（隔离级别），事务和事务之间没有任何影响，完全隔离
D：持久性，事务执行结束后，commit的那一刻，写入磁盘，永久保存。
只要commit之后 就没办法回滚了 相当于执行commit就是把数据写入磁盘了 不执行commit相当于在内存中写
四、事务流程举例
五、事务控制语句
START TRANSACTION（或 BEGIN） #显式开始一个新事务
SAVEPOINT #分配事务过程中的一个位置，以供将来引用 像打游戏存档
COMMIT #永久记录当前事务所做的更改
ROLLBACK #取消当前事务所做的更改
ROLLBACK TO SAVEPOINT #取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT #删除 savepoint 标识符
SET AUTOCOMMIT #为当前连接禁用或启用默认 autocommit 模式
mysql> update jiaoyi set money=3000 where name='msy';
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 3000 |
| hjm | 1500 |
+------+-------+
#存档
mysql> savepoint diyici;
# 不小心执行错了 如果roollback 上一笔hjm的操作也没了
mysql> update jiaoyi set money=2000 where name='hjm';
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 3000 |
| hjm | 2000 |
+------+-------+
#这个时候就可以用回滚到存档
mysql> rollback to savepoint diyici;
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 3000 |
| hjm | 1500 |
+------+-------+
注意：要关闭自动提交 不然每次触发就会自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
#临时修改 退出会话就没了
mysql> set autocommit=0;
#永久关闭
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0
六、事务的生命周期
一个成功事务的生命周期
begin;
sql1
sql2
sql3
...
commit;
一个失败事务的生命周期
begin;
sql1
sql2
sql3
...
rollback;
七、事务的隐式提交
在MySQL5.6，不需要手动执行begin；只要执行DML语句，会自动开启一个事务
update、insert、delete
神奇的事-1
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
+------+-------+
mysql> insert into jiaoyi values('jjj',0);
mysql> rollback;
mysql> select * from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 0 |
+------+-------+
#按理说在没有执行commit之前 是不会提交的 但是做一次回滚回到提交之后的数据发现有刚插入的
#这就是触发DML语句自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
[root@db01 data]# vim /etc/my.cnf
autocommit=0
[root@db01 data]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS'
神奇的事-2
# 10.0.0.51窗口1执行但不提交
mysql> select *from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 500 |
+------+-------+
mysql> update jiaoyi set money=600 where name='jjj';
mysql> select *from jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 600 |
+------+-------+
# 10.0.0.51窗口2查看数据 无变化
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 500 |
+------+-------+
# 10.0.0.51窗口1提交
mysql> commit;
#10.0.0.51窗口2查看数据
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 500 |
+------+-------+
为什么还是无变化？？？
这不应该的啊！！！！!
退出当前会话重新进之后就变化了
mysql> \q
[root@db01 ~]# mysql -S /tmp/mysql.sock
mysql> select * from test.jiaoyi;
+------+-------+
| name | money |
+------+-------+
| msy | 2000 |
| hjm | 1500 |
| jjj | 600 |
+------+-------+
面试题
开启了几个事务 完成了几个事务？
开启三个 完成三个
#这是一个
begin;
update
create database
#这是第二个
update
select
insert
#这是第三个
begin;
update
commit;
1.在事务执行期间，如果执行begin或者 start transaction会自动提交上一个事务，并且开启新的事务
2.在事务执行期间，如果运行了DDL和DCL都会自动提交事务 就是非DML和DQL
3.在事务执行期间，执行lock table 或者 unlock table 也会自动提交事务
4.导出数据 load data infile ，会自动提交事务
5.在事务执行期间，执行select for update，会自动提交事务
6.在autocommit=1的时候，会自动提交事务
八、事务日志
1.redu
1.Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。
2.作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。
3.redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog
4.redo的刷新策略
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
5.REDO工作过程
#执行步骤
update t1 set num=2 where num=1;
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
特性:WAL(Write Ahead Log)日志优先写
REDO：记录的是，内存数据页的变化过程
2.undo
1.undo是什么？
undo,顾名思义“回滚日志”，是事务日志的一种。
2.作用是什么？
在事务ACID过程中，实现的是“A”原子性的作用。
当然CI的特性也和undo有关
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
3.MySQL CSR——前滚
MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,
在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"
3.redo和undo的存储位置
在MySQL5.6版本中undo是在ibdata文件中，在MySQL5.7版本会独立出来。
5.6也可以 只不过要重新编译
#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
4.一些概念
redo log ---> 重做日志 ib_logfile0~1 50M , 轮询使用
redo log buffer ---> redo内存区域
ibd ---> 存储 数据行和索引
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.
九、事务中的锁
锁一般配合隔离级别使用
1.什么是“锁”？
“锁”顾名思义就是锁定的意思。
2.“锁”的作用是什么？
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
3.MySQL中的锁
排他锁 ：保证在多事务操作时，数据的一致性。(修改操作会被阻塞)
共享锁 ：保证在多事务工作期间，数据查询 时不会被阻塞。
乐观锁 ：谁先提交，以谁为准
悲观锁 ：谁先执行事务，并阻塞其他事务查询
4.锁的粒度
innodb：行级锁
myisam：表级锁
5.锁的实践
1) 同一列的情况下：如果两个事务修改同一行 一定是谁先提交以谁为准
# 1.创建库与表 对数据进行修改 但不提交
mysql> create table suo(id int);
mysql> insert into suo values(1),(2),(3);
mysql> select *from suo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> update test.suo set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
# 2.另个窗口也对数据做修改 但是失败 因为这个数据正在被修改 被锁住了
mysql> select * from suo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> update test.suo set id=3 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
# 3.第一个窗口提交
mysql > commit;
# 4.第二个窗口修改成功 但是数据没有变化 因为第一个修改好了就没有id=1了 数据变了 所以结果是第一个提交后的
mysql> update test.suo set id=3 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from suo;
+------+
| id |
+------+
| 2 |
| 2 |
| 3 |
+------+
2) 想行级锁 必须要有主键
# 1.修改数据 先不提交
mysql> update suo set id=10 where id=3;
Query OK, 1 row affected (0.00 sec)
# 2.另一个窗口不同行做修改
#因为行级锁同行不能同时修改 但是不同行就可以
mysql> update test.suo set id=20 where id=2; 卡住
# 3.第一个窗口提交
mysql> commit;
mysql> select * from suo;
+------+
| id |
+------+
| 2 |
| 2 |
| 10 |
+------+
#第二个窗口执行 还是不行
# 1.创建一个新表 带主键
mysql> create table suo_new(id int not null primary key auto_increment);
mysql> insert into suo_new values(1),(2),(3);
mysql> commit;
mysql> select * from suo_new;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
# 2.第一个窗口修改一行数据 先不提交
mysql> update suo_new set id=10 where id=1;
# 3.另一个窗口修改另一行数据 先不提交 但是不卡了 执行成功了
mysql> update test.suo_new set id=20 where id=2;
# 4.提交以后
mysql> select * from suo_new;
+----+
| id |
+----+
| 3 |
| 10 |
| 20 |
+----+
3) 不同列的情况下：就不是谁先提交看谁
mysql> alter table test.suo_new add name varchar(10);
mysql> update test.suo_new set name='qls' where id=3;
mysql> select * from suo_new;
+----+------+
| id | name |
+----+------+
| 3 | qls |
| 10 | NULL |
| 20 | NULL |
+----+------+
# 另一个窗口
mysql> update test.suo_new set name='zhangsan' where id=10;
mysql> select * from test.suo_new;
+----+----------+
| id | name |
+----+----------+
| 3 | NULL |
| 10 | zhangsan |
| 20 | NULL |
+----+----------+
两个事务互不影响
# 1.第一个窗口修改
mysql> update test.suo_new set id=100 where name='qls';
# 2.另一个窗口修改 不成功
mysql> update test.suo_new set id=200 where name='qls'; 卡住
# 3.第一个窗口提交
mysql> commit;
# 4.第二个窗口就执行成功了
然后commit
最后结果是
mysql> select * from test.suo_new;
+-----+----------+
| id | name |
+-----+----------+
| 10 | zhangsan |
| 20 | NULL |
| 200 | qls |
+-----+----------+
十、MVCC多版本并发控制
1）只阻塞修改类操作（排他锁），不阻塞查询类操作（共享锁）
2）乐观锁的机制（谁先提交谁为准）
十一、隔离级别
1.查看当前隔离级别
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
2.read-commit（RC）
提交读
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=read-commit
# 1.第一个窗口修改数据
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | qls |
+-----+--------+
mysql> begin;
mysql> update test.suo_new set name='wang5' where id=200;
mysql> commit;
# 2.提交时另一个窗口查看
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | wang5 |
+-----+--------+
3.read-uncommit（RU）
未提交读
安全性低 容易出现幻读 脏读
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=read-uncommit
# 1.第一个窗口修改但不提交
mysql> begin;
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | qls |
+-----+--------+
mysql> update test.suo_new set name='wang5' where id=200;
# 2.未提交时另一个窗口查看
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | wang5 |
+-----+--------+
4.REPEATABLE-READ（RR）
重复读
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=REPEATABLE-READ
# 1.第一个窗口修改并提交
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | wang5 |
+-----+--------+
mysql> begin;
mysql> update test.suo_new set name='zhao4' where id=30;
mysql> commit;
# 2.第二个窗口必须先退出一下会话 再重新进入 才可查看
5.串行化
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=SERIALIZABLE
#阻塞所有操作，查询的时候，不能修改，修改的时候，不能查询
除非提交 其他都阻塞
十二、架构改造项目
项目背景:
2台 IBM X3650 32G ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全)
MySQL 5.1.77 默认存储引擎 MyISAM
数据量: 60G左右 ,每周全备,没有开二进制日志
架构方案:
1. 升级数据库版本到5.7.20
2. 更新所有业务表的存储引擎为InnoDB
3. 重新设计备份策略为热备份,每天全备,并备份日志
4. 重新构建主从
结果:
1.性能
2.安全方面
3.快速故障处理
浙公网安备 33010602011771号