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鎖與死鎖實戰(zhàn)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> {
    /**
     * 根據(jù)ID更新用戶年齡
     * @param id 用戶ID
     * @param age 新年齡
     * @return 影響行數(shù)
     */
    @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;
/**
 * 用戶服務(wù)實現(xiàn)類
 * @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("事務(wù)A：開始更新id={}的用戶年齡", id1);
            userMapper.updateAgeById(id1, age1);
            Thread.sleep(1000);
            log.info("事務(wù)A：開始更新id={}的用戶年齡", id2);
            userMapper.updateAgeById(id2, age2);
            transactionManager.commit(status);
            log.info("事務(wù)A：執(zhí)行完成，事務(wù)提交");
        } catch (InterruptedException e) {
            transactionManager.rollback(status);
            Thread.currentThread().interrupt();
            log.error("事務(wù)A：線程中斷，事務(wù)回滾", e);
            throw new RuntimeException("更新失敗，線程中斷", e);
        } catch (Exception e) {
            transactionManager.rollback(status);
            log.error("事務(wù)A：更新異常，事務(wù)回滾", 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("事務(wù)B：開始更新id={}的用戶年齡", id2);
            userMapper.updateAgeById(id2, age2);
            Thread.sleep(1000);
            log.info("事務(wù)B：開始更新id={}的用戶年齡", id1);
            userMapper.updateAgeById(id1, age1);
            transactionManager.commit(status);
            log.info("事務(wù)B：執(zhí)行完成，事務(wù)提交");
        } catch (InterruptedException e) {
            transactionManager.rollback(status);
            Thread.currentThread().interrupt();
            log.error("事務(wù)B：線程中斷，事務(wù)回滾", e);
            throw new RuntimeException("更新失敗，線程中斷", e);
        } catch (Exception e) {
            transactionManager.rollback(status);
            log.error("事務(wù)B：更新異常，事務(wù)回滾", 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 = "用戶相關(guān)操作接口")
public class UserController {
    @Resource
    private UserService userService;
    @PostMapping("/deadlock/simulate")
    @Operation(summary = "模擬交叉更新死鎖場景", description = "并發(fā)執(zhí)行兩個交叉更新的事務(wù)，觸發(fā)死鎖")
    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執(zhí)行異常", e);
            } finally {
                countDownLatch.countDown();
            }
        }, "deadlock-thread-1").start();
        new Thread(() -> {
            try {
                userService.crossUpdateSecond(id1, id2, age1 + 1, age2);
            } catch (Exception e) {
                log.error("線程2執(zhí)行異常", 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 {
        // 統(tǒng)一更新順序：先更新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("事務(wù)B：開始更新id={}的用戶年齡", firstId);
        userMapper.updateAgeById(firstId, firstAge);
        Thread.sleep(1000);
        log.info("事務(wù)B：開始更新id={}的用戶年齡", secondId);
        userMapper.updateAgeById(secondId, secondAge);
        transactionManager.commit(status);
        log.info("事務(wù)B：執(zhí)行完成，事務(wù)提交");
    } catch (InterruptedException e) {
        transactionManager.rollback(status);
        Thread.currentThread().interrupt();
        log.error("事務(wù)B：線程中斷，事務(wù)回滾", e);
        throw new RuntimeException("更新失敗，線程中斷", e);
    } catch (Exception e) {
        transactionManager.rollback(status);
        log.error("事務(wù)B：更新異常，事務(wù)回滾", e);
        throw new RuntimeException("更新失敗", e);
    }
}