MySQL总结（四）——事务

事务Transaction

可以保证数据的一致性

ACID

事务的特性

原子性（Atomicity）

事务要么全部执行，要么全部不执行。

一致性（Consistency）

一种合法的状态转换到另一种合法的状态，事务要保证数据的一致性，满足数据的约束

example

是A转账给B，A有100元，如果转给B 101元，此时不满足数据的约束

如果转给B 50 元，但是B没有增加，此时不满足；

隔离性（Isolation）

事物之间不相互影响

持久性（Durability）

事务一旦提交，对数据库的改变将是永久性的。

并发的问题

1. 脏写

事务A修改了事务B未提交并且修改的数据

2.脏读

事务A读了事务B未提交的数据

3.可重复读

事务A查了一个字段，事务B修改了这个字段，事务A又读了一次，两次的数据不一样了

4幻读

事务A读了2条数据，事务B增加了2两条记录，事务A再读的时候发现有4条记录，和幻觉一样。

隔离级别

有四种隔离级别

1. READ UNCOMMITTED

2. READ COMMITTED

解决了脏读

3. REPEATABLE READ (MySQL默认)

解决了可重复读

4. SERIALIZABLE

解决了幻读

Example

建表

mysql> DESC stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查看事务的隔离级别

mysql> SHOW VARIABLES LIKE 'TRANSACTION_ISOLATION';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

1.读未提交

设置为READ-UNCOMMITTED

mysql> SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'TRANSACTION_ISOLATION';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

事务A

mysql> insert into stu values (1, 'lang',18);

mysql> SELECT * FROM stu;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   18 |
+----+------+------+
1 row in set (0.00 sec)

事务B

mysql> SELECT * FROM stu;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   18 |
+----+------+------+
1 row in set (0.00 sec)

2.读已提交

事务A

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stu;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   20 |
|  2 | he   |   18 |
|  3 | zhao |   18 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from stu where id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   20 |
+----+------+------+
1 row in set (0.01 sec)

mysql> update stu set age = 1 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu where id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |    1 |
+----+------+------+
1 row in set (0.00 sec)

事务B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   20 |
|  2 | he   |   18 |
|  3 | zhao |   18 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from stu where id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from stu where id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |   20 |
+----+------+------+
1 row in set (0.00 sec)

事务A的age = 1，事务B的age = 20；解决了脏读

3.可重复读

事务A

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM stu WHERE id = 1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | laaang |    1 |
+----+--------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM stu WHERE id = 1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | laaang |    1 |
+----+--------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM stu WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |    1 |
+----+------+------+
1 row in set (0.00 sec)

事务B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE stu SET name = 'lang' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM stu WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lang |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

欢迎关注我的公众号

本文由mdnice多平台发布