MySQL - 表锁定

MySQL：表锁定详解

在多用户数据库环境中，当多个客户端同时访问相同数据时，并发控制对于防止数据损坏至关重要。MySQL 为此提供了几种机制，包括表锁定。

重要背景信息： 显式 LOCK TABLES 是一种较旧的并发机制，主要与 MyISAM 存储引擎相关联。现代默认引擎 InnoDB 使用一种更复杂、并发性更高的方法：事务中的行级锁定。对于典型的应用程序工作负载，您几乎总是应优先选择 InnoDB 事务而非显式表锁。

何时使用 LOCK TABLES

尽管 LOCK TABLES 如今不那么常见，但它仍然具有有效的用例：

• 批量操作： 在需要防止其他任何访问以确保一致性的大量数据加载或批量更新期间。
• 非事务性引擎： 处理 MyISAM 或其他非事务表时。
• 模拟事务： 对于无法轻松封装在单个标准事务中的复杂多表操作，表锁可以确保一致的快照。

理解表锁定类型

LOCK TABLES 语句为当前客户端会话获取锁。在锁释放之前，其他任何会话都无法以冲突的方式访问被锁定的表。主要的锁类型有：

• READ LOCK（读锁）： 持有此锁的会话可以从表中读取，但不能写入。其他会话也可以从表中读取，但无法获取 WRITE 锁或修改数据。
• WRITE LOCK（写锁）： 持有此锁的会话拥有独占访问权限。它可以从表中读取和写入。其他任何会话都不能从表中读取或写入。

锁定和解锁的语法

-- 获取锁
LOCK TABLES table1_name [READ | WRITE], table2_name [READ | WRITE], ...;

-- 释放当前会话持有的所有锁
UNLOCK TABLES;

示例：安全数据归档

假设您需要将 active_orders 表中的旧订单归档到 archived_orders 表。为了确保在此过程中没有新订单被修改或删除，我们可以使用表锁。

首先，我们来设置表：

CREATE TABLE active_orders (
    id INT PRIMARY KEY,
    order_data TEXT,
    order_date DATE
);

CREATE TABLE archived_orders LIKE active_orders;

INSERT INTO active_orders VALUES
(1, 'Order A', '2022-01-15'),
(2, 'Order B', '2023-05-20'),
(3, 'Order C', '2022-03-10');

现在，在锁定块内执行归档操作：

-- 在源表上获取 READ 锁，在目标表上获取 WRITE 锁
LOCK TABLES active_orders READ, archived_orders WRITE;

-- 1. 将旧订单复制到归档表
INSERT INTO archived_orders
SELECT * FROM active_orders WHERE order_date < '2023-01-01';

-- 2. 由于我们只有 READ 锁，此步骤将失败
-- DELETE FROM active_orders WHERE order_date < '2023-01-01';
-- ERROR 1099 (HY000): 表 'active_orders' 被 READ 锁锁定，无法更新

-- 释放锁
UNLOCK TABLES;

-- 要同时执行 INSERT 和 DELETE，您需要对两个表都使用 WRITE 锁
LOCK TABLES active_orders WRITE, archived_orders WRITE;
INSERT INTO archived_orders SELECT * FROM active_orders WHERE order_date < '2023-01-01';
DELETE FROM active_orders WHERE order_date < '2023-01-01';
UNLOCK TABLES;

验证

在使用 WRITE 锁执行正确的序列后，您可以验证表：

SELECT * FROM archived_orders;
-- 返回订单 1 和 3

SELECT * FROM active_orders;
-- 返回订单 2

现代替代方案：InnoDB 事务

对于 InnoDB 表上的相同归档任务，事务是更优越的方法。它提供原子性（全有或全无），并使用细粒度的行级锁，允许表的其他部分保持可访问。

START TRANSACTION;

-- SELECT 语句对匹配的行放置读锁
INSERT INTO archived_orders
SELECT * FROM active_orders WHERE order_date < '2023-01-01';

-- DELETE 语句对匹配的行放置写锁
DELETE FROM active_orders WHERE order_date < '2023-01-01';

-- 如果两者都成功，则使更改永久化
COMMIT;

-- 如果发生错误，您将发出 ROLLBACK 以撤消更改。

以编程方式使用表锁

如果您必须使用 LOCK TABLES，这里介绍如何在应用程序代码中实现。请务必确保即使发生错误，也会调用 UNLOCK TABLES，通常在 finally 块中进行。

NodeJS
Python
Java
PHP



使用 `mysql2/promise` 演示锁定/解锁流程。

const mysql = require('mysql2/promise');

async function lockedOperation() {
    const connection = await mysql.createConnection(process.env.DATABASE_URL);
    try {
        console.log('正在获取锁...');
        await connection.execute('LOCK TABLES active_orders WRITE, archived_orders WRITE');

        console.log('正在执行锁定操作...');
        await connection.execute("INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1");
        await connection.execute('DELETE FROM active_orders WHERE id = 1');
        console.log('操作成功。');

    } catch (error) {
        console.error('锁定操作期间发生错误:', error);
    } finally {
        console.log('正在释放锁...');
        await connection.execute('UNLOCK TABLES');
        await connection.end();
    }
}

lockedOperation();



使用 Python 的 `try...finally` 确保锁被释放。

import mysql.connector

def locked_operation():
    conn = mysql.connector.connect(**config) # 假设 config 已定义
    cursor = conn.cursor()
    try:
        print('正在获取锁...')
        cursor.execute('LOCK TABLES active_orders WRITE, archived_orders WRITE')

        print('正在执行锁定操作...')
        cursor.execute('INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1')
        cursor.execute('DELETE FROM active_orders WHERE id = 1')
        conn.commit()
        print('操作成功。')

    except mysql.connector.Error as err:
        print(f'错误: {err}')
    finally:
        print('正在释放锁...')
        cursor.execute('UNLOCK TABLES')
        cursor.close()
        conn.close()

locked_operation()



使用 Java 的 `try-finally` 块确保 `UNLOCK TABLES` 始终被执行。

import java.sql.*;

public class TableLocker {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS); // 假设常量已定义
            Statement stmt = conn.createStatement();

            System.out.println("正在获取锁...");
            stmt.execute("LOCK TABLES active_orders WRITE, archived_orders WRITE");

            System.out.println("正在执行操作...");
            stmt.executeUpdate("INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1");
            stmt.executeUpdate("DELETE FROM active_orders WHERE id = 1");
            System.out.println("操作成功。");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try (Statement finalStmt = conn.createStatement()) {
                    System.out.println("正在释放锁...");
                    finalStmt.execute("UNLOCK TABLES");
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}



使用 PHP 及 `try...finally` 块（PHP 5.5+）来确保清理操作。

<?php
mysqli_report(MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');

try {
    echo "正在获取锁...\n";
    $mysqli->query('LOCK TABLES active_orders WRITE, archived_orders WRITE');

    echo "正在执行操作...\n";
    $mysqli->query('INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1');
    $mysqli->query('DELETE FROM active_orders WHERE id = 1');
    echo "操作成功。\n";

} catch (mysqli_sql_exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
} finally {
    if (isset($mysqli)) {
        echo "正在释放锁...\n";
        $mysqli->query('UNLOCK TABLES');
        $mysqli->close();
    }
}