mysql - 触发器不尊重自动提交选项？

Question

MySQL 触发器主体不尊重 AUTOCOMMIT 选项并且始终在事务中运行？

<小时/>

现实世界场景

我只有AFTER UPDATE触发器，它设置了一些计数器(例如，标志更改将减少旧标志的计数器并增加新标志的计数器)，并且有两台负载相对较高的服务器一直在改变标志...有时会陷入僵局:)

<小时/>

测试设置

我已经设置了这两个表:

test (
    id INT NOT NULL AUTO_INCREMENT,
    data TEXT DEFAULT NULL,
    PRIMARY KEY (id)
)

test2 (
    id INT NOT NULL AUTO_INCREMENT,
    val INT DEFAULT 0,
    PRIMARY KEY (id)
)

插入一些虚拟数据:

INSERT INTO test (data) VALUES (RAND());
INSERT INTO test2 (id, val) VALUES
    (1, 0),
    (2, 0);

创建模拟数据计算的触发器(故意很长，以便让我有时间运行第二个查询)并对 test2 表中的数据进行虚拟更改:

DELIMITER $$
CREATE TRIGGER `test_AUPD` AFTER UPDATE ON `test` FOR EACH ROW
BEGIN
    UPDATE test2
    SET val = val + 1
    WHERE id = 1;

    SELECT SLEEP(30) INTO @void;

    UPDATE test2
    SET val = val - 1
    WHERE id = 2;

END $$

当然:

SHOW VARIABLES LIKE 'autocommit';
-- autocommit, ON

<小时/>

测试结果

当我打电话时:

UPDATE test SET data = RAND();

在两个连接中，第一个连接需要 30 秒才能完成更新，但第二个连接大约需要 55 秒。

<小时/>

问题

• 此行为记录在哪里(我花了几个小时研究 InnoDB 事务锁定等，但没有运气)？ 我认为当复制打开时这是合理的行为。

• 同步触发器更新同一个表中的计数器的正确方法是什么？ OR 如何处理这种情况？

Answer 1

调试这个问题的好方法似乎是从INFORMATION_SCHEMA中选择锁和事务信息:

SELECT 
    PS.id AS conneciton_id, PS.HOST AS host, PS.INFO AS query_full, INNOLOCK.lock_id,
    INNOLOCK.lock_mode, INNOLOCK.lock_type, INNOLOCK.lock_table, INNOLOCK.lock_rec, 
    INNOTRX.trx_query, INNOTRX.trx_state, INNOTRX.trx_requested_lock_id
FROM INFORMATION_SCHEMA.INNODB_LOCKS AS INNOLOCK
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS INNOTRX 
    ON INNOLOCK.lock_trx_id = INNOTRX.trx_id
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS PS ON PS.ID = INNOTRX.trx_mysql_thread_id;

它将获取如下数据:

+-----------------------++-------------------------------+-----------------------------+
| connection_id         || *****                         | *****                       |
| host                  || *****:*****                   | *****:*****                 |
| query_full            || UPDATE test SET data = RAND() | SELECT SLEEP(30) INTO @void |
| lock_id               || 1234567890:12345678:2:3       | 0124567890:01234567:1:2     |
| lock_mode             || X                             | X                           |
| lock_type             || RECORD                        | RECORD                      |
| lock_table            || `test`                        | `test`                      |
| lock_rec              || 2                             | 2                           |
| trx_query             || UPDATE test SET data = RAND() | SELECT SLEEP(30) INTO @void |
| trx_state             || LOCK WAIT                     | RUNNING                     |
| trx_requested_lock_id || 0124567890:01234567:1:2       |                             |
+-----------------------++-------------------------------+-----------------------------+

这会立即告诉您第一列中显示的查询正在等待锁定 0124567890:01234567:1:2 和带有锁定的事务 0124567890:01234567:1:2现在处于SELECT SLEEP(30)。