1

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 ?

0

1 Answer 1

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.

2
  • Interesting! What exactly gets locked ? show engine innodb lists the update as "trx id 11800(first window) lock_mode X locks rec but not gap waiting"
    – aconrad
    Jun 17 at 18:18
  • 1
    dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html: "If a duplicate-key error occurs, a shared lock on the duplicate index record is set." However, the status you observed seems to say it's an exclusive lock, not a shared lock. Jun 17 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.