数据库|03 | 浅谈mysql事务数据库|mysql

mysql事务有了解多少?

在mysql中，事务是在引擎层实现的，目前mysql常用的两种存储引擎分别为：myisam和innodb，innodn支持，而myisam是不支持事务的（第二篇文章有说到原因），这也是为什么现在mysql的默认存储引擎是innodb
提到事务，我们肯定会想到mysql的ACID（Atomicity，Consistency，Isolation，Durability，即原子性，一致性，隔离性，持久性）属性.
A：原子性指的是同生共死，一个事物中的一组SQL语句要么一起执行成功，要么全部执行失败。例如：事务a中包含6条sql语句，只要其中有一条执行失败则其他执行成功的语句都会回滚到执行前的状态
B：一致性指的是事务执行前后数据要保持一致，例如：b卡里有500块钱，a给b转1000块钱，同时b给c转300，这个时候b账户只会是1200而不会是其他的
C：隔离性指的是事务间的可读性，当多个事务同时进行的时候就可能会出现脏读，不可重复读，幻读等现象，为了解决这些问题，就有了事务隔离的概念，事务隔离分4个级别：读未提交，读提交，可重复读，串行化，隔离级别越高，成本越大，mysql默认隔离级别为可重复读
a.读未提交：一个事务还没有提交时，它做的变更其他事务也可以看到
b.读提交：一个事务提交之后，它做的变更才能被其它事务看到（可以解决脏读）
c.可重复读：一个事务在执行过程中看到的数据，总是跟事务开始的时候一样，同时它所做的变更在提交之前对其它事也不可见（可以解决脏读，不可重复读）
d.串行化：通过加锁的方式实现，读的时候会加上读锁，写的时候则会加上写锁，当同时存在多个事物对同一行数据进行读写的时候则会出现冲突，这个时候就会排队一个个执行（可以解决脏读，不可重复读，幻读）
D: 持久性指的是数据的状态，事物完成后数据永久性保存
总是要记得使用 set autocommit=1,在同一个线程中自动提交事务！如果autocommit=0，则不会自动提交事务，如果是长连接则很可能形成长事务。

autocommit的测试,对于innodb表：首先创建两个innodb表： mysql> create table tab_kx(a int auto_increment,primary key(a)); Query OK, 0 rows affected (0.16 sec)mysql> create table tab_kx2(a int auto_increment,primary key(a)); Query OK, 0 rows affected (0.17 sec)在session1，设置autocommit为OFF： mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit| OFF| +---------------+-------+ 1 row in set (0.00 sec)在session2设置autocommit为ON： mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit| ON| +---------------+-------+ 1 row in set (0.00 sec)在session1，对tab_kx插入三行数据但不提交： mysql> insert into tab_kx values(''); Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into tab_kx values(''); Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into tab_kx values(''); Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from tab_kx; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)mysql> select * from tab_kx2; Empty set (0.00 sec)此时查看session2是否可以看到这三行数据： mysql> select * from tab_kx; Empty set (0.00 sec)【说明】，session2看不到其他session没有提交的DML；此时在session2插入四行数据到表tab_kx2（由于是auto commit的，所以不需要提交）： mysql> insert into tab_kx2 values(); Query OK, 1 row affected (0.07 sec)mysql> insert into tab_kx2 values(); Query OK, 1 row affected (0.04 sec)mysql> insert into tab_kx2 values(); Query OK, 1 row affected (0.04 sec)mysql> insert into tab_kx2 values(); Query OK, 1 row affected (0.02 sec)mysql> select * from tab_kx2; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.00 sec)【注意】但此时session1仍然看不到tab_kx2的数据， mysql> select * from tab_kx2; Empty set (0.00 sec) 当手动执行commit时，才可以看到这些数据，同时session2也可以看到tab_kx表的数据。 mysql> commit; Query OK, 0 rows affected (0.03 sec)mysql> select * from tab_kx2; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.00 sec)引用： http://www.cnblogs.com/chedanlangren/p/8318673.html

事务隔离级别中的读提交和可重复读是通过 read view实现的
a. 读提交：每一次读取都会创建视图，隔离效果仅限于该条语句
【数据库|03 | 浅谈mysql事务】b. 可重复读：第事务开始后的第一次读操作创建视图，在该事务提交之前都使用同一个视图以确保可重复读

事务的起始点其实是以执行的第一条语句为起始点的，而不是以begin作为事务的起始点的。实验1：sesseion A session B mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +----+------+ | c1 | c2| +----+------+ |1 |1 | +----+------+ 1 row in set (0.00 sec) 上面的实验说明：RR隔离级别下的一致性读，不是以begin开始的时间点作为snapshot建立时间点，而是以第一条select语句的时间点作为snapshot建立的时间点。实验2：session A session B mysql> set tx_isolation='repeatable-read'; mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec) mysql> begin; mysql> select * from t; mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; Empty set (0.00 sec) 该使用说明：RR隔离级别下的一致性读，是以第一条select语句的执行点作为snapshot建立的时间点的，即使是不同表的select语句。这里因为session A在insert之前对 t 表执行了select，所以建立了snapshot，所以后面的select * from t1 不能读取到insert的插入的值。实验3：session A session B mysql> set tx_isolation='repeatable-read'; mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec) mysql> begin; mysql> select * from t1; Empty set (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1(c1,c2) values(1,1); mysql> select * from t1; Empty set (0.01 sec) 该实验中：session A 的第一条语句，发生在session B的 insert语句提交之前，所以session A中的第二条select还是不能读取到数据。因为RR中的一致性读是以事务中第一个select语句执行的时间点作为snapshot建立的时间点的。而此时，session B的insert语句还没有执行，所以读取不到数据。实验4：session A session B mysql> set tx_isolation='repeatable-read'; mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1(c1,c2) values(1,1),(2,2); mysql> select * from t1; +----+------+ | c1 | c2| +----+------+ |1 |1 | |2 |2 | +----+------+ 2 rows in set (0.01 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> update t1 set c2=100 where c1=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1Changed: 1Warnings: 0 mysql> select * from t1; +----+------+ | c1 | c2| +----+------+ |1 |100 | +----+------+ 1 row in set (0.00 sec) 该实验说明：本事务中进行修改的数据，即使没有提交，在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”，所以它可以修改成功。引用： https://www.cnblogs.com/digdeep/p/4947694.html

尽量避免使用长事务，长事务可能会拖垮整个数据库。假如一个长事务查询使用了1000条数据，那其他事务只要查询使用了这1000行数据中的一条，即使这些事务完成提交掉，但伴随着这些事务产生的涉及到那1000条数据的日志及视图快照都会保留下来，直到那个长事务结束后，引擎认为再没有其他事务使用这些日志和视图快照时才会进行清理。