在 MySql 中锁定表的完美策略是什么？

Question

我已经编写了这个创建新的 A/c 凭证的程序。它运行在 MySql 5.0 上。现在，是时候在我们的生产中实施这一点了。但我不确定它是否满足要求。这个锁定策略完美吗？请帮忙。

它这样调用：

CALL SpAcVoucherCreate(1,'2022/03/31','2831',5000,'A001');

我的程序如下：

USE `FinanceDB`;

DROP PROCEDURE IF EXISTS  `SpAcVoucherCreate`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SpAcVoucherCreate`(
    V_VOCHNO    INT,
    V_VOCHDT    CHAR(10),
    V_ACCODE    CHAR(4),
    V_AMOUNT    DECIMAL(12,2),
    V_USER_ID   CHAR(5)
    )
BEGIN

DECLARE V_ERR_OCCURED   BOOLEAN;

SET V_ERR_OCCURED=FALSE;

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET V_ERR_OCCURED=TRUE;

SET AUTOCOMMIT=0;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

LOCK TABLES `ACTRANS` WRITE;

INSERT INTO ACTRANS
        (VOCHNO,VOCHDT,ACCODE,AMOUNT,USER_ID)
        VALUES
        (V_VOCHNO,V_VOCHDT,V_ACCODE,V_AMOUNT,V_USER_ID);

IF V_ERR_OCCURED=TRUE THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

UNLOCK TABLES;

SET AUTOCOMMIT=1;

END;

    END $$
DELIMITER ;

Answer 1

我发现该过程做了一些不必要的事情。

• 对于您正在做的事情，SERIALIZABLE 的作用与 REPEATABLE READ 相同。 MySQL 中 SERIALIZABLE 的唯一作用是它使非锁定 SELECT 语句变成锁定 SELECT 语句，就像您使用了 SELECT...LOCK IN SHARE MODE 一样。由于您只执行 INSERT，因此这种差异没有影响。

• 无需禁用自动提交，然后为单个语句执行事务启动和提交/回滚。如果您启用了自动提交，则成功的 INSERT 将提交，而不成功的 INSERT 将不会提交。它的结果与您的代码相同。

• 不需要 SQLEXCEPTION 处理程序来回滚事务，因为事务中只有一条语句。它要么成功，要么不成功。

• 无论上述情况如何，您都没有显示出使用 LOCK TABLES 的理由。如果多个客户端尝试同时添加行，它只会阻止不必要的 INSERT/UPDATE/DELETE。如果有特殊原因需要这样做，您没有描述。

• 您的过程主体有一个不必要的 BEGIN/END 块。它没有任何作用。另外，我相信 DECLARE 只允许在第一个 BEGIN 之后，而不是在主体的其他块中。

• 可能根本没有理由使用存储过程，因为它只完成单个 INSERT 操作。为什么不直接在客户端中执行 INSERT 呢？我想如果您想限制对过程进行插入的权限，如果用户没有该权限，这将是一个原因。

我也同意上面的评论，即您使用的 MySQL 版本可疑已经过时。根据https://endoflife.software/applications/databases，MySQL 5.0 已于 2012 年结束生命周期/mysql。您缺少许多错误修复、安全补丁，当然还有现代功能。

---

更新：

SolarFlare 的评论是正确的，不允许在 MySQL 的存储过程中使用 LOCK 或 UNLOCK 语句：

mysql> create procedure p()
    -> begin
    -> lock tables mytable write;
    -> insert into mytable () values ();
    -> unlock tables;
    -> end//
ERROR 1314 (0A000): LOCK is not allowed in stored procedures