mysql事务

准备工作了解

mysql -uroot -p123456

use test;

;系统级隔离级别,如果只是实验系统的可以不修改只修改会话的值 SET GLOBAL tx_isolation='READ-UNCOMMITTED';

;会话级隔离级别

set session tx_isolation='read-uncommitted';

select @@global.tx_isolation,@@tx_isolation;

其他模式类似操作

概念

不可重复读:

事务B修改id=1的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读.

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

脏读:

事务中未提交的数据,只要别的会话也是read-uncommitted模式下不管有没有开启事务都能看到.

或者说在一个事务处理过程里读取了另一个未提交的事务中的数据。

脏读和不可重复读的区别:

脏读不需要提交就会影响别的会话,不可重复读是别的会话commit之后才看到的叫重复读

虚读/幻读:

注意:以下实验都是我都只设置了session的变量,如果你一开始就设置了GLOBAL结果会不一样

下面说的a都是a环境(a数据库连接)   b都是b环境(b数据库连接)  重点是第一个例子内容会比较多案例剩下的类似只会对特性进行说明

test1表格式

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

案例:

a:mysql> set session tx_isolation='read-uncommitted'; begin;   insert into test1 values(5,null,null);  select * from test1;(多了1条数据)

b:mysql> select * from test1;(没有a新增数据)

b:mysql> insert into test1 values(5,null,null);(会等待a的数据处理,直到超时出现ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction或者a事务结束则b会执行)

b:mysql> insert into test1 values(6,null,null);(新增数据成功)

b:mysql> set session tx_isolation='READ-UNCOMMITTED';   select * from test1;(有a的新增数据)

b:mysql> begin;insert into test1 values(5,null,null);(执行超时)

a:mysql> rollback;

b:mysql> rollback;

结论

1.a是read-uncommitted b是repeatable-read 则 a的insert/update/delete 一条id=5的数据 b是看不到的,当也不可以操作这条id=5的数据,除了这条数据外b都可以进行insert/update/delete

2.a,b都是read-uncommitted 则b能看到a的数据,两者对数据进行insert/update/delete都会影响到对方,所以这种模式会出现很多超时,互相嵌套执行,出现问题维护起来无从下手

Read Committed（读取提交内容）

一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

a:mysql>set session tx_isolation='read-committed';   

b:mysql>set session tx_isolation='repeatable-read';

a:mysql>begin; delete from test1 where id=4;  select * from test1;(删除成功)

b:mysql>select * from test1;(id=4的数据还有显示)

b:mysql> update test1 set value=1 where id=4;(超时)

a:mysql> insert into test1 values(5,null,null);select * from test1;(新增成功)

b:mysql> select * from test1;(没有显示)

b:mysql> set session tx_isolation='read-committed';select * from test1;(依然没有显示a新增的数据)

b:mysql> insert into test1 values(5,null,null);(超时)

b:mysql> begin; insert into test1 values(5,null,null);(超时)

a:rollback;

b:rollback;

结论:

1.a在read-committed模式下b在repeatable-read模式下,a的insert/update/delete的操作对b的查询都是不影响的,但是b不能对a事务下正在insert/update/delete的数据进行修改,需要等待a事务结束

2.a,b都在read-committed模式下,b也不能看到a的修改数据了,但是b一样不能操作a正在操作的数据,当a没有结束事务,不管b有没有开启事务

3.read-committed和read-uncommitted的区别就在于committed只有提交后别的数据库才能看到这些数据,而uncommitted则不是只要别的环境也uncommitted模式下就行看到对方修改的数据

a:mysql> set session tx_isolation='repeatable-read';

b:mysql> set session tx_isolation='REPEATABLE-READ';

a:mysql> begin;insert into test1 values(5,null,null);select * from test1;

b:mysql> select * from test1;(没有看a的新数据)

b:mysql> insert into test1 values(5,null,null);(超时)

b:mysql> begin;select * from test1; (没有看a的新数据)

a:mysql>commit;

b:mysql> select * from test1;(没有看到a的新数据)

b:mysql> insert into test1 values(5,null,null);(这里不是超时,ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY)

b:mysql> update test1 set value=5 where id=5;select * from test1;(修改成功了,同时id=5的数据也出现了!!!!)

结论:

1.a,b在repeatable模式下,a开启事务了,b不管没有开启事务都看不到a新增的数据,同时b也不可以操作a在操作的数据

2.a,b在repeatable模式下都开启了事务,a对数据的操作就算提交了,b也是查询出来的也是开启事务之前的数据,但是这个时候b进行insert同一条数据,会直接报错不会超时.如果update这条数据会成功而且这条数据用select也能查询出来了,但是a在b提交前看不到

Serializable（可串行化）

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

a:mysql> set session tx_isolation=serializable; begin;

b:mysql> set session tx_isolation=serializable;

a:mysql> insert into test1 values(7,7,7);

b:mysql> select * from test1;(没有a新增数据)

b:mysql>insert into test1 values (8,8,8);(超时!!!!注意这里我操作的不是id=7的数据)

b:mysql> update test1 set value=4 where id=4;(也超时)

b:mysql> begin;

b:mysql> select * from test1;(超时!!!!!!)

结论:

1.a,b都在serializable模式,a开启事务,入库了1条数据,b就只能执行select语句了,insert/update就算操作的不是a的数据也执行超时,当b也开启事务,那么连select语句b也会超时

补充

读锁：

也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

写锁：

又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

表锁：

操作对象是数据表。是系统开销最低但并发性最低的一个锁策略。事务T对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。

行级锁：

操作对象是数据表中的一行。是MVCC技术用的比较多的，但在MYISAM用不了，行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大，处理高并发较好。

MVCC：

多版本并发控制（MVCC,Multiversion Currency Control）。一般情况下，事务性储存引擎不是只使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。Mvcc处理高并发能力最强，但系统开销比最大（较表锁、行级锁），这是最求高并发付出的代价。

Autocommit：

是mysql一个系统变量，默认情况下autocommit=1表示mysql把没一条sql语句自动的提交，而不用commit语句。所以，当要开启事务操作时，要把autocommit设为0，可以通过“setsession autocommit=0;”来设置

幻读:

参考资料

MYSQL隔离级别以及相对应操作

Innodb中的事务隔离级别和锁的关系