这是MySQL触发线程安全的吗？ [英] Is this MySQL trigger thread-safe?
问题描述
我有与此处所述的相同问题，并且我认为答案https://stackoverflow.com/a/22343265/297487 是一个很好的解决方案，但我对此问题还有另一个疑问。
I have the same question as described here and also I think the answer https://stackoverflow.com/a/22343265/297487 is a good solution but I have another question about this answer.
以下触发器（从答案中复制）是否是线程安全的？我的意思是，如果将两个并发记录插入到表中，优先级列（如问题所述）是否具有一致的值（与id相同的值）？
Is the following trigger (copied from answer) thread-safe? I mean if two concurrent record inserted to table, does "priority" column (as describe in question) have consistent value (The same value as id)?
delimiter //
drop trigger if exists bi_table_name //
create trigger bi_table_name before insert on table_name
for each row begin
set @auto_id := ( SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='table_name'
AND TABLE_SCHEMA=DATABASE() );
set new.priority= @auto_id;
end;
//
delimiter ;
我们如何解释每行子句？
假设MySQL要插入两个并发行。
How do we interpret "for each row" clause? Suppose that MySQL wants insert two concurrent rows. How does this trigger works?
一种解释如下：
MySQL锁定表并在插入其中之一之前MySQL触发器的行（并发记录之一）启动并获取当前的AUTO_INCREMENT值并将其设置为 priority列，然后插入一条记录。之后，MySQL开始插入另一条记录，然后将相同的情况应用于新记录。
MySQL locks table and before insert one of the rows (one of concurrent record) MySQL trigger starts and gets current AUTO_INCREMENT value and sets it to "priority" column and then inserts a record. After that MySQL starts inserting another record and then the same situation applies for new record.
另一种解释可能如下：
当将两个并发记录插入MySQL时，MySQL会锁定表，然后在插入两个并发记录之前触发启动，并且 for each row子句在两个记录之间进行迭代，并将 priority列值设置为相同的值，并且然后在数据库中插入两个并发记录。在这种情况下，触发器无法按预期方式工作。
When two concurrent records are inserted to MySQL, MySQL locks the table and then before inserting two concurrent records a trigger starts and "for each row" clause iterate between two record and set "priority" column value to the same value and then insert two concurrent record in database. In this situation the trigger does not work as expected.
以上解释之一是正确的吗？
Which one of the above interpretation is correct?
我有下表：
CREATE TABLE `t_file` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_persian_ci NOT NULL,
`p_name` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `p_name_2` (`p_name`)
) ENGINE=InnoDB AUTO_INCREMENT=206284 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;
我想插入 p_name 的值与 id 相同。
触发设置 p_name 的值如下（从代码中复制）
The trigger that sets value of p_name is as follows(copied from your code)
delimiter $$
drop trigger if exists file_p_name $$
create trigger file_p_name before insert on t_file
for each row begin
set @id := ( SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='t_file'
AND TABLE_SCHEMA=DATABASE() );
set new.p_name= @id;
end;
$$
delimiter ;
在我们的应用程序中，我将插入在 t_file try catch 的c $ c>表，几乎总是可以的，但是有时（在同时插入 t_file 表），我在应用程序的日志中看到以下异常：
In our application, i surround the code that inserts int_file table with try catch,in almost always everything is OK,but sometimes(in concurrent insert to t_file table), i see the following exception in our application's log:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \'387456\' for key \'p_name_2\''
c $ c> trigger 不能按预期工作，或者我错了！
It seems the trigger does not work as expected or maybe i am wrong!!!
推荐答案
您的问题中提到的答案
The answer referred in your question was posted by me some time back.
而在对于每一行 中， NEW 是在上下文中插入的对应新行。并且 set new.xxx 仅适用于该行，而不适用于中的适用于所有行 批处理。因此不会有任何冲突，也不会出现失败的问题。
While in "For Each Row", The NEW is the corresponding new row, being inserted, in context. And the set new.xxx is only applicable for that row and but not 'for all rows' in batch. So there won't be any collision and the question of failure should not arise.
我也回答了类似的问题
在MySQL触发器中，每行如何工作？。请仔细阅读答案中给出的示例。
I have also answered a similar question How does "for each row" work in triggers in mysql?. Please go through the examples given in the answer.
请参阅文档：
这篇关于这是MySQL触发线程安全的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
