抱歉,您的浏览器无法访问本站
本页面需要浏览器支持(启用)JavaScript
了解详情 >

MySQL事务

数据库事务

为什么需要事务

​ 现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。

​ A 给B 要划钱,A 的账户-1000元, B 的账户就要+1000元,这两个update 语句必须作为一个整体来执行,不然A 扣钱了,B 没有加钱这种情况很难处理。

什么存储引擎支持事务

查看数据库下面是否支持事务

show engines;

查看mysql当前默认的存储引擎

show variables like ‘%storage_engine%’;

1
2
3
4
5
6
7
8
9
10
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)
查看某张表的存储引擎

show create table 表名 ;

对于表的存储结构的修改

建立InnoDB 表:Create table …. type=InnoDB;

Alter table table_name type=InnoDB;

事务特性(ACID)

如果一个数据库声称支持事务的操作,那么该数据库必须要具备以下四个特性:

原子性(Atomicity)

​ 一个事务必须被视为一个不可分割的最小单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作,整个事务要么全部成功,要么全部失败。

一致性(consistency)

​ 一致性是指事务将数据库从一种一致性转换到另外一种一致性状态,在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。

​ 拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。

持久性(durability)

​ 一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失。并不是数据库的角度完全能解决。

  例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务以及正确提交,即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。

隔离性(Isolation)

​ 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

​ 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰(对数据库的并行执行,应该像串行执行一样)。

​ 即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。

为什么要设置隔离级别

在数据库操作中,在并发的情况下可能出现如下问题

更新丢失(Lost update)

​ 如果多个线程操作,基于同一个查询结构对表中的记录进行修改,那么后修改的记录将会覆盖前面修改的记录,前面的修改就丢失掉了,这就叫做更新丢失。这是因为系统没有执行任何的锁操作,因此并发事务并没有被隔离开来。

第1类丢失更新

事务A撤销时,把已经提交的事务B的更新数据覆盖了。

第2类丢失更新

事务A覆盖事务B已经提交的数据,造成事务B所做的操作丢失

解决方法

对行加锁,只允许并发一个更新事务。

脏读(Dirty Reads)

脏读(Dirty Read):A事务读取B事务尚未提交的数据并在此基础上操作,而B事务执行回滚,那么A读取到的数据就是脏数据。

解决办法:

如果在第一个事务提交前,任何其他事务不可读取其修改过的值,则可以避免该问题。

不可重复读(Non-repeatable Reads)

一个事务对同一行数据重复读取两次,但是却得到了不同的结果。事务T1读取某一数据后,事务T2对其做了修改,当事务T1再次读该数据时得到与前一次不同的值。

解决办法:

如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。

幻象读

​ 指两次执行同一条 select 语句会出现不同的结果,第二次读会增加一数据行,并没有说这两次执行是在同一个事务中。一般情况下,幻象读应该正是我们所需要的。但有时候却不是,如果打开的游标,在对游标进行操作时,并不希望新增的记录加到游标命中的数据集中来。隔离级别为 游标稳定性 的,可以阻止幻象读。

​ 例如:目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工,得到了10条记录;这时事务2向员工表插入了一条员工记录,工资也为1000;那么事务1再次读取所有工资为1000的员工共读取到了11条记录。

解决办法

如果在操作事务完成数据处理之前,任何其他事务都不可以添加新数据,则可避免该问题。

事务的隔离级别

数据库事务的隔离级别有4个,由低到高依次为

  • Read uncommitted(未授权读取、读未提交)
  • Read committed(授权读取、读提交)
  • Repeatable read(可重复读取)
  • Serializable(序列化)

这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

读未提交

Read uncommitted(未授权读取、读未提交)

​ 如果一个事务已经开始写数据,则另外一个事务则不允许同时进行写操作,但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失,却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。

读已提交

Read committed(授权读取、读已提交)

​ 读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读,但是却可能出现不可重复读。事务A事先读取了数据,事务B紧接了更新了数据,并提交了事务,而事务A再次读取该数据时,数据已经发生了改变。

可重复读

Repeatable read(可重复读)

​ 可重复读是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,即使第二个事务对数据进行修改,第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的,因此称为是可重复读。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。这样避免了不可重复读取和脏读,但是有时可能出现幻象读。(读取数据的事务)这可以通过“共享读锁”和“排他写锁”实现。

序列化

Serializable(序列化)

​ 提供严格的事务隔离。它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别,同时代价也花费最高,性能很低,一般很少使用,在该级别下,事务顺序执行,不仅可以避免脏读、不可重复读,还避免了幻像读。

​ 隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取,而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题,在可能出现这类问题的个别场合,可以由应用程序采用悲观锁或乐观锁来控制。大多数数据库的默认级别就是Read committed,比如Sql Server , Oracle。MySQL的默认隔离级别就是Repeatable read。

MySQL默认的事务隔离级

mysql默认的事务隔离级别为repeatable-read

查看默认的隔离级别:

show variables like ‘%tx_isolation%’;

1
2
3
4
5
6
7
8

mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)

事务问题重现

准备工作

1
2
3
4
5
6
7
8
9
CREATE TABLE `account`(  
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`balance` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
ALTER TABLE `account`
ADD UNIQUE INDEX `index_name` (`name`);
INSERT INTO `account`(NAME,balance)VALUE('张三',100),('李四',100),('王五',100),('赵六',100);

读未提交

​ 隔离级别最低,容易产生的问题就是脏读,因为可以读取其它事务修改了的但是没有提交的数据。它的作用跟在事务中 SELECT 语句对象表上设置 (NOLOCK) 相同。

​ 打开两个查询窗口,第一个窗口表示事务 A, 第二个窗口表示事务B。 事务A 保持默认的隔离级别,事务B 设置它们的隔离级别为 READ UNCOMMITTED。

执行时序
时间序号 事务A 事务B
1 start TRANSACTION;
– 开启事务
2 update account set balance = balance -50 where id = 1;
– 修改数据 未提交
3 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
– 设置隔离级别是读未提交
4 select * from account;
– 查询数据 发现读到了未提交的数据
5 ROLLBACK;
–回滚
6 select * from account;
– 查询数据 修改的数据不见了
执行事务A
1
2
3
4
5
6
mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance -50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
执行事务B
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
回滚事务A
1
2
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
查询事务B
1
2
3
4
5
6
7
8
9
10
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 100 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
总结

​ 可以看出,事务 B 对 ID = 1的这条数据进行了两次读取,但是很显然第一次读取的数据是脏数据。

读已提交

这是 SQL Server 的默认设置,已提交读,可以避免脏读,可以满足大多数要求。事务中的语句不能读取已由其它事务做出修改但是还未提交的数据,但是能够读取由其它事务做出修改并提交了的数据。也就是说,有可能会出现 Non-Repeatable Reads 不可重复读取和 Phantom Reads 幻读的情况,因为当前事务中可能出现两次读取同一资源,但是两次读取的过程之间,另外一事务可能对这一资源完成了读取更新并提交的行为,这样数据前后可能就不一致了。因此,这一个默认的隔离级别能够解决脏读但是解决不了 Non-Repeatable Reads 不可重复读。

执行时序
时间序号 事务A 事务B
1 start TRANSACTION;
– 开启事务
2 update account set balance = balance -50 where id = 1;
– 修改数据 未提交
3 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
– 设置隔离级别是读未提交
4 select * from account;
– 查询数据 发现未读取到改变的数据
5 COMMIT;
–回滚
6 select * from account;
– 查询数据 发现改变的数据读取到了
执行事务A
1
2
3
4
5
6
mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance -50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
执行事务B
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 100 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
回滚事务A
1
2
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
查询事务B
1
2
3
4
5
6
7
8
9
10
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
总结

​ 所以 READ COMMITTED 已提交读隔离级别能够避免脏读,但是仍然会遇到不可重复读取的问题。

可重复读

不能读取已由其它事务修改了但是未提交的行,其它任何事务也不能修改在当前事务完成之前由当前事务读取的数据。但是对于其它事务插入的新行数据,当前事务第二次访问表行时会检索这一新行。因此,这一个隔离级别的设置解决了 Non-Repeatable Reads 不可重复读取的问题,但是避免不了 Phantom Reads 幻读。

接着上面的例子做出一些修改,增加了一些查询,记得把 ID = 1001 的余额改回 1000。将事务 A 的隔离级别设置为 REPEATABLE READ 可重复读级别,来看看这个隔离级别的表现。

执行时序
时间序号 事务A 事务B
1 start TRANSACTION;
– 开启事务
2 select * from account;
– 查询数据
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
– 设置隔离级别是读未提交
3 start TRANSACTION;
– 开启事
4 INSERT INTO account(NAME,balance)VALUE(‘lili’,100);
– 插入一条数据
5 select * from account;
– 再次查询发现没有新增
6 INSERT INTO account(NAME,balance)VALUE(‘lili’,100);
– 插入数据发现进入等待状态
7 COMMIT;
– 提交事务
8 INSERT INTO account(NAME,balance)VALUE(‘lili’,100);
– 发现报index_name唯一索引冲突
9 select * from account;
– 发现还是没有查询到lili的数据
10 COMMIT;
– 提交事务
11 select * from account;
– 发现能够查询出lili
先执行事务A
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
执行事务B
1
2
3
4
5
6
7
8
mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> INSERT INTO `account`(NAME,balance)VALUE('lili',100);
Query OK, 1 row affected (0.00 sec
执行事务A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> INSERT INTO `account`(NAME,balance)VALUE('lili',100); -- 进入等待状态强制退出
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)
提交事务B
1
2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
执行事务A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> INSERT INTO `account`(NAME,balance)VALUE('lili',100);
ERROR 1062 (23000): Duplicate entry 'lili' for key 'index_name'

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
+----+--------+---------+
4 rows in set (0.00 sec)

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

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
| 7 | lili | 100 |
+----+--------+---------+
5 rows in set (0.00 sec)
总结

​ 我们发现在A事务提交前 lili插入不进去,也查询不出来,很像是幻象一样,一般这种情况叫幻读。

​ 很多人容易搞混不可重复读和幻读,确实这两者有些相似。但不可重复读重点在于update和delete,而幻读的重点在于insert。

​ 总的来说幻读就是事务A对数据进行操作,事务B还是可以用insert插入数据的,因为使用的是行锁,这样导致的各种奇葩问题就是幻读,表现形式很多,就不列举了。

可串行化

可串行化——SERIALIZABLE
事务的最高级别,在每个读的数据行上,加上锁,使之不可能相互冲突,因此,会导致大量的超时现象执行时序

时间序号 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
– 开启序列化
2 start TRANSACTION;
– 开启事务
select * from account;
– 查询数据
3 start TRANSACTION;
– 开启事务
4 select * from account;
– 正常查询
5 INSERT INTO account(NAME,balance)VALUE(‘lucy’,100);
– 进入等待状态
6 COMMIT;
– 提交事务
7 INSERT INTO account(NAME,balance)VALUE(‘lucy’,100);
– 正常插入数据
8 COMMIT;
– 提交事务
执行事务B
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
| 7 | lili | 100 |
+----+--------+---------+
5 rows in set (0.00 sec)
执行事务A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
| 7 | lili | 100 |
+----+--------+---------+
5 rows in set (0.00 sec)

mysql> INSERT INTO account(NAME,balance)VALUE('lucy',100); -- 进入等待状态强制退出
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
执行事务B
1
2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
执行事务A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> INSERT INTO account(NAME,balance)VALUE('lucy',100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 50 |
| 2 | 李四 | 100 |
| 3 | 王五 | 100 |
| 4 | 赵六 | 100 |
| 7 | lili | 100 |
| 11 | lucy | 100 |
+----+--------+---------+
6 rows in set (0.00 sec)

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

​ 可以看出,如果一个事务,使用了SERIALIZABLE——可串行化隔离级别时,在这个事务没有被提交之前
其他的线程,只能等到当前操作完成之后,才能进行操作,这样会非常耗时,而且,影响数据库的性能,通常情况下,不会使用这种隔离级别

评论