MySQL 死锁后事务无法回滚是真的吗？

MySQL
216
0
0
2024-01-14
MySQL 使用规范 —— 如何建好字段和索引
上一篇
MySQL字符集及其排序规则
下一篇
标签   MySQL事务 MySQL锁

MySQL 作为目前互联网企业使用最多的，或者说在基于成本下，最流行的数据库之一，MySQL 在国内使用者众多，那么在MySQL偶然安装后，在使用中出现死锁后，死锁中的事务到底能不能回滚 ？我们来进行相关的实验

我们先验证一遍

1 我们打开一个MySQL 版本为 8.027 官方版本

2 通过下面的操作我们可以确认两个分屏访问的是同一个MySQL的数据库

3 我们在其中建立一张表，并且插入数据

4 我们通过数据库的操作原理，产生了一个死锁条件，让下面的操作产生了死锁，并让数据通过自身的工作原理，解开了死锁。

5 然后我们对相关的表进行数据查询，看最终我们的数据表产生了什么最终的结果

6 我们再次验证，MySQL 数据库的隔离级别，我们并未选择MySQL的默认隔离级别 Repeatable Read ,而是我们大多数数据库包含Oracle 常用的 read committed

好了现在我们来捋一捋结果，到底是不是如PostgreSQL 老师们Diss ,MySQL 存在死锁时，部分提交的问题，并且违反了事务的ACID的特性。

我们先把A 面的操作都用文本列出来

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| orders         |
| payments       |
| test_deadlock  |
+----------------+
3 rows in set (0.00 sec)

mysql> drop table test_deadlock;
Query OK, 0 rows affected (0.04 sec)

mysql> create table test_deadlock (id int primary key,name varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_deadlock (id,name) values (1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_deadlock (id,name) values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

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

mysql> insert into test_deadlock(id,name) values (3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> update test_deadlock set name = 'd' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_deadlock set name = 'd' where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test_deadlock set name = 'd' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | d    |
|  2 | e    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

我们在把B面的文字给大家展示

mysql> 
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| orders         |
| payments       |
| test_deadlock  |
+----------------+
3 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| orders         |
| payments       |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

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

mysql> 
mysql> 
mysql> 
mysql> update test_deadlock set name = 'e' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_deadlock set name = 'd' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | d    |
|  2 | e    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

结论，的确MySQL 在发生死锁的情况下，会有事务部分提交的问题，从上面的图和文本可以看出，A 事务中，插入数据和对数据第一行的修改，均生效了，而按照数据库的事务部分的既定原理，这是不可以的，事务要么回滚，要么全部执行。

实际上，这个问题和死锁本身关系不大，我们通过这样一个情景可以再次验证问题

我们在把文字进行展示

A 操作后的结果

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

mysql> delete from test_deadlock where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_deadlock (id,name) values (3,'d');
ERROR 1062 (23000): Duplicate entry '3' for key 'test_deadlock.PRIMARY'
mysql> 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | d    |
|  2 | e    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

B 操作后的结果

1 row in set (0.00 sec)

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

mysql> delete from test_deadlock where id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_deadlock (id,name) values (3,'d');
ERROR 1062 (23000): Duplicate entry '3' for key 'test_deadlock.PRIMARY'
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  2 | e    |
|  3 | c    |
+----+------+
2 rows in set (0.00 sec)

从上的结果看，实际上在事务出现操作错误后，按照数据库原理是应该全部回滚的，而B 操作中，我们commit 是会部分进行提交的，也就是把错误的操作不提交，而正确的操作提交。

结论最终的结果是------是的MySQL 在事务操作中，并不是按照我们认为的数据库原理进行事务的操作的，或者更准确的说，一部分不是我们认为的那样。

这里我们也有其他类似的设计，这个数据库就是 SQL SERVER ，使用过SQL SERVER 的小伙伴，不知道注意到没有SQL SERVER 本身默认安装后，也是和MySQL 一样的，事务错误后会部分提交事务中错误发生前正确的部分。

这样的设计有一种说法，MySQL本身就不是给金融类产品使用的，而是定位于WEB 类型的数据库产品，而web 类的数据库产品本身就更具有灵活性，同时需要更快的速度，将很多问题都简化和改变的情况，这里我们可以从MySQL 在处理事务的和存储过程的建议，或者说民间使用MySQL的建议，不建议大事务，不建议存储过程大量的在业务中使用的这个‘口耳相传’的论调,找寻到一些最初MySQL 被设计出来的最初的初衷。

所以数据库本身是有定位的，不是一个数据库走天下，不同的数据库定位的业务不同，定位的使用者和业务的逻辑不同。当然事务不完全回滚对于使用者本身是不是一个问题，具体我们需要看业务的设定是不是允许或接受，如同MySQL 本身也在表设计时也不希望遵循 三范式一样。MySQL 是一个反传统的数据库产品。

但是 但是 但是 MySQL 可以解决这个问题，我们有参数可以解决。这点和SQL SERVER 是一样的。

经过我们的调整MySQL的参数后，MySQL 满足了我们传统的数据库对于事务中的要求，要么全回滚，要么全不回滚。

A 和 B 事务代码，事务A 中的插入是没有生效的，从而证明MySQL 完全可以实现在死锁后死锁事务的全部回滚。顺便打一个广告 2023-08-22日晚8点，大家可以了解PolarDB 了解一下，我们请来PolarDB的基础架构研发负责人 来回答大家对于PolarDB数据库的问题，吃不了亏，上不了当 ！（具体参见地址在文章最下方）

备注：关于SQL SERVER 部分提交的问题，感兴趣加群，告诉你怎么解决

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

mysql> insert into test_deadlock(id,name) values (4,'g');
Query OK, 1 row affected (0.00 sec)

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

mysql> update test_deadlock set name = 'e' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | d    |
|  2 | e    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)


mysql> 
mysql> select * from test_deadlock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | e    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

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

mysql> update test_deadlock set name = 'e' where id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

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

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL 使用规范 —— 如何建好字段和索引
上一篇
MySQL字符集及其排序规则
下一篇