MySQL锁等待与死锁根治的全攻略

show processlist;

select
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread_id,
  r.trx_query waiting_sql,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread_id,
  b.trx_query blocking_sql,
  b.trx_started blocking_trx_start_time
from
  performance_schema.data_lock_waits w
  inner join information_schema.innodb_trx b on w.blocking_engine_transaction_id = b.trx_id
  inner join information_schema.innodb_trx r on w.requesting_engine_transaction_id = r.trx_id;

select
  engine_transaction_id trx_id,
  object_schema db_name,
  object_name table_name,
  index_name,
  lock_type,
  lock_mode,
  lock_data,
  lock_status
from
  performance_schema.data_locks;

create table `t_user` (
  `id` bigint not null auto_increment comment '主键id',
  `user_name` varchar(64) not null comment '用户名',
  `age` int not null comment '年龄',
  primary key (`id`),
  unique key `uk_user_name` (`user_name`)
) engine=innodb default charset=utf8mb4 comment='用户表';
insert into `t_user` (`id`, `user_name`, `age`) values (1, '张三', 20), (2, '李四', 25);

------------------------
latest detected deadlock
------------------------
2026-04-08 10:00:00 0x7f8a1b2c3700
*** (1) transaction:
transaction 42107, active 10 sec starting index read
mysql tables in use 1, locked 1
lock wait 2 lock struct(s), heap size 1136, 1 row lock(s)
mysql thread id 12, os thread handle 140230523201280, query id 245 localhost root updating
update t_user set age=22 where id=2
*** (1) waiting for this lock to be granted:
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42107 lock_mode x locks rec but not gap waiting
record lock, heap no 3 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 00000000a47b; asc      {;;
 2: len 7; hex 81000001100110; asc        ;;
 3: len 4; hex 8000001a; asc     ;;
 4: len 2; hex e69d8e; asc   ;;

*** (2) transaction:
transaction 42108, active 5 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 2 row lock(s)
mysql thread id 13, os thread handle 140230522930944, query id 246 localhost root updating
update t_user set age=27 where id=1
*** (2) holds the lock(s):
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42108 lock_mode x locks rec but not gap
record lock, heap no 3 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 00000000a47b; asc      {;;
 2: len 7; hex 81000001100110; asc        ;;
 3: len 4; hex 8000001a; asc     ;;
 4: len 2; hex e69d8e; asc   ;;

*** (2) waiting for this lock to be granted:
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42108 lock_mode x locks rec but not gap waiting
record lock, heap no 2 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000000a47a; asc      z;;
 2: len 7; hex 820000010f0120; asc         ;;
 3: len 4; hex 80000014; asc     ;;
 4: len 3; hex e5bca0e4b889; asc       ;;

*** we roll back transaction (1)

2026-04-08 10:00:00 0x7f8a1b2c3700

*** (1) transaction:
transaction 42107, active 10 sec starting index read
mysql tables in use 1, locked 1
lock wait 2 lock struct(s), heap size 1136, 1 row lock(s)
mysql thread id 12, os thread handle 140230523201280, query id 245 localhost root updating
update t_user set age=22 where id=2

*** (1) waiting for this lock to be granted:
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42107 lock_mode x locks rec but not gap waiting
record lock, heap no 3 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;

*** (2) holds the lock(s):
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42108 lock_mode x locks rec but not gap
record lock, heap no 3 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;

*** (2) waiting for this lock to be granted:
record locks space id 26 page no 4 n bits 72 index primary of table `test`.`t_user` trx id 42108 lock_mode x locks rec but not gap waiting
record lock, heap no 2 physical record: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;

*** we roll back transaction (1)

<?xml version="1.0" encoding="utf-8"?>
<project xmlns="http://maven.apache.org/pom/4.0.0" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"
         xsi:schemalocation="http://maven.apache.org/pom/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelversion>4.0.0</modelversion>
    <parent>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-starter-parent</artifactid>
        <version>3.2.5</version>
        <relativepath/>
    </parent>
    <groupid>com.jam.demo</groupid>
    <artifactid>mysql-lock-demo</artifactid>
    <version>0.0.1-snapshot</version>
    <name>mysql-lock-demo</name>
    <description>mysql锁与死锁实战demo</description>
    <properties>
        <java.version>17</java.version>
        <mybatis-plus.version>3.5.7</mybatis-plus.version>
        <fastjson2.version>2.0.52</fastjson2.version>
        <guava.version>33.1.0-jre</guava.version>
    </properties>
    <dependencies>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</artifactid>
        </dependency>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-jdbc</artifactid>
        </dependency>
        <dependency>
            <groupid>com.baomidou</groupid>
            <artifactid>mybatis-plus-boot-starter</artifactid>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupid>org.springdoc</groupid>
            <artifactid>springdoc-openapi-starter-webmvc-ui</artifactid>
            <version>2.5.0</version>
        </dependency>
        <dependency>
            <groupid>com.mysql</groupid>
            <artifactid>mysql-connector-j</artifactid>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupid>org.projectlombok</groupid>
            <artifactid>lombok</artifactid>
            <version>1.18.30</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupid>com.alibaba.fastjson2</groupid>
            <artifactid>fastjson2</artifactid>
            <version>${fastjson2.version}</version>
        </dependency>
        <dependency>
            <groupid>com.google.guava</groupid>
            <artifactid>guava</artifactid>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-test</artifactid>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupid>org.springframework.boot</groupid>
                <artifactid>spring-boot-maven-plugin</artifactid>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupid>org.projectlombok</groupid>
                            <artifactid>lombok</artifactid>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

package com.jam.demo.entity;
import com.baomidou.mybatisplus.annotation.idtype;
import com.baomidou.mybatisplus.annotation.tableid;
import com.baomidou.mybatisplus.annotation.tablename;
import io.swagger.v3.oas.annotations.media.schema;
import lombok.data;
import java.io.serial;
import java.io.serializable;
/**
 * 用户实体类
 * @author ken
 */
@data
@tablename("t_user")
@schema(description = "用户实体")
public class user implements serializable {
    @serial
    private static final long serialversionuid = 1l;
    @tableid(type = idtype.auto)
    @schema(description = "主键id", example = "1")
    private long id;
    @schema(description = "用户名", example = "张三")
    private string username;
    @schema(description = "年龄", example = "20")
    private integer age;
}

package com.jam.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.basemapper;
import com.jam.demo.entity.user;
import org.apache.ibatis.annotations.param;
import org.apache.ibatis.annotations.update;
/**
 * 用户mapper接口
 * @author ken
 */
public interface usermapper extends basemapper<user> {
    /**
     * 根据id更新用户年龄
     * @param id 用户id
     * @param age 新年龄
     * @return 影响行数
     */
    @update("update t_user set age = #{age} where id = #{id}")
    int updateagebyid(@param("id") long id, @param("age") integer age);
}

package com.jam.demo.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.serviceimpl;
import com.jam.demo.entity.user;
import com.jam.demo.mapper.usermapper;
import com.jam.demo.service.userservice;
import lombok.extern.slf4j.slf4j;
import org.springframework.stereotype.service;
import org.springframework.transaction.platformtransactionmanager;
import org.springframework.transaction.transactionstatus;
import org.springframework.transaction.support.defaulttransactiondefinition;
import org.springframework.util.objectutils;
import jakarta.annotation.resource;
/**
 * 用户服务实现类
 * @author ken
 */
@slf4j
@service
public class userserviceimpl extends serviceimpl<usermapper, user> implements userservice {
    @resource
    private platformtransactionmanager transactionmanager;
    @resource
    private usermapper usermapper;
    @override
    public void crossupdatefirst(long id1, long id2, integer age1, integer age2) {
        if (objectutils.isempty(id1) || objectutils.isempty(id2)) {
            throw new illegalargumentexception("用户id不能为空");
        }
        defaulttransactiondefinition def = new defaulttransactiondefinition();
        def.setpropagationbehavior(defaulttransactiondefinition.propagation_required);
        transactionstatus status = transactionmanager.gettransaction(def);
        try {
            log.info("事务a：开始更新id={}的用户年龄", id1);
            usermapper.updateagebyid(id1, age1);
            thread.sleep(1000);
            log.info("事务a：开始更新id={}的用户年龄", id2);
            usermapper.updateagebyid(id2, age2);
            transactionmanager.commit(status);
            log.info("事务a：执行完成，事务提交");
        } catch (interruptedexception e) {
            transactionmanager.rollback(status);
            thread.currentthread().interrupt();
            log.error("事务a：线程中断，事务回滚", e);
            throw new runtimeexception("更新失败，线程中断", e);
        } catch (exception e) {
            transactionmanager.rollback(status);
            log.error("事务a：更新异常，事务回滚", e);
            throw new runtimeexception("更新失败", e);
        }
    }
    @override
    public void crossupdatesecond(long id1, long id2, integer age1, integer age2) {
        if (objectutils.isempty(id1) || objectutils.isempty(id2)) {
            throw new illegalargumentexception("用户id不能为空");
        }
        defaulttransactiondefinition def = new defaulttransactiondefinition();
        def.setpropagationbehavior(defaulttransactiondefinition.propagation_required);
        transactionstatus status = transactionmanager.gettransaction(def);
        try {
            log.info("事务b：开始更新id={}的用户年龄", id2);
            usermapper.updateagebyid(id2, age2);
            thread.sleep(1000);
            log.info("事务b：开始更新id={}的用户年龄", id1);
            usermapper.updateagebyid(id1, age1);
            transactionmanager.commit(status);
            log.info("事务b：执行完成，事务提交");
        } catch (interruptedexception e) {
            transactionmanager.rollback(status);
            thread.currentthread().interrupt();
            log.error("事务b：线程中断，事务回滚", e);
            throw new runtimeexception("更新失败，线程中断", e);
        } catch (exception e) {
            transactionmanager.rollback(status);
            log.error("事务b：更新异常，事务回滚", e);
            throw new runtimeexception("更新失败", e);
        }
    }
}

package com.jam.demo.controller;
import com.jam.demo.service.userservice;
import io.swagger.v3.oas.annotations.operation;
import io.swagger.v3.oas.annotations.parameter;
import io.swagger.v3.oas.annotations.tags.tag;
import lombok.extern.slf4j.slf4j;
import org.springframework.web.bind.annotation.postmapping;
import org.springframework.web.bind.annotation.requestmapping;
import org.springframework.web.bind.annotation.requestparam;
import org.springframework.web.bind.annotation.restcontroller;
import jakarta.annotation.resource;
import java.util.concurrent.countdownlatch;
/**
 * 用户控制器
 * @author ken
 */
@slf4j
@restcontroller
@requestmapping("/user")
@tag(name = "用户管理", description = "用户相关操作接口")
public class usercontroller {
    @resource
    private userservice userservice;
    @postmapping("/deadlock/simulate")
    @operation(summary = "模拟交叉更新死锁场景", description = "并发执行两个交叉更新的事务，触发死锁")
    public string simulatedeadlock(
            @parameter(description = "第一个用户id", example = "1") @requestparam long id1,
            @parameter(description = "第二个用户id", example = "2") @requestparam long id2,
            @parameter(description = "第一个用户新年龄", example = "21") @requestparam integer age1,
            @parameter(description = "第二个用户新年龄", example = "26") @requestparam integer age2
    ) {
        countdownlatch countdownlatch = new countdownlatch(2);
        new thread(() -> {
            try {
                userservice.crossupdatefirst(id1, id2, age1, age2 + 1);
            } catch (exception e) {
                log.error("线程1执行异常", e);
            } finally {
                countdownlatch.countdown();
            }
        }, "deadlock-thread-1").start();
        new thread(() -> {
            try {
                userservice.crossupdatesecond(id1, id2, age1 + 1, age2);
            } catch (exception e) {
                log.error("线程2执行异常", e);
            } finally {
                countdownlatch.countdown();
            }
        }, "deadlock-thread-2").start();
        try {
            countdownlatch.await();
        } catch (interruptedexception e) {
            thread.currentthread().interrupt();
            return "模拟中断";
        }
        return "死锁模拟完成，请查看mysql死锁日志";
    }
}

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useunicode=true&characterencoding=utf8&usessl=false&servertimezone=asia/shanghai&allowpublickeyretrieval=true
    username: root
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.driver
  application:
    name: mysql-lock-demo
mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.stdoutimpl
springdoc:
  swagger-ui:
    path: /swagger-ui.html
    enabled: true
  api-docs:
    enabled: true
    path: /v3/api-docs
server:
  port: 8080

@override
public void crossupdatesecond(long id1, long id2, integer age1, integer age2) {
    if (objectutils.isempty(id1) || objectutils.isempty(id2)) {
        throw new illegalargumentexception("用户id不能为空");
    }
    defaulttransactiondefinition def = new defaulttransactiondefinition();
    def.setpropagationbehavior(defaulttransactiondefinition.propagation_required);
    transactionstatus status = transactionmanager.gettransaction(def);
    try {
        // 统一更新顺序：先更新id较小的记录，再更新id较大的记录
        long firstid = math.min(id1, id2);
        long secondid = math.max(id1, id2);
        integer firstage = firstid.equals(id1) ? age1 : age2;
        integer secondage = secondid.equals(id2) ? age2 : age1;
        log.info("事务b：开始更新id={}的用户年龄", firstid);
        usermapper.updateagebyid(firstid, firstage);
        thread.sleep(1000);
        log.info("事务b：开始更新id={}的用户年龄", secondid);
        usermapper.updateagebyid(secondid, secondage);
        transactionmanager.commit(status);
        log.info("事务b：执行完成，事务提交");
    } catch (interruptedexception e) {
        transactionmanager.rollback(status);
        thread.currentthread().interrupt();
        log.error("事务b：线程中断，事务回滚", e);
        throw new runtimeexception("更新失败，线程中断", e);
    } catch (exception e) {
        transactionmanager.rollback(status);
        log.error("事务b：更新异常，事务回滚", e);
        throw new runtimeexception("更新失败", e);
    }
}