mysql+事物插入_MySQL事务

MySQL事务

事务：一个或一组SQL语句组成的一个执行单元，要么全部成功，要么全部失败。

注：mysql中只有innodb支持事务

事务的ACID特性原子性(Atomicity)  指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

一致性(Consistency)  指事务必须使数据库从一个一致性状态变成另一个一致性状态

隔离性(Isolation) 指一个事务的执行不能被另一个事务干扰

持久性(Durability) 指事务一旦被提交，对数据库中数据的改变是永久性的

事务的创建

默认情况下mysql的事务是自动提交的show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit    | ON    |

+---------------+-------+

#需要手动的禁用一下事务自动提交

set autocommit = 0;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit    | OFF   |

+---------------+-------+

事务的隔离级别

查看数据库的隔离级别#mysql8中查看隔离级别

select @@transaction_isolation;

+-------------------------+

| @@transaction_isolation |

+-------------------------+

| REPEATABLE-READ         |

+-------------------------+

#mysql8之前查看隔离级别

select @@tx_isolation;

修改数据库的隔离级别#修改全局隔离级别

set global transaction isolation level read committed;

#修改会话隔离级别

set session transaction isolation level read committed;

事务并发出现的问题脏读  对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取到的内容就是临时无效的

不可重复读  对于两个事务T1,T2，T1读取了一个字段，T2更新了该字段，之后T1再次读取同一个字段，值就不同了(描述的是修改操作)

幻读  对于两个事务T1,T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，T1再次读取同一个表，就会多出几行(描述的是插入操作)

脏读和幻读相似，脏读属于修改操作，幻读属于插入操作

隔离级别READ UNCOMMITED(读未提交的数据)  允许事务读取未被其他事务提交的变更，脏读、不可重复读、幻读问题都存在

READ COMMITED(读已提交的数据)  只允许事务读取已被其他事务提交的数据，可以避免脏读，但是不可重复读和幻读仍存在

REPEATABLE READ(可重复读)  确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读仍存在(Mysql默认的事务隔离级别)

SERIALIZABLE(串行化) 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可以避免，但是性能低下

测试各个隔离级别

开启两个mysql连接，为mysql1和mysql2

原始数据为select * from users;

+----+---------+

| id | u_name  |

+----+---------+

|  2 | 张三    |

|  3 | 张三0   |

|  4 | 张三1   |

|  5 | 张三2   |

+----+---------+

测试READ UNCOMMITTED#首先设置mysql1和mysql2隔离级别为read uncommited

set session transaction isolation level read uncommitted;

#禁用mysql1 mysql2的自动提交

set autocommit = 0;

update users set u_name='李四' where id = 3;#①  mysql1修改数据但未提交

select * from users where id =3;#② mysql1 查询数据 为李四

select * from users where id =3;#③ mysql2 查询数据 也为李四 查询到了mysql1中未提交的数据 出现了脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交

select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据

select * from users where u_name = '刘亦菲';#⑥mysql2 查询到该数据  出现了幻读

rollback;#⑦mysql1 回滚之前的操作  此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读

测试read commited#首先设置mysql1和mysql2隔离级别为read commited

set session transaction isolation level read committed;

#禁用mysql1 mysql2的自动提交

set autocommit = 0;

update users set u_name='李四' where id = 3;#①  mysql1修改数据但未提交

select * from users where id =3;#② mysql1 查询数据 为李四

select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交

select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据

select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作  此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读

测试read commited#首先设置mysql1和mysql2隔离级别为read commited

set session transaction isolation level read committed;

#禁用mysql1 mysql2的自动提交

set autocommit = 0;

update users set u_name='李四' where id = 3;#①  mysql1修改数据但未提交

select * from users where id =3;#② mysql1 查询数据 为李四

select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交

select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据

select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作  此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读

测试repeatable read#首先设置mysql1和mysql2隔离级别为repeatable read

set session transaction isolation level repeatable read;

#禁用mysql1 mysql2的自动提交

set autocommit = 0;

update users set u_name='李四' where id = 3;#①  mysql1修改数据但未提交

select * from users where id =3;#② mysql1 查询数据 为李四

select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交

select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据

select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作  此时mysql2还是没有数据

保存点savepoint

在一个事务中可以设置保存点，和rollback搭配使用

测试保存点select * from users;

+------+-----------+

| id   | u_name    |

+------+-----------+

|    2 | 张三      |

|    3 | 李四      |

|    4 | 张三1     |

|    5 | 张三2     |

| 1006 | 刘亦菲    |

+------+-----------+

delete from users where id = 4;

savepoint a;#设置保存点

delete from users where id = 1006;

rollback to a;#回滚到保存点的位置

select * from users;

+------+-----------+

| id   | u_name    |

+------+-----------+

|    2 | 张三      |

|    3 | 李四      |

|    5 | 张三2     |

| 1006 | 刘亦菲    |

+------+-----------+