single insert ignore on a table with no auto-increment

Question

For this table, in mysql 5.7,

    CREATE TABLE `t` (
      `id` int(10) unsigned NOT NULL,
      `val` int(10) unsigned NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

• The table doesn't have autoincrement column
• The table doesn't have partitions
• There ONLY queries that are performed on this table are INSERT and SELECT
• the table is not used in any subqueries
• sql_mode doesnt have any STRICT_* enabled

I need to insert into this table, but the id value exists in >50% of the cases. I want to use a single INSERT IGNORE instead of "SELECT id" , if doesnt exist "INSERT"

When doing an INSERT IGNORE, (NOT a multi-insert one), and the PRIMARY KEY exists, is/can there ANY locking involved ?

Answer 1

This can be confirmed in a test. Open two windows.

In the first window:

mysql> insert into t values (1, 10), (3, 30);
mysql> begin;
mysql> insert ignore into t values (1, 10);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't.PRIMARY' |
+---------+------+-----------------------------------------+

The transaction is still uncommitted, so if we have locked the row, this transaction still holds the lock.

Now in the second window:

mysql> update t set val = val * 2 where id = 1;
<hangs waiting for lock>

Evidently the INSERT IGNORE in the first window did acquire a lock.