Linux下数据库学习过程之事物（持续更新中）

一：事物

Transaction，最小的不可再分的工作单元
事物：一组逻辑操作单元，使得数据由一种状态转为另一种状态

1.事物的特性

事物是由一组sql语句组成的逻辑处理单元
包含四个特性：

特性	解释
原子性（Atomicity）	原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）	事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）	事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）	持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

2.事物的语法

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

START TRANSACTION or BEGIN start a new transaction. 
如何开启一个事物
COMMIT commits the current transaction, making its changes permanent.
提交一个事物，使永久改变
ROLLBACK rolls back the current transaction, canceling its changes.
回滚当前事物，取消其改变动作
SET autocommit disables or enables the default autocommit mode for the current session.
自动提交

3、事物操作

mysql> show variables like   '%commit%';查看事物状态

+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| autocommit                              | ON                |    自动提交开启
| binlog_group_commit_sync_delay          | 0                 |
| binlog_group_commit_sync_no_delay_count | 0                 |
| binlog_order_commits                    | ON                |
| innodb_api_bk_commit_interval           | 5                 |
| innodb_commit_concurrency               | 0                 |
| innodb_flush_log_at_trx_commit          | 1                 |
| original_commit_timestamp               | 36028797018963968 |
| replica_preserve_commit_order           | ON                |
| replication_sender_observe_commit_only  | OFF               |
| slave_preserve_commit_order             | ON                |
+-----------------------------------------+-------------------+
11 rows in set (0.01 sec

选择一个表

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
+---------+-----------+
7 rows in set (0.00 sec)

开启事物

mysql> start transaction;
mysql> begin;
mysql> insert into department values(null,'就业部');
mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
|    1008 | 就业部    |
+---------+-----------+
8 rows in set (0.00 sec)

回滚

mysql> rollback;
mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
+---------+-----------+
7 rows in set (0.00 sec)
此时事物已经结束

测试二：savepoint

mysql> start transaction;
开启事物
mysql> insert into department values(null,'就业部');
插入数据，就业部
mysql> savepoint sp1;
设置保存点sp1

mysql> insert into department values(null,'教学部');
mysql> insert into department values(null,'管理部');
mysql> insert into department values(null,'策划部');
连续插入三条数据

mysql> savepoint sp2;
设置第二个保存点

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
|    1009 | 就业部    |
|    1010 | 教学部    |
|    1011 | 管理部    |
|    1012 | 策划部    |
+---------+-----------+
11 rows in set (0.00 sec)

mysql> delete from department where dept_name='教学部';
删除教学部，测试保存点sp2
mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
|    1009 | 就业部    |
|    1011 | 管理部    |
|    1012 | 策划部    |
+---------+-----------+
10 rows in set (0.00 sec)

mysql> rollback to sp2;


mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
|    1009 | 就业部    |
|    1010 | 教学部    |
|    1011 | 管理部    |
|    1012 | 策划部    |
+---------+-----------+
11 rows in set (0.00 sec)

mysql> delete from department where dept_name='就业部';
删除就业部测试sp1

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
+---------+-----------+
7 rows in set (0.00 sec)

mysql> rollback to sp1;

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
|    1009 | 就业部    |
+---------+-----------+
8 rows in set (0.00 sec)

mysql> rollback;

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 销售部    |
|    1007 | 测试部    |
+---------+-----------+
7 rows in set (0.00 sec)
事物结束

注意：
rollback和commit都可以结束事物

4.重做日志和回滚日志

mysql> show variables like '%redo%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| innodb_redo_log_archive_dirs |           |
| innodb_redo_log_capacity     | 104857600 |
| innodb_redo_log_encrypt      | OFF       |
+------------------------------+-----------+
3 rows in set (0.00 sec)

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+
5 rows in set (0.00 sec)

二、事物的隔离级别

1、如果不开启隔离，可能会导致幻读，不可重复读，脏读
（1）脏读
指的是一个事物读取了另一个未提交的事物的数据
（2）不可重复读取
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
（3）幻读
幻读是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

2、数据库共定义了四种隔离级别：

• Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）

• Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）

• Read committed：可避免脏读情况发生（读已提交）。

• Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

3、设置事物隔离等级的语法

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

修改隔离等级分为全局和当前两种，全局需要刷新，当前不需要刷新