mysql 有长时间未提交的事务怎么处理

转载

mob6454cc6d3e23 2024-10-31 18:13:05

文章标签 当前事务无法提交版本号隔离级别加锁 文章分类 MySQL 数据库

mysql 有长时间未提交的事务怎么处理_当前事务无法提交

事务：就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功的对数据库应用改组查询的全部语句，那么就执行该族语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。换句话说就是，事务内的语句，要么全部执行成功，要么全部执行失败。

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表。现在要从用户张三的支票账户转移￥200到他的储蓄账户中，那么需要至少三个步骤：

检查支票账户的余额是否高于￥200；
从支票账户余额中减去￥200；
在储蓄账户余额中增加￥200；

上述三个步骤的操作必须在一个食物中，任何一个步骤失败，则必须回滚搜有步骤。

可使用START TRANSACTION 语句开始一个事务，然后要么使用COMMIT提交事务，要么使用ROLLBACK回滚事务。

START TRANSACTION；
SELECT　balance FROM checking WHERE customer_id=10233276;
UPDATE checking SET balance = balance - 200 WHERE customer_id= 10233276;
UPDATE savings SET balance = balance + 200 WHERE customer_id= 10233276;
COMMIT;

事务的ACID

原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态。

隔离性（isolation）：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

持久性（durability）：一旦事务提交，则其所作的修改就会永久保存到数据库中。

事务的ACID特性可以确保银行不会弄丢你的钱。

四种隔离级别

READ UNCOMMITTED（未提交读）：在该级别，事务中的修改即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，称为脏读（Dirty Read）。实际应用中很少使用。
READ COMMITTED（提交读）：大多数数据库系统的默认隔离级别都是该级别一个事务从开始直到提交之前，所做的任何修改对其他事务都不可见。该级别在执行两次同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）：解决了脏读的问题，保证了在同一事物中多次读取同样记录的结果是一致的。但无法避免幻读。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外的事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB通过多版本控制（mvcc）解决了幻读的问题。
SERIALIZATBLE（可串行化）：最高的隔离级别，通过强制事务的串行化，避免了前面所说的幻读的问题。简单来说，SERIALIZATBLE会再读取的每一行数据上都加锁，可能会导致大量的超时和锁竞争的问题，实际应用中很少使用。

锁机制：按照维度区分

类型维度

共享锁（读锁 / S 锁）
排它锁（写锁 / X 锁）
类型细分：

意向共享锁
意向排他（互斥）锁

悲观锁（使用锁，即 for update）
乐观锁（使用版本号字段，类似 CAS 机制，即用户自己控制。缺点：并发很高的时候，多了很多无用的重试）

2. 锁的粒度（粒度维度）

表锁（table lock）:MySQL中最基本的锁策略，并且是开销最小的策略。在特定场景中，表锁也可能有良好的性能。例如，READ LOCAL表锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。
页锁（Mysql BerkeleyDB 引擎）
行锁（InnoDB）：行级锁可以最大程度的支持并发处理（同时带来了最大的锁开销）。行级锁只在存储引擎层实现。

3. 锁的算法（算法维度）

Record Lock（单行记录）
Gap Lock（间隙锁，锁定一个范围，但不包含锁定记录）
Next-Key Lock（Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身， MySql 防止幻读，就是使用此锁实现）

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都会同时实现了多版本并发控制（MVCC）。

MVCC可以认为是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也能只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事物看到的数据都是一致的。根据事务开始的时间不同，每个事物对同一张表，同一时刻看到的数据可能是不一样的。

不同存储引擎的MVCC是现实不同的，典型的有乐观并发控制和悲观并发控制。我们通过InnoDB来说明MVCC是如何工作的。

InnoDB的MVCC，是通过在每行记录后i按保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面是在REPEARABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录：