InnoDB 事务详解 Linux运维

1.介绍

事务：Transaction （交易）。 伴随着交易类的业务出现的概念（工作模式）
交易？
物换物，等价交换。
货币换物，等价交换。
虚拟货币换物（虚拟物品），等价交换。
现实生活中怎么保证交易“和谐” ，法律、道德等规则约束。
数据库中为了保证线上交易的“和谐”，加入了“事务”工作机制。

2.事务控制语句

#1.标准(显示)的事务控制语句
#开启事务
begin; start transaction;
#提交事务
commit;
# 回滚事务
rollback;
注意：
事务生命周期中，只能使用DML语句（select、update、delete、insert）

#2.事务的生命周期演示：
mysql> use world
mysql> begin;
mysql> delete from city where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> commit;

#3.隐式提交：
begin
a
b
begin
SET AUTOCOMMIT = 1
导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

#4.隐式回滚
会话窗口被关闭。
数据库关闭 。
出现事务冲突（死锁）。

3.事务的ACID

A: 原子性
不可再分性：一个事务生命周期中的DML语句，要么全成功要么全失败，不可以出现中间状态。
主要通过：undo保证的。

C：一致性
事务发生前，中，后，数据都最终保持一致。
CR + DWB

I：隔离性
事务操作数据行的时候，不会受到其他事务的影响。
读写隔离： 隔离级别、MVCC
写写隔离： 锁、隔离级别

D: 持久性
一但事务提交，永久生效（落盘）。
redo保证 ckpt。

4. 隔离级别

#1.作用
实现事务工作期间的“读”的隔离
读？ ----》 数据页（记录）的读

#2.级别类型（默认级别）
mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |     #称之为RR级别
+-----------------------+-----------------+
1 row in set (0.00 sec)

# RU ： READ-UNCOMMITTED 读未提交
可以读取到事务未提交的数据。隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题
#########################################
先将隔离级别设置为RU
mysql> set global transaction_isolation='READ-UNCOMMITTED'; 退出数据库重新连接数据库
#A事务：
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update city set population='1000' where id =1;
Query OK, 1 row affected (0.00 sec)

#B事务：
mysql> begin;
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |       1000 |     #查到未提交数据
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
##########################################################################

# RC ： READ-COMMITTED 读已提交（可以用）：
可以读取到事务已提交的数据。隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题
#####################################################################
mysql> set global transaction_isolation='READ-COMMITTED'; 退出数据库重新连接数据库
#A事务：
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update city set population='1000' where id =1;
Query OK, 1 row affected (0.00 sec)

#B事务：
mysql> begin;
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |       1000 |  #只有等A事务commit后才可以查到提交数据
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)


# RR ： REPEATABLE-READ 可重复读（默认） ：
防止脏读（当前内存读），不可重复读，幻读问题。需要配合锁机制来避免幻读。
# SE ： SERIALIZABLE 可串行化

#3.修改级别
set global transaction_isolation='READ-UNCOMMITTED';设置OK后需要退出重新连接数据库才生效


结论： 隔离性越高，事务的并发读就越差。
结论： 一般情况下RC就够用了。
select for update