Is this MySQL trigger thread-safe?

Question

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.

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 ;

How do we interpret "for each row" clause? Suppose that MySQL wants insert two concurrent rows. How does this trigger works?

One interpretation is as follows:

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.

Another interpretation might be as follows:

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?

Update :

I have the following table :

    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;

I want to insert value of p_name the same as id when row inserted.

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 ;

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\''

It seems the trigger does not work as expected or maybe i am wrong!!!

Answer 1

The answer referred in your question was posted by me some time back.

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.

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.

Refer to Documentation:

• MySQL: Triggers