MySQL事务特性，隔离级别

事务特性ACID

Atomic,原子：同一个事务里，要么都提交，要么都回滚；

Consistency,一致性：即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；

Isolation,隔离：并发事务间的行数据是彼此隔离的；

Durability,持久：事务提交后，所有结果务必被持久化。

 

MySQL支持事务的存储引擎：Innodb，NDBcluster，TokuD

MySQL不支持事务的存储引擎：myisam  ，memory

 

1.隔离性通过锁的方式实现

2.原子性，一致性，持久性通过数据库的redo和undo来完成

撤销日志，undo log 没提交的事务撤销

重做日志，redo log 检查已经提交没有持久化的事务重做

 

显式事务启动|结束
1.以start transaction/begin开始事务
2.以commit/rollback transaction结束事务

隐形事务提交
主要是DDL,DCL会引发事务隐形提交
DDL语句
1.alter function
2.alter procedure
3.alter table
4.begin
5.create databases
6.create function
7.create index
8.create procedure
9.create table
10.drop databases
11.drop function
12.drop index
13.drop procedure
14.drop table
15.unlock tables
16.load master data
17.lock tables
18.rename table
19.truncate table
20.set autocommit=1
21.start transaction
22.create table…select
23.create temporary table ….select 除外
用户管理
1.create user
2.drop user
3.grant
4.rename user
5.revoke
6.set password
事务控制
1.begin
2.lock tables
3.set autocommit=1（if the valueis not already 1）
4.start transaction
5.unlock tables
6.lock tables unlock tables也会
7.flush tables with read lock & unlock table除外
数据导入
Load data infile
表管理语句
1.analyze table
2.cache index
3.check table
4.load index into cache
5.optimize table
6.repair table

事务隔离级别

Read uncommitted（读未提交）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。会发生脏读。

ReadCommitted（读已提交）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。会发生不可重复读。

RepeatableRead（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。可能发生幻读。

Serializable（可串行化） 
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

 

Dirty Read（脏读）

事务A读取到另一个事务B中修改后但是尚未提交的数据，并在这个数据的基础上操作，这时如果事务B回滚那么A事务读到的数据是无效的，不符合一致性。

Unrepeatable Read（不可重复读）

事务A读取到另外一个事务B已经提交更改的数据，不符合隔离性。

Phantom Read（幻读）

事务A读取到了另一事务B提交的新增数据不符合隔离性。

Innodb 采用next-key lock机制来避免幻读，RR+innodb_locks_unsafe_for_binlog=1，它的作用是事务隔离级别降为RC，只有record lock，没有gap lock。

隔离级别	Dirty read（脏读）	Unrepeatable read（不可重复读）	Phatom read（幻读）
Read Uncommitted（读未提交）	可能	可能	可能
Read Commited（读已提交）	不可能	可能	可能
RepeatableRead（可重复读）	不可能	不可能	不可能（innodb特定条件下可能）
Serializable（串行）	不可能	不可能	不可能

set tx_isolation='read-uncommitted'；

select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-UNCOMMITTED       |

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

1 row in set (0.00 sec)

案例1读到脏数据（脏读）

 

Session1	Session2
>begin; Query OK, 0 rows affected (0.00 sec) >select * from t5 where id=7; Empty set (0.00 sec)
	>select * from t5 where id=7; Empty set (0.00 sec
>insert into t5 select 7,'wwb',29,'dba','M'; Query OK, 1 row affected (0.00 sec) Records: 1  Duplicates: 0  Warnings: 0 >select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | M    | +----+------+------+---------+------+ 1 row in set (0.00 sec)
	>select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | M    | +----+------+------+---------+------+ 1 row in set (0.00 sec)

 

将事务隔离级别改为RC

>set tx_isolation='read-committed';

QueryOK, 0 rows affected (0.00 sec)

>select@@session.tx_isolation;

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

|@@session.tx_isolation |

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

|READ-COMMITTED         |

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

1 row in set (0.00 sec)

不可重复读

Session1	Session2
>select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | M    | +----+------+------+---------+------+ 1 row in set (0.00 sec)
	select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | M    | +----+------+------+---------+------+ 1 row in set (0.00 sec)
update t5 set sex='W' where id=7; select * from t5; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | W    | +----+------+------+---------+------+ commit; Query OK, 0 rows affected (0.00 sec)
	select * from t5; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | W    | +----+------+------+---------+------+ 1 row in set (0.00 sec)

幻读

begin; Query OK, 0 rows affected (0.00 sec) >select * from t5; +------+--------+------+---------+------+ | id   | name   | age  | content | sex  | +------+--------+------+---------+------+ |    7 | wwb    |   29 | dba     | M    | |    8 | laoyan |   29 | dba     | M    | +------+--------+------+---------+------+	begin; Query OK, 0 rows affected (0.00 sec) >select * from t5; +------+--------+------+---------+------+ | id   | name   | age  | content | sex  | +------+--------+------+---------+------+ |    7 | wwb    |   29 | dba     | M    | |    8 | laoyan |   29 | dba     | M    | +------+--------+------+---------+------+ 2 rows in set (0.00 sec)
>insert into t5 select 9,'leilei',32,'dba','M'; Query OK, 1 row affected (0.00 sec) Records: 1  Duplicates: 0  Warnings: 0 root@localhost:mysql3308.sock  03:24:05 [wwb]>select * from t5; +------+--------+------+---------+------+ | id   | name   | age  | content | sex  | +------+--------+------+---------+------+ |    7 | wwb    |   29 | dba     | M    | |    8 | laoyan |   29 | dba     | M    | |    9 | leilei |   32 | dba     | M    | +------+--------+------+---------+------+ 3 rows in set (0.01 sec)
	select * from t5; +------+--------+------+---------+------+ | id   | name   | age  | content | sex  | +------+--------+------+---------+------+ |    7 | wwb    |   29 | dba     | M    | |    8 | laoyan |   29 | dba     | M    | |    9 | leilei |   32 | dba     | M    | +------+--------+------+---------+------+ 3 rows in set (0.00 sec)

>select@@session.tx_isolation;

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

|@@session.tx_isolation |

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

|REPEATABLE-READ        |

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

1 row in set (0.00 sec)

Session	Session
>begin; Query OK, 0 rows affected (0.00 sec) >select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | W    | +----+------+------+---------+------+ 1 row in set (0.00 sec)
	>begin; Query OK, 0 rows affected (0.00 sec) >select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | W    | +----+------+------+---------+------+ 1 row in set (0.00 sec)
>update t5 set sex='M' where id=7; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 >commit; Query OK, 0 rows affected (0.00 sec)
	>select * from t5 where id=7; +----+------+------+---------+------+ | id | name | age  | content | sex  | +----+------+------+---------+------+ |  7 | wwb  |   29 | dba     | W    | +----+------+------+---------+------+ 1 row in set (0.00 sec)

修改查看隔离级别

在my.cnf配置文件中【mysqld】分段中，加入一行

Transaction-isolation=‘READ-COMMITTED’  #默认值是REPEATABLE-READ

在线动态修改

Set【GLOBAL|SESSIION】 TRANSACTION ISOLATION LEVEL READ COMMITTED

查看当前隔离级别

Select @@global.tx_isolation，@@session.tx_isolation，@@tx_isolation；

MySQL默认事务隔离级别是:RR