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

事务隔离级别

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

set tx_isolation='read-uncommitted'；
select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

脏读

 

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