事务
什么是事务?
多条sql语句,要么全部成功,要么全部失败。MySQL的事务是在存储引擎层实现。 MySQL的事务有ACID
A: 原子性(atomicity):一个事务必须被视为一个不可分割的单元。
C: 一致性(consistency):数据库是从一种状态切换到另一种状态。
I: 隔离性(isolation):事务在提交之前,对于其他事务不可见。
D: 持久性(durablity):一旦事务提交,所修改的将永久保存到数据库。
查看事务当前级别
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%isolation';
+-----------------------+--------------+
| Variable_name | Value |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
| tx_isolation | SERIALIZABLE |
+-----------------------+--------------+
2 rows in set (0.01 sec)
mysql> create table bank
-> (
-> name varchar(25),
-> money float
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into bank(name,money) values('lu','1000'),('hao'','5000');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
Begin 或 start transaction 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set money=money-1000 where name='hao';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update bank set money=money+1000 where name='lu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu | 2000 |
| hao | 4000 |
+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu | 1000 |
| hao | 5000 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu | 1000 |
| hao | 5000 |
+------+-------+
2 rows in set (0.00 sec)
【总结事务命令】
事务开始: start transaction
事务开始: begin
事务提交: commit
回 滚: rollback
查看自动提交模式是自动还是手动
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.05 sec)
mysql> set autocommit=off; //off换成0也可以
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.02 sec)
事务有4种隔离级别 事务在提交之前对其他事务可不可见
1.read unaommitted(未提交读)
2.read committed(已提交读)
3.Repeatable read(可重复读)
4. seaializable(可串行化)
详细解释:
未提交读
事务中修改没有提交对其他事务也是可见的,俗称脏读
mysql> CREATE TABLE student
-> (
-> id int not null auto_increment,
-> name varchar(32) not null default '',
-> primary key(id)
-> )engine=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)
两端的客户端都设置成未提交读
mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)
客户端A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('test1');
Query OK, 1 row affected (0.00 sec) //注意:此时事务未提交!!!
客户端B:
mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.00 sec)
客户端B可以查看到信息
总结:以上可以看出未提交读隔离级别非常危险,对于一个没有提交事务所做修改对另一个事务是可见状态,出现了脏读!非特殊情况不建议使用此级别。
已提交读
多数数据库系统默认为此级别(MySQL不是)。已提交读级别为一个事务只能已提交事务所做的修改,也就是解决了未提交读的问题
mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into student(name) values ('test2');
Query OK, 1 row affected (0.00 sec) //此时去另一个客户端去查看
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
客户端B:
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.02 sec) //未提交的时候查看
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec) //已提交事务之后查看
总结:从上面的例子可以看出,提交读没有了未提交读的问题,但是我们可以看到客户端A的一个事务中执行了两次同样的SELECT语句,,得到不同的结果,因此已提交读又被称为不可重复读。同样的筛选条件可能得到不同的结果。
可重复读–解决了不可重复读的问题,数据库级别没有解决幻读的问题。
mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec) //两个客户端均设置为可重复读
然后两边一起开启一个事务
客户端A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> update student set name='test' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
| 2 | test2 |
+----+-------+
2 rows in set (0.01 sec)
客户端B:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
总结:上面的例子我们得知,可重复读两次读取的内容不一样。数据库的幻读问题并没有得到解决。幻读只读锁定里面的数据,不能读锁定外的数据,解决幻读出了mvcc机制Mvcc机制。
可串行化–是最高隔离级别,强制事务串行执行,执行串行了也就解决问题了,这个I别只有在对数据一致性要求非常严格并且没有 并发的情况下使用
mysql> set session tx_isolation='serializable';
Query OK, 0 rows affected, 1 warning (0.00 sec) //客户端两边设置成可串行读
客户端A:
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student(name) values('test3');
Query OK, 1 row affected (0.00 sec) //未提交状态
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.00 sec)
客户端B:
mysql> select * from student;
//注意客户端A如果出入数据但未提交,客户端B就会卡在这,客户端A提交之后,客户端B就可以查看了
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | test |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.00 sec)
总结:我们发现INSERT 语句被阻塞执行,原因是A执行了查询表student同时满足id<10,已被锁定。如果查询表student同时满足id<5,则新增语句可以正常执行。
隔离级别 | 脏读 | 不可重复 | 幻读 | 加锁读 |
---|---|---|---|---|
未提交读 | 是 | 是 | 是 | 否 |
提交读 | 否 | 是 | 是 | 否 |
可重复读 | 否 | 否 | 是 | 否 |
串行读 | 否 | 否 | 否 | 是 |