关于数据库事务的知识点归纳

wuweixiang

发布于 2019-03-12 11:35:34

6120

发布于 2019-03-12 11:35:34

文章被收录于专栏：吴伟祥吴伟祥

1 什么是事务？

事务是逻辑上的一组操作，这组操作要么全部成功，要么全部失败

2 事务具有四大特性ACID

* 原子性（Atomicity）
 事务是最小的工作单元，不可再分

* 一致性（Consistency）
 事务要求的DML语句操作的时候，必须保证同时成功或者同时失败  # DML  Manipulation 数据操纵语言 用于改变数据库数据  1) 插入：INSERT  2) 更新：UPDATE 3) 删除：DELETE 

* 隔离性（Isolation）
 事务A和事务B之间具有隔离

* 持久性（Durability）
 是事务的保证，事务终结的标志。【内存中的数据持久到硬盘文件中】

3 关于事务的一些术语

- 开启事务：start transaction
- 提交事务：commit transaction       
- 回滚事务：rolback transaction
- 事务结束：end transaction

4 事务控制语言TCL

commit; 提交
rollback; 回滚
savepoint;      # TCL  用于维护数据的一致性

5 事务开启的标志是什么？事务结束的标志是什么？

5.1 事务开启的标志

任何一条DML语句执行，标志事务的开启

5.2 事务结束的标志

提交或者回滚

6 事务自动提交机制的关闭方式

在MySQL数据库管理系统中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句，开启了事务，并且提交了事务。

mysql> show variables like '%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
| sql_auto_is_null            | OFF   |
+-----------------------------+-------+
8 rows in set, 1 warning (0.01 sec)

6.1 关闭自动提交的第一种方式

start transaction;    手动开启事务

DML语句…

DML语句…

DML语句…

commit;     手动提交事务【事务成功的结束】

start transaction;    手动开启事务

DML语句…

DML语句…

DML语句…

rollback;     手动回滚事务【事务失败的结束】

6.2 关闭自动提交的第二种方式

# 以下关闭和打开自动提交机制，只对当前会话有效。

set autocommit = off;
或者
set session autocommit = off;

set autocommit = on;
或者
set session autocommit = on;

7 事务的隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

7.1 查看隔离级别

# 查看当前会话的隔离级别：
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

# 查看全局的事务隔离级别：
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |             # MySQL数据库管理系统默认的隔离级别：
+-----------------------+
1 row in set, 1 warning (0.00 sec)

7.1.1 read uncommitted 读未提交

- 事务A和事务B，事务A未提交的数据，事务B可以读取到。
- 这里读取到的数据可以叫做“脏数据”或者叫做“Dirty Read”
- 这种隔离级别是最低级别，这种级别一般都是理论上存在的，数据库默认的隔离级别一般都是高于该隔离级别的。

7.1.2 read committed 读已提交

- 事务A和事务B，事务A提交的数据，事务B才能读取到。
- 这种隔离级别高于上面的读未提交
- 换句话说：对方事务提交之后的数据，我当前事务才能够读取到
- 这种隔离级别可以避免脏数据
- 这种隔离级别会导致：“不可重复读取”
- Oracle数据库管理系统默认的隔离级别：读已提交

7.1.3 repeatable read 可重复读

- 事务A和事务B，事务A提交之后的数据，事务B读取不到。
- 事务B是可重复读取数据的。
- 这种隔离级别高于读已提交。
- 换句话说：对方提交之后的数据我还是读取不到。
- 这种隔离级别可以避免“不可重复读”，达到可重复读取。
- MySQL数据库管理系统默认的隔离级别：可重复读
- 虽然可以达到“可重复读”的结果，但是会导致：“幻象读”

7.1.4 serializable 串行化

- 事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待。
- 这种隔离级别一般很少使用，吞吐量太低。用户体验不好。
- 这种隔离级别可以避免“幻象读”，每一次读取的都是数据库表中真实的记录。
- 事务A和事务B 不再并发，是串行执行的。

7.2 设置事务的隔离级别

7.2.1 第一种方式：修改my.ini配置文件

可选值：
-  READ-UNCOMMITTED
-  READ-COMMITTED
-  REPEATABLE-READ
-  SERIALIZABLE

-------------------my.ini---------------------
[mysqld]
transaction-isolation = READ-COMMITTED
-------------------my.ini---------------------

7.2.2 第二种方式：使用命令方式设置事务的隔离级别

命令格式：
set [无/session/global] transaction isolation level <isolation-level>;
<isolation-level>可选值：
-  READ-UNCOMMITTED
-  READ-COMMITTED
-  REPEATABLE-READ
-  SERIALIZABLE
设置事务的隔离级别作用于全局：
set global transaction isolation level <level>;
设置事务的隔离级别作用于当前会话：
set transaction isolation level <level>;
或者
set session transaction isolation level <level>;

7.3 并发事务与隔离级别示例

7.3.1 第一级别：读未提交 read uncommitted

mysql> set global transaction isolation level read uncommitted;

①会话1

mysql> use test;
Database changed

mysql> select * from tx1;
Empty set (0.00 sec)

mysql> start transaction;  # 手动开启事务A，等于关闭当前的自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`.`tx1`(`id`, `c1`, `c2`) VALUES (1, 'a', 'a');
Query OK, 1 row affected (0.00 sec)

②会话2

mysql> select * from tx1;  
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    | a    |
+----+------+------+
1 row in set (0.00 sec)

③会话1

mysql> update tx1 set c1='aa',c2='bb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

④会话2

mysql> select * from tx1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aa   | bb   |               # 脏读  意味着事务A仍未提交，但它任何一条数据变化，其它事务都可以看到
+----+------+------+
1 row in set (0.00 sec)

7.3.2 第二级别：读已提交 read committed Oracle

这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。导致原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

mysql> set global transaction isolation level read committed;

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

①会话1

mysql> use test;
Database changed

mysql> select * from tx1;
Empty set (0.00 sec)

mysql> start transaction;  # 手动开启事务，等于关闭当前的自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`.`tx1`(`id`, `c1`, `c2`) VALUES (1, 'a', 'a');
Query OK, 1 row affected (0.00 sec)

②会话2

mysql> use test;
Database changed
mysql> select * from tx1;
Empty set (0.01 sec)

③会话1

# 继续在会话1窗口

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

④会话2

mysql> select * from tx1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    | a    |
+----+------+------+
1 row in set (0.00 sec)

7.3.3 第三级别：可重复读 repeatable read MySQL

此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

①会话1

mysql> use test; 
Database changed 

mysql> select * from tx1; 
Empty set (0.00 sec) 

mysql> start transaction; # 手动开启事务，等于关闭当前的自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`.`tx1`(`id`, `c1`, `c2`) VALUES (1, ' REPEATABLE-READ', ' REPEATABLE-READ');
Query OK, 1 row affected (0.00 sec)

②会话2

mysql> use test;
Database changed

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

③会话1

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

④会话2

mysql> select * from tx1;    # 会话1 已经提交了，仍然无法读到内容。  说明级别3 解决了 不可重复读 的问题
Empty set (0.00 sec)

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

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

mysql> select * from tx1;           # 只有当会话2提交了，它才能够看到数据变化
+----+------------------+------------------+
| id | c1               | c2               |
+----+------------------+------------------+
|  1 |  REPEATABLE-READ |  REPEATABLE-READ |
+----+------------------+------------------+
1 row in set (0.00 sec)

7.3.4 第四级别：可串行化 Serializable

(1)这是最高的隔离级别 (2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。 (3)在这个级别，可能导致大量的超时现象和锁竞争

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE          |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

数据库

sql

登录后参与评论

0 条评论

热度