MySQL事务机制
事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言(如SQL,C 或Java)书写的用户程序的执行所引起,并用形如begin transaction和end transaction语句(或函数调用)来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
例如:在关系数据库中,一个事务可以是一条SQL语句,一组SQL语句或整个程序。
如果事务成功执行,那么该事务中所有的更新操作都会成功执行、并将执行结果提交到数据库文件中,成为数据库永久的组成部分。如果事务中某条更新操作执行失败,那么事务中的所有操作均被撤销。
事务 ACID Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性
一组事务,要么成功;要么撤回。
2、稳定性(一致性)
有非法数据(外键约束之类),事务撤回。
3、隔离性
事务独立运行。
一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。
事务的100%隔离,需要牺牲速度。
4、可靠性(持久性)
软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。
可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。
MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
2.直接用set来改变mysql的自动提交模式
但要注意当用set autocommit = 0 的时候,你以后所有的sql都将作为事务处理,直到你用commit确认或 rollback结束,注意当你结束这个事务的同时也开启了新的事务!按第一种方法只将当前的做为一个事务!
MYSQL只有 INNODB和BDB类型的数据表才支持事务处理,其他的类型是不支持的!
事务的4种隔离级别:
第1级别:Read Uncommitted(读取未提交内容)
(1)所有事务都可以看到其他未提交事务的执行结果
(2)本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少
(3)该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据
#首先,修改隔离级别
注意:修改隔离级别set tx_isolation不能写成set @@tx_isolation
set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
------------------
| @@tx_isolation
|
------------------
| READ-UNCOMMITTED |
------------------
#事务A:启动一个事务
start transaction;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务B:也启动一个事务(那么两个事务交叉了)
start transaction;
update tx set num=10 where id=1;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
10 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务A:那么这时候事务A能看到这个更新了的数据吗?
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
10 |
--->可以看到!说明我们读到了事务B还没有提交的数据
|
2 |
2 |
|
3 |
3 |
------ ------
#事务B:事务B回滚,仍然未提交
rollback;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务A:在事务A里面看到的也是B没有提交的数据
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 |
--->脏读意味着我在这个事务中(A中),事务B虽然没有提交,但它任何一条数据变化,我都可以看到!
|
2 |
2 |
|
3 |
3 |
------ ------
第2级别:Read Committed(读取提交内容)
(1)这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
(2)它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变
(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read):不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。
#首先修改隔离级别
set tx_isolation='read-committed';
select @@tx_isolation;
----------------
| @@tx_isolation |
----------------
| READ-COMMITTED |
----------------
#事务A:启动一个事务
start transaction;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务B:也启动一个事务(那么两个事务交叉了)
start transaction;
update tx set num=10 where id=1;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
10 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务A:这个时候我们在事务A中能看到数据的变化吗?
select * from tx; --------------->
------ ------
|
| id
| num
|
|
------ ------
|
|
1 |
1 |--->并不能看到!
|
|
2 |
2 |
|
|
3 |
3 |
|
------ ------
|——>相同的select语句,结果却不一样
#事务B:如果提交了事务B呢?
|
commit;
|
#事务A:
|
select * from tx; --------------->
------ ------
| id
| num
|
------ ------
|
1 |
10 |--->因为事务B已经提交了,所以在A中我们看到了数据变化
|
2 |
2 |
|
3 |
3 |
------ ------
第3级别:Repeatable Read(可重读)
(1)这是MySQL的默认事务隔离级别
(2)它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
(3)此级别可能出现的问题——幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行
(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题
#首先,更改隔离级别
set tx_isolation='repeatable-read';
select @@tx_isolation;
-----------------
| @@tx_isolation
|
-----------------
| REPEATABLE-READ |
-----------------
#事务A:启动一个事务
start transaction;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
------ ------
#事务B:开启一个新事务(那么这两个事务交叉了)
start transaction;
update tx set num=10 where id=1;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
10 |
|
2 |
2 |
|
3 |
3 |
------ ------
commit;
#事务A:这时候即使事务B已经提交了,但A能不能看到数据变化?
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
1 | --->还是看不到的!(这个级别2不一样,也说明级别3解决了不可重复读问题)
|
2 |
2 |
|
3 |
3 |
------ ------
#事务A:只有当事务A也提交了,它才能够看到数据变化
commit;
select * from tx;
------ ------
| id
| num
|
------ ------
|
1 |
10 |
|
2 |
2 |
|
3 |
3 |
------ ------
第4级别:Serializable(可串行化)
(1)这是最高的隔离级别
(2)它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
(3)在这个级别,可能导致大量的超时现象和锁竞争
#首先修改隔离界别
set tx_isolation='serializable';
select @@tx_isolation;
----------------
| @@tx_isolation |
----------------
| SERIALIZABLE
|
----------------
#事务A:开启一个新事务
start transaction;
#事务B:在A没有commit之前,这个交叉事务是不能更改数据的
start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction