Mysql-事务

本质是一组不可分割SQL语句,所以事务不可以嵌套

1. 概念

●事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为--个整体一起向系统提交或撤销操作请求，即这组数据库命令要么都执行，要么都不执行。

●事务是-一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。

●事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。

●事务通过事务的整体性以保证数据的一致性。

●事务能够提高在向表中更新和插入信息期间的可靠性。

2. ACID

原子性Atomicity

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性Consistency

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

一致性问题
- 脏读
  
  当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外a个事务也访问这个数据，然后使用了这个数据。
- 不可重复读
  
  指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。( 即不能读到相同的数据内容)
- 幻读
  
  一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。
- 丢失更新
  
  两个事务同时读取同一条记录，A先修改记录，B也修改记录( B不知道A修改过)，B提交数据后B的修改结果覆盖了A的修改结果。
隔离性Isolation

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 隔离级别
  - Read Uncommitted（读取未提交内容）RU
    
    允许脏读，即允许一个事务可以看到其他事务未提交的修改。
  - Read Committed（读取提交内容）其他sql数据库的默认级别RC
    
    允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。防止脏读。
  - Repeatable Read（可重复读）mysql默认级别RR
    
    确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。可以防止脏读和不可重复读
  - Serializable（可串行化）
    
    完全串行化的读，将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁，读写相互都会阻塞。可以防止脏读，不可重复读取和幻读，(事务串行化)会降低数据库的效率。
持久性Durability

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3. 事务的查询与设置

查询全局事务隔离级别：global

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

查询会话事务隔离级别：session

show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

设置全局事务隔离级别

set global transaction isolation level <隔离级别>;

对所有会话生效

设置会话事务隔离级别
```
set session transaction isolation level read committed;
```
- 对当前会话生效，有会话事务隔离优先适用
事务控制语句
- BEGIN 或 START TRANSACTION：开始一个事务
- COMMIT 也可以使用 COMMIT WORK 事务确认
- ROLLBACK 也可以使用 ROLLBACK WORK 事务回滚
  
  回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
- SAVEPOINT identifier:创建回滚点（identifier是回滚点名称，自定义）
- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier :把事务回滚到标记点；
事务的自动提交功能

直接用 SET 来改变 MySQL 的自动提交模式:
```
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
```
mysql自动开启自动提交

即每一条sql语句都可以视为一个事务，当关闭自动提交后，必须通过commit才能完成一次数据提交
- 关闭了自动提交后，开启一个事务不再以begin开始。

4.事务隔离级别测试。

4.1 mysql默认隔离级别RR

4.1.1 当无主键的时候

现有一张数据表test_tb1。

mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
+------+--------+-------+
2 rows in set (0.00 sec)

现在使用两个终端连接数据库。

使用终端1查看全局会话事务隔离级别，为RR。（可重复读，即在开启一个事务后，使用select语句查看的效果一致，无论是否有其他事务提交）

##Centos7-1:1
mysql> show global variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

在终端1开启一个事务。

##Centos7-1:1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
+------+--------+-------+
2 rows in set (0.00 sec)

在终端2提交一个修改。

##Centos7-1:2
mysql> insert into test_tb1 values (3,'david',1500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
|    3 | david  |  1500 |
+------+--------+-------+
3 rows in set (0.00 sec)

在终端1使用select语句再次查看,select语句与原结果一致（事务尚未提交）

##Centos7-1:1
mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
+------+--------+-------+
2 rows in set (0.00 sec)

在终端1再次插入id为3的记录。

##Centos7-1:1
mysql> insert into test_tb1 values(3,'nike',1250);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
|    3 | nike   |  1250 |
+------+--------+-------+
3 rows in set (0.00 sec)

终端2再次select查看效果,记录3依旧为david。

##Centos7-1:2
mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
|    3 | david  |  1500 |
+------+--------+-------+
3 rows in set (0.00 sec)

终端1提交事务

##Centos7-1:1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

终端2再次使用select查看修改效果，出现两个id为3的记录。

mysql> select * from test_tb1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | nancy  |   500 |
|    2 | jackie |  1000 |
|    3 | david  |  1500 |
|    3 | nike   |  1250 |
+------+--------+-------+
4 rows in set (0.00 sec)

4.1.2 当id为主键的时候。

首先删除nike记录。并将id列设置为主键。

mysql> delete from test_tb1 where name='nike';
Query OK, 1 row affected (0.00 sec)

mysql> alter table test_tb1 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

在终端1开启一个事务,并插入一个id为4的记录。

##Centos7-1:1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_tb1 values (4,'xiaoming',2000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_tb1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | nancy    |   500 |
|  2 | jackie   |  1000 |
|  3 | david    |  1500 |
|  4 | xiaoming |  2000 |
+----+----------+-------+
4 rows in set (0.00 sec)

终端2插入id为4的记录，未插入成功，并陷入假死状态。一段时间后爆出1205错误

##Centos7-1:2
mysql> insert into test_tb1 values (4,'xiaohong',1750);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

终端1提交事务。并查看插入结果

##Centos7-1:1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tb1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | nancy    |   500 |
|  2 | jackie   |  1000 |
|  3 | david    |  1500 |
|  4 | xiaoming |  2000 |
+----+----------+-------+
4 rows in set (0.00 sec)

4.1.3 默认级别中记录的修改

终端1开始一个事务，修改nancy的money值为750。

##Centos7-1:1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from test_tb1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | nancy    |   750 |
|  2 | jackie   |  1000 |
|  3 | david    |  1500 |
|  4 | xiaoming |  2000 |
+----+----------+-------+
4 rows in set (0.00 sec)

终端2，将nancy的money值修改为650。无法修改，并显示锁等待时间超时。

##Centos7-1:2
mysql> select * from test_tb1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | nancy    |   500 |
|  2 | jackie   |  1000 |
|  3 | david    |  1500 |
|  4 | xiaoming |  2000 |
+----+----------+-------+
4 rows in set (0.00 sec)

mysql> update test_tb1 set money=650 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

将jackie的money值修改为1250。正常修改。

##Centos7-1:2
mysql> update test_tb1 set money=1250 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4.1.4 总结

在mysql默认隔离级别RR进行操作的时候，当事务对一行记录进行操作的时候，对该行记录会进行锁定，防止其他事务对该记录修改，从而导致记录的混乱，且在一个事务当中，仅能够看见自己事务中对表记录修改的状态，无法查看其他事务提交的修改结果。

posted @ 2022-05-31 10:09 残-云阅读(48) 评论(0) 编辑收藏举报

刷新页面返回顶部

残-云

Mysql-事务

Mysql-事务

1. 概念

2. ACID

3. 事务的查询与设置

4.事务隔离级别测试。

4.1 mysql默认隔离级别RR

4.1.1 当无主键的时候

4.1.2 当id为主键的时候。

4.1.3 默认级别中记录的修改

4.1.4 总结

公告