MySQL 死锁处理

本文主要是介绍MySQL 死锁处理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、手动开启/提交事务语句
start transaction 和 begin的区别：
两者的作用一摸一样，只是在begin可能成为关键字的时候，使用start transaction 可以 避免这种情况，start transaction或者begin开启一个事务，然后使用commit提交事务或者ROLLBACK回滚事务

在默认情况下，用户执行的每一条SQL语句都会被当成单独的事务自动提交。如果要将一组SQL语句作为一个事务，则需要先执行以下语句显式地开启一个事务。

START TRANSACTION;

Begin;

上述语句执行后，每一条SQL语句不再自动提交，用户需要使用以下语句手动提交，只有事务提交后，其中的操作才会生效。

COMMIT;

如果不想提交当前事务，可以使用如下语句取消事务（即回滚）。

ROLLBACK;

2、并发事务引起的问题

1. 脏读：

一个事务读取到另个事务还没有提交的数据

2. 不可重复读：

一个事务分两次读取某个数据，前后两次读取数据不一致

3. 幻读：

一个事务读取某个数据时，并没有该数据，但插入时发现已经存在

查看事务隔离级别

# 查看全局隔离级
SELECT @global.transaction_isolation;
# 查看当前会话中的隔离级
SELECT @@session.transaction_isolation;
# 查看下一个事务的隔离级
SELECT @@transaction_isolation;

读未提交（脏读）

READ UNCOMMITTED 是事务中最低的级别，在该级别下的事务可以读取到其他事务中未提交的数据，这种读取的方式也被称为脏读（Dirty Read）。简而言之，脏读是指一个事务读取了另外一个事务未提交的数据。

读已提交（不可重复读）

READ COMMITTED 是大多数 DBMS (如 SQL Server、Oracle) 的默认隔离级，但不包括MySQL。

在该隔离级下只能读取其他事务已经提交的数据，避免了脏读数据的现象。但是在该隔离级别下，会出现不可重复读（NON-REPEATABLE READ）的问题。

可重复读

REPEATABLE READ 是MySQL的默认事务隔离级，它解决了脏读和不可重复读的问题，确保了同一事务的多个实例在并发读取数据时，会看到同样的结果。但在理论上，该隔离级会出现幻读（PHANTOM READ)的现象。

幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不可重复读有些类似，同样发生在两次查询过程中。不同的是，幻读是由于其他事务做了插入记录的操作，导致记录数有所增加。不过，MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读的问题。

串型化

SERIALIZABLE 是最高级别的隔离级，它在每个读的数据行上加锁，使之不会发生冲突，从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时（Timeout) 和 锁竞争（Lock Contention)现象，因此 SERIALIZABLE 也是性能最低的一种隔离级。除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级。

阿里云 PolarDB MySQL版支持READ_UNCOMMITTED、READ_COMMITTED（默认）、REPEATABLE_READ这三种隔离级别，不支持SERIALIZABLE隔离级别。

5、查询慢SQL

show processlist;information_schema.innodb_trxperformance_schema.events_statements_current

6、查看DDL语句执行状态和MDL锁状态。

在DDL语句执行过程中，通过执行以下命令查看performance_schema.events_stages_current表，可以获取当前DDL语句的执行状态：

SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current;

查询结果如下：

+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
| THREAD_ID | EVENT_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
|   3057989 |       13 | stage/innodb/alter table (read PK and internal sort) |          56634 |         330135 |  17.1548 |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
1 row in set (0.00 sec)

借助表performance_schema.threads和information_schema.PROCESSLIST，执行以下命令，您可以查看当前事件对应的SQL语句。

SELECT esc.THREAD_ID, esc.EVENT_NAME, esc.WORK_COMPLETED, esc.WORK_ESTIMATED, pl.INFO FROM performance_schema.events_stages_current esc LEFT JOIN performance_schema.threads th ON esc.thread_id = th.thread_id LEFT JOIN information_schema.PROCESSLIST pl ON th.PROCESSLIST_ID = pl.ID;

查询结果如下：

+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
| THREAD_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | INFO                                                                                    |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
|   3057989 | stage/innodb/alter table (read PK and internal sort) |          77034 |         330519 | ALTER TABLE test.test ALGORITHM=INPLACE, ADD testA VARCHAR(20) NOT NULL DEFAULT 'testA' |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过执行以下命令查看表 performance_schema.metadata_locks，可以获取当前集群中MDL锁的使用情况：

SELECT * FROM performance_schema.metadata_locks;

 查询结果如下：

+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME      | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE                 | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL             | NULL        |       139949462878336 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:3103       |         3055785 |              1 |
| TABLE       | test               | test             | NULL        |       139931318980224 | SHARED_WRITE        | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3055785 |              1 |
| COMMIT      | NULL               | NULL             | NULL        |       139931318980480 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | handler.cc:1669        |         3055785 |              1 |
| TABLE       | performance_schema | metadata_locks   | NULL        |       139934227366144 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057612 |              1 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216849664 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5519       |         3057989 |             13 |
| SCHEMA      | test               | NULL             | NULL        |       139934216849408 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5506       |         3057989 |             13 |
| TABLE       | test               | test             | NULL        |       139934216848640 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057989 |             13 |
| BACKUP LOCK | NULL               | NULL             | NULL        |       139934216849280 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5526       |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216848384 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:815            |         3057989 |             13 |
| TABLE       | test               | #sql-17d9_2ea89a | NULL        |       139934216848896 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:15054     |         3057989 |             13 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216850176 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dictionary_impl.cc:416 |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216849920 | EXCLUSIVE           | TRANSACTION   | GRANTED     | dictionary_impl.cc:397 |         3057989 |             13 |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
12 rows in set (0.00 sec)

通过OWNER_THREAD_ID字段，执行以下命令查看表performance_schema.threads中持有MDL锁的线程信息。

SELECT * FROM performance_schema.threads WHERE THREAD_ID = "OWNER_THREAD_ID in performance_schema.metadata_locks table ";

7、终止慢SQL

先查看慢SQL的ID，然后执行 kill <Id> 终止慢SQL。

7、在同一个PolarDB MySQL版的数据库里复制一个数据量很大的表（如将整张表A复制到表B中），比较合适的方式可以使用如下SQL语句直接复制：

create table B as select * from A

这篇关于MySQL 死锁处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---