[大数据]MySQL事务及代码详解

目录

1.1事务

1.2四大特性

1.3事务隔离级别

1.4代码实现

1.1事务

事务指的是一组逻辑操作，要么全部执行成功，要么全部执行失败。

• MySQL中以InnoDB数据库引擎建立的库和表才支持事务

• 事务处理可以来保证数据库维护的完整性

• MySQL默认自动提交事务

• 事务包含四大特性（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

1.2四大特性

1.原子性（Atomicity）：组成事务的逻辑操作是最小单元不可分割的；这些操作要么全部执行，要么全部不执行。

2.一致性（Consistency）：事务提交前后的数据必须保持一致。

3.隔离性（Isolation）：每个事务之间相互独立，互不影响。

4.持久性（Durability）：事务提交后，产生的数据必须持久化的保存下来。

1.3事务隔离级别

MySQL中共有4种不同的隔离级别，这4种隔离级别分别是：

1. 读未提交（READ UNCOMMITTED）：事务将会读取到未提交的数据，可能会造成脏读、可重复读和幻读的现象，是一种较低的隔离级别，在实际中较少使用。

2. 读已提交（READ COMMITTED）：该种隔离级别在事务1没有提交或回滚时，事务2可避免脏读，但是在事务1提交或回滚之后，事务2出现了可重复读和幻读的情况。

3. 可重复读（REPEATABLE READ）：可重复读是MySQL默认的隔离级别，可以有效避免脏读和可重复读的情况，但是不能避免幻读。

4. 可串行化（SERIABLIZABLE）：可以同时解决脏读、可重复读和幻读的情况，但是由于会出现阻塞的情况，所以实际中也较少使用。

1.4代码实现

设置MySQL事务提交为不可自动提交事务：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

选择测试表account，查询信息如下：

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?4000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

1.读未提交（READ UNCOMMITTED）:

由下面的查询结果可知,MySQL默认的隔离级别为可重复读（REPEATABLE READ）:

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

因此需要将默认的隔离级别设置为读未提交（READ UNCOMMITTED）:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation ? |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

同时开启两个会话窗口。

(1)在1窗口执行：

mysql> update account set money=1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
?
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?1000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

(2)在2窗口查询account表：

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?1000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

此时可以发现窗口2读取到了窗口1还未提交的数据，发生了数据的脏读。

2.读已提交（READ COMMITTED）：

将数据库隔离级别修改为读已提交（READ COMMITTED）:

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

(1)在窗口1执行如下语句：

mysql> update account set money=3000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
?
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?3000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

(2)在窗口2查询account表：

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?1000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

此时可以发现，窗口1操作的事务未commit时，窗口2读取的数据依旧是发生修改之前的，也就是说READ COMMITTED避免了脏读现象；将窗口1的事务提交，窗口2再次查询：

窗口1:
mysql> commit;
Query OK, 0 rows affected (0.07 sec)

窗口2:
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?3000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

窗口1提交后窗口2读取到的数据便是修改后的数据，使用READ COMMITTED的隔离级别避免了脏读的出现，但是不能避免可重复读，即同一时间段内一个用户多次查询的数据可能出现不一致的情况。

3.可重复读（REPEATABLE READ）：

将数据库隔离级别修改为可重复读（REPEATABLE READ）：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(1)在窗口1执行如下修改：

mysql> update account set money=1500 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
?
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?1500 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

(2)在窗口2执行查询：

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?3000 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

此时发现，在第二个窗口中查询的数据并没有发生改变，REPEATABLE READ可以避免脏读。

(3)在窗口1执行事务提交：

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

(4)在窗口2再次查询： 

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ??| ?3000 |
| ?2 | ls ??| ?2000 |
| ?3 | ww ??| ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

在第二个窗口中无论读取多少次，读取到的数据都不会是第一个窗口中更新的数据，只有当第二个窗口也提交时，此时的第二个窗口才会更新数据。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
?
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| ?1 | zz ? | ?1500 |
| ?2 | ls ? | ?2000 |
| ?3 | ww ? | ?6000 |
+----+------+-------+
3 rows in set (0.00 sec)

此时,在窗口2执行插入一条数据：

mysql> insert into account values(4,'dd',4500);
Query OK, 1 row affected (0.06 sec)

在窗口1更新数据：

mysql> update account set money=2000;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

按照道理来说应该时3行数据受到影响，但是实际显示为4行受到影响，说明出现了幻读的现象。

4.可串行化（SERIABLIZABLE）：

修改隔离级别：

mysql> SET GLOGAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

此种隔离级别下，窗口1未提交时(未释放锁)，窗口2要执行更新操作时，会发生阻塞现象，只有在窗口1执行提交操作后，窗口2的才能操作成功。

此种隔离级别虽然解决了所有的问题，但是因为效率太低，实际开发中很少运用。