-- 查看當(dāng)前所有鎖信息
SELECT * FROM `performance_schema`.data_locks;

-- 查看所有活動事務(wù)
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as age_seconds
FROM information_schema.innodb_trx 
WHERE trx_state = 'RUNNING';

docker logs mysql

# docker-compose.yml
services:
  mysql:
    image: mysql:8.0.27
    command: 
      - --innodb-force-recovery=1
      # ... 其他參數(shù)

SHOW VARIABLES LIKE 'innodb_force_recovery';
-- 輸出: innodb_force_recovery = 1

XA RECOVER;
-- 仍然顯示2個XA事務(wù)！

# 備份所有數(shù)據(jù)，跳過鎖表
docker exec mysql mysqldump -uroot -p123456 \
  --all-databases \
  --skip-lock-tables \
  --set-gtid-purged=OFF \
  > /tmp/all_dbs_backup.sql

# 停止并清理容器
docker-compose down
docker rm -f mysql

# 徹底刪除數(shù)據(jù)目錄
sudo rm -rf data/*

# 清理配置文件中的恢復(fù)參數(shù)
sed -i '/innodb_force_recovery/d' my.cnf

# 使用干凈的配置啟動
docker-compose up -d

# 等待初始化完成（重要！）
docker logs mysql -f

# 導(dǎo)入備份數(shù)據(jù)
docker exec -i mysql mysql -uroot -p123456 < /tmp/all_dbs_backup.sql

using System.Transactions;

public class OrderService
{
    private readonly IOrderRepository _orderRepository;
    private readonly IInventoryService _inventoryService;
    
    public OrderService(IOrderRepository orderRepository, IInventoryService inventoryService)
    {
        _orderRepository = orderRepository;
        _inventoryService = inventoryService;
    }
    
    public void CreateOrder(Order order)
    {
        using (var scope = new TransactionScope())
        {
            try
            {
                // 業(yè)務(wù)邏輯
                _orderRepository.Save(order);
                
                // 調(diào)用外部系統(tǒng)（可能超時或異常）
                _inventoryService.UpdateStock(order); // 這里可能拋出異常
                
                // 如果這里異常，事務(wù)可能停留在prepared狀態(tài)
                scope.Complete();
            }
            catch (Exception ex)
            {
                // 事務(wù)會自動回滾，但分布式事務(wù)可能異常
                throw;
            }
        }
    }
}

public class OrderService
{
    private readonly ApplicationDbContext _context;
    
    public async Task CreateOrderAsync(Order order)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                // 保存訂單
                _context.Orders.Add(order);
                await _context.SaveChangesAsync();
                
                // 調(diào)用外部服務(wù)
                await UpdateInventoryAsync(order); // 可能在這里失敗
                
                // 提交事務(wù)
                await transaction.CommitAsync();
            }
            catch (Exception)
            {
                // 回滾事務(wù)
                await transaction.RollbackAsync();
                throw;
            }
        }
    }
}

-- 檢查XA事務(wù)
SELECT COUNT(*) as xa_count FROM performance_schema.xa_transactions 
WHERE STATE = 'PREPARED';

-- 檢查長事務(wù)
SELECT 
    trx_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as age_seconds
FROM information_schema.innodb_trx 
WHERE trx_state = 'RUNNING' 
AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 300; -- 5分鐘以上

public class ResilientOrderService
{
    private readonly IOrderRepository _orderRepository;
    private readonly IInventoryService _inventoryService;
    private readonly ILogger<ResilientOrderService> _logger;
    
    public async Task<bool> CreateOrderWithRetryAsync(Order order)
    {
        var retryCount = 0;
        const int maxRetries = 3;
        
        while (retryCount < maxRetries)
        {
            try
            {
                await using var transaction = await BeginTransactionWithTimeoutAsync(TimeSpan.FromSeconds(30));
                
                try
                {
                    // 保存訂單
                    await _orderRepository.SaveAsync(order);
                    
                    // 更新庫存（設(shè)置超時）
                    await _inventoryService.UpdateStockAsync(order)
                        .TimeoutAfter(TimeSpan.FromSeconds(10));
                    
                    await transaction.CommitAsync();
                    return true;
                }
                catch (Exception)
                {
                    await transaction.RollbackAsync();
                    throw;
                }
            }
            catch (MySqlException ex) when (ex.Number == 1397) // XAER_NOTA
            {
                retryCount++;
                _logger.LogWarning("XA事務(wù)異常，重試 {RetryCount}/{MaxRetries}", retryCount, maxRetries);
                
                if (retryCount >= maxRetries)
                {
                    _logger.LogError(ex, "XA事務(wù)重試次數(shù)耗盡");
                    throw;
                }
                
                await Task.Delay(TimeSpan.FromSeconds(1 * retryCount));
            }
        }
        
        return false;
    }
    
    private async Task<MySqlTransaction> BeginTransactionWithTimeoutAsync(TimeSpan timeout)
    {
        // 實現(xiàn)帶超時的事務(wù)開始邏輯
        // ...
    }
}

// 超時擴展方法
public static class TaskExtensions
{
    public static async Task<T> TimeoutAfter<T>(this Task<T> task, TimeSpan timeout)
    {
        using var timeoutCancellationTokenSource = new CancellationTokenSource();
        var completedTask = await Task.WhenAny(task, Task.Delay(timeout, timeoutCancellationTokenSource.Token));
        
        if (completedTask == task)
        {
            timeoutCancellationTokenSource.Cancel();
            return await task;
        }
        else
        {
            throw new TimeoutException("操作超時");
        }
    }
}

version: "3.9"
services:
  mysql:
    image: mysql:8.0.27
    container_name: mysql
    restart: unless-stopped  # 修改重啟策略
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql
      - ./conf.d:/etc/mysql/conf.d  # 統(tǒng)一配置目錄
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      TZ: Asia/Shanghai
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      timeout: 5s
      retries: 3

#!/bin/bash
# MySQL XA事務(wù)緊急恢復(fù)腳本

BACKUP_DIR="/tmp/mysql_backup_$(date +%Y%m%d_%H%M%S)"
mkdir -p $BACKUP_DIR

echo "1. 備份數(shù)據(jù)庫..."
docker exec mysql mysqldump -uroot -p$MYSQL_PWD --all-databases > $BACKUP_DIR/full_backup.sql

echo "2. 停止服務(wù)..."
docker-compose down

echo "3. 清理數(shù)據(jù)..."
sudo rm -rf data/*

echo "4. 重新初始化..."
docker-compose up -d

echo "5. 等待啟動完成..."
sleep 60

echo "6. 恢復(fù)數(shù)據(jù)..."
docker exec -i mysql mysql -uroot -p$MYSQL_PWD < $BACKUP_DIR/full_backup.sql

echo "恢復(fù)完成！備份保存在: $BACKUP_DIR"