首页 > 代码库 > MySQL事务特性,隔离级别
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 function2.alter procedure3.alter table4.begin5.create databases6.create function7.create index8.create procedure9.create table10.drop databases11.drop function12.drop index13.drop procedure14.drop table15.unlock tables16.load master data17.lock tables18.rename table19.truncate table20.set autocommit=121.start transaction22.create table…select23.create temporary table ….select 除外用户管理1.create user2.drop user3.grant4.rename user5.revoke6.set password事务控制1.begin2.lock tables3.set autocommit=1（if the valueis not already 1）4.start transaction5.unlock tables6.lock tables unlock tables也会7.flush tables with read lock & unlock table除外数据导入Load data infile表管理语句1.analyze table2.cache index3.check table4.load index into cache5.optimize table6.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: 0root@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
MySQL事务特性,隔离级别
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。