MySQL - 锁定表或从触发器开始事务

Question

在高层次上，我所拥有的是:

1. 由应用程序 1 填充和管理的“主”数据库。
2. 使用standard mechanism 从“主”数据库复制的单独主机上的“从”数据库.
3. 应用程序 2，它独立于应用程序 1 运行，对从属数据库的只读访问权限非常有限，对自己的独立数据库的读写访问权限也非常有限。

基本上需要做的是，当“从属”数据库中的某些内容发生变化时，应用程序 2 需要得到通知，以便它可以检查“从属”数据库的内容并将一些内容写入自己的数据库。

性能不是问题，发生这种情况时锁定整个“从”数据库是可以接受的。 关键是确保在应用程序 2 完成其操作之前，不会将更多信息复制到“从属”数据库中。应用程序 2 从独立于触发 session 的 session 访问“从属”数据库通知。

为此，我有以下触发器:

delimiter //
CREATE TRIGGER create_report_trigger AFTER UPDATE ON jobs
        FOR EACH ROW 
                BEGIN
                        DECLARE report_id INT;
                        IF (NEW.status = 7 AND OLD.status != 7) THEN
                                CALL CREATE_REPORT_PROC(NEW.id, @report_id);
                        END IF;
                END;
        //
delimiter ;

...并且由于您无法在触发器内启动事务或锁定数据库，因此我还有以下过程:

delimiter //
CREATE PROCEDURE create_report_proc( 
        IN jobId INT,
        OUT report_id INT 
)
        BEGIN
                START TRANSACTION WITH CONSISTENT SNAPSHOT;
                SELECT CREATE_REPORT(jobId) INTO report_id;
                COMMIT;
        END //
delimiter ;

程序正在调用 user-defined function它使用 libcurl 联系应用程序 2 并让它知道它需要处理作业。

当我在 MySQL 命令行上手动调用它时，该过程完美运行。但是，当它从触发器中调用时，MySQL 日志中出现以下错误:

存储函数或触发器中不允许显式或隐式提交

...显然 MySQL 足够聪明，可以检测到我试图通过将触发器委托(delegate)给过程来破坏它的“不从触发器内部锁定”规则。

用户定义函数等待应用程序 2 的操作完成，然后将结果返回给其调用者，这可能是相关的(如果 MySQL 复制过程基本上被触发器执行阻塞，则无需手动锁定任何东西; 复制过程是唯一能够对“从”数据库进行更改的过程。

无论如何，我想这里有两个问题:

1. 由于触发器是由于 MySQL 复制过程进行的更新而触发的，这是否意味着复制过程将被阻止，直到触发器返回？

2. 如果没有，我该如何锁定数据库或以其他方式停止触发器的复制过程？我想从触发器内部发出 STOP SLAVE; 可能会成功？

编辑 - 这是一个额外的后续问题:

当应用程序 2 开始做它的事情时，它看到的数据并没有反射(reflect)应该在“从属”数据库中的最新信息。具体来说，作为触发事务的一部分对 jobs 表所做的任何更新对应用程序 2 都是不可见的。

鉴于触发器配置为触发 AFTER UPDATE，为什么会这样？是否有任何方法可以使新内容对数据库中的应用程序 2 可见，或者是否有必要手动收集并传递所有更新的字段值作为发送通知的一部分？

Answer 1

好的，根据我自己的研究:

1. 触发器执行是同步的，并阻塞调用线程直到触发器完成。默认情况下，MySQL 复制本质上是一个单线程进程(我认为即使启用多线程复制，每个数据库也只能获得一个线程)。因此，在我的例子中，出于所有实际目的，阻塞复制线程会“锁定”数据库；除了复制线程之外，没有其他人具有写入权限。

2. 你没有，明确地说。然而，触发器的执行被认为是触发它的事务的一部分，这意味着您可能无论如何都被隐式锁定/在原子事务中。这种情况发生的程度似乎取决于您的存储引擎和事务隔离设置。

3. 仍然不能完全确定“奖励”问题，但我推测它与 #2 相关。如果触发器执行是触发它的事务的一部分，则该事务在触发器完成之前无法提交。如果事务尚未提交，则其他 session 的观察者将无法看到其中的任何更改。

   无论如何，将更新后的字段作为参数传递给 UDF 可以很好地解决这个问题。