Laravel 10迁移百万级数据表时锁表操作引发SQL报错的问题排查

看起来你在处理百万级数据表的结构迁移时遇到了锁表相关的SQL错误，我来帮你一步步排查问题根源并给出修正方案。

问题核心原因分析 #

你的两次报错本质都是MySQL锁表机制和Laravel迁移流程的冲突导致的：

1. 锁表范围不匹配：MySQL执行LOCK TABLES后，当前连接只能访问被显式锁定的表，任何对未锁定表的操作都会触发1100错误。比如第一次创建applications_new时的报错，可能是之前误锁了其他表且未释放；第二次的migrations表报错，则是因为Laravel在迁移结束后会自动往migrations表插入记录，但此时你的连接仍持有锁且未锁定该表。
2. 混乱的锁表逻辑：你多次重复执行LOCK TABLES，但MySQL的锁表语句是替换式的——新的锁表指令会覆盖之前的锁，导致之前锁定的表被意外解锁，进而引发中间步骤的异常。

修正后的迁移代码 #

我们重新梳理锁表逻辑，确保所有操作安全且符合MySQL和Laravel的运行规则：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if (Schema::hasTable('applications')) {
            // 1. 先创建新表（锁表前完成，避免创建时的锁范围问题）
            if (!Schema::hasTable('applications_new')) {
                Schema::create('applications_new', function (Blueprint $table) {
                    $table->id();
                    $table->foreignId('user_id')->index();
                    $table->foreignId('company_id')->index();
                    $table->foreignId('country_id')->index();
                    $table->foreignId('product_id')->index();
                    $table->foreignId('affiliate_product_id')->index();
                    $table->foreignId('pingtree_group_id')->index();
                    $table->foreignId('selected_pingtree_id')->index()->nullable();
                    $table->foreignId('affiliate_id')->index();
                    $table->foreignId('affiliate_campaign_id')->index()->nullable();
                    $table->foreignId('api_request_log_id')->index()->nullable();
                    $table->uuid('thread_uuid')->index();
                    $table->string('status')->index();
                    $table->json('status_metadata')->nullable();
                    $table->json('additional_info')->nullable();
                    $table->nullableMorphs('modelable');
                    $table->string('brand')->nullable()->index();
                    $table->ipAddress('ip');
                    $table->text('user_agent');
                    $table->string('fingerprint');
                    $table->timestamp('submitted_at')->index();
                    $table->timestamps();
                });
            }

            if (Schema::hasTable('applications_new')) {
                try {
                    // 2. 一次性锁定所有需要操作的表，避免锁被替换
                    DB::statement('LOCK TABLES applications WRITE, applications_new WRITE');

                    // 获取原表最大ID，设置新表自增起始值
                    $lastAutoIncrement = DB::table('applications')->orderBy('id', 'desc')->first();
                    if ($lastAutoIncrement) {
                        DB::statement('ALTER TABLE applications_new AUTO_INCREMENT = ' . ($lastAutoIncrement->id + 1));
                    }

                    // 3. 执行表重命名操作（瞬间完成，不影响数据）
                    Schema::rename('applications', 'applications_backup');
                    Schema::rename('applications_new', 'applications');
                } finally {
                    // 4. 无论操作是否成功，必须解锁表，防止死锁
                    DB::statement('UNLOCK TABLES');
                }
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        if (Schema::hasTable('applications_backup')) {
            try {
                DB::statement('LOCK TABLES applications WRITE, applications_backup WRITE');
                Schema::rename('applications', 'applications_new');
                Schema::rename('applications_backup', 'applications');
                Schema::dropIfExists('applications_new');
            } finally {
                DB::statement('UNLOCK TABLES');
            }
        }
    }
};

关键优化点 #

• 提前创建新表：在锁表前完成新表结构创建，避免创建表时因锁范围不匹配报错。
• 一次性锁表：用单条LOCK TABLES语句锁定所有涉及的表，避免锁被意外替换。
• try-finally保证解锁：无论操作是否抛出异常，都能确保表被解锁，防止影响正常业务。
• 完善回滚逻辑：在down方法中添加对应的锁表和回滚操作，确保迁移可安全回滚。

额外业务建议 #

• 表重命名是瞬间操作，但后续的差异数据同步建议在业务低峰期执行，避免影响系统性能。
• 若你的业务是高可用场景，建议先将读流量切换到从库，再执行迁移操作，进一步降低锁表对业务的影响。

备注：内容来源于stack exchange，提问作者Ryan H