一、Mysql表引擎

  • Myisam:

Mysql 5.5 版本之前的默认表引擎
不支持事务
不支持外键
提供 表锁

存储数据时，会将数据存储在三个文件中：一个文件存数据，一个文件存结构，一个文件存索引

  • innodb：

支持事务
支持外键
提供 行锁

存储数据时，会将数据存储在两个文件中：一个文件存数据和索引，一个文件存结构
查询速度相对较慢

二、事务（Transaction）的概述

  1. MySQL 事务主要用于处理操作量大，复杂度高的数据。
  2. 在 MySQL 中只有使用了 Innodb 数据库引擎 的数据库或表才支持事务。
  3. 事务是一个不可再分的工作单元；通常将一组有序的增删改操作看做一个独立的执行单元，当这一组所有的操作都成功，事务执行成功，如果其中有一个操作失败，事务执行回滚
  4. 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

三、事务的四大特性（ACID）

  1. 原子性（Atomicity）： 事务被作为最小的执行单元，不可分割。一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
  2. 一致性（Consistency）： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。即写入的资料必须完全符合所有的预设规则。
  3. 隔离性（Isolation）： 多个用户并发访问数据库时，数据库会给每个用户开启一个事务，事务和事务之间相互隔离，互不影响。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
    事务隔离分为不同级别，包括读未提交（Read uncommitted）、读已提交（read committed）、可重复读（repeatable read） 和 串行化（Serializable）。
  4. 持久性（Durability）： 一旦事务执行成功，对于数据的修改会被永久性的存储在数据库中

四、事务的四种隔离级别

4.1、 如果事务没有隔离性，会导致以下情况:

  • 脏读： 一个事务使用了 另一个未提交事务的数据；
  • 不可重复读： 当 一个事务重复读取 一条数据，另一个事务修改了这条数据，导致两次读取的结果不一样；
  • 幻读： 一个事务读取了多条数据并做了操作(统计，计算)，另一个事务修改了其中一条数据，导致操作结果不一样，就像出现了幻觉一样。

4.2、 事务存在隔离性的话，会有以下四种隔离级别，隔离级别越高效率越低。

- Read uncommit: 读未提交：最低的隔离级别  可能会发生任何情况
  • 事物A和事物B，事物A未提交的数据，事物B可以读取到
  • 这里读取到的数据叫做“脏数据”
  • 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
  • Read commit: 读已提交：可以避免脏读
  • 事物A和事物B，事物A提交的数据，事物B才能读取到
  • 这种隔离级别高于读未提交
  • 换句话说，对方事物提交之后的数据，我当前事物才能读取到
  • 这种级别可以避免“脏数据”
  • 这种隔离级别会导致“不可重复读取”
  • Oracle默认隔离级别
  • Repeatable read: 可重复读： 可以避免脏读和 不可重复读
  • 事务A和事务B，事务A提交之后的数据，事务B读取不到
  • 事务B是可重复读取数据
  • 这种隔离级别高于读已提交
  • 换句话说，对方提交之后的数据，我还是读取不到
  • 这种隔离级别可以避免“不可重复读取”，达到可重复读取
  • 比如1点和2点读到数据是同一个
  • MySQL默认级别
  • 虽然可以达到可重复读取，但是会导致“幻读”
  • Serializable: 串行化 ，最高隔离级别，可以避免任何情况发生
  • 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
  • 这种隔离级别很少使用，吞吐量太低，用户体验差
  • 这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

五、事务隔离级别操作

1、设置事务隔离级别

方法一：在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

例如：
[mysqld]
transaction-isolation = READ-COMMITTED

方法二：通过命令动态设置隔离级别。

隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>  

其中的<isolation-level>可以是：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2、隔离级别的作用范围

•   事务隔离级别的作用范围分为两种： 
–   全局级：对所有的会话有效 
–   会话级：只对当前的会话有效 

•   例如，设置会话级隔离级别为READ COMMITTED ：
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

•   设置全局级隔离级别为READ COMMITTED ： 
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

3、查看隔离级别

查看当前会话隔离级别
SELECT @@tx_isolation;

查看系统当前隔离级别
select @@global.tx_isolation;

六、事务操作的语句

  • START TRANSACTION或BEGIN：显式地开启一个事务；
  • COMMIT：会提交事务，并使对数据库进行的所有修改成为永久性的；
  • ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT point名称：是事务过程中的一个逻辑点，用于取消部分事务，当结束事务时，会自动的删除该事务中所定义的所有保存点。当执行rollback时，通过指定保存点可以回退到指定的点。在相应的事务操作组里，保存点的名称必须是唯一的；
  • RELEASE SAVEPOINT point名称：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO point名称：把事务回滚到标记点；

1、提交操作

mysql> start transaction;#手动开启事务
mysql> insert into user(name) values('王五');
mysql> commit;#commit之后即可改变底层数据库数据
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | 张三  |
|  2 | 李四  |
|  3 | 王五  |
+----+------+
3 rows in set (0.00 sec)

2、回滚操作

mysql> start transaction;
mysql> insert into user(name) values('赵六');
mysql> rollback;
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | 张三  |
|  2 | 李四  |
|  3 | 王五  |
+----+------+
3 rows in set (0.00 sec)