MySQL Bugs: #110347: Inconsistent blocking of UPDATE and DELETE with the same WHERE clause

Bug #110347	Inconsistent blocking of UPDATE and DELETE with the same WHERE clause
Submitted:	12 Mar 2023 12:18	Modified:	16 Mar 2023 1:15
Reporter:	Dai Dinary	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	read committed, read uncommitted, transaction

Description:
Isolation Level: Read Uncommitted & Read Committed.
UPDATE statement is not blocked by the INSERT statement of another transaction, while DELETE statement that has the same WHERE clause as the UPDATE statement is blocked by the same INSERT statement of another transaction.

How to repeat:
Test case 1
/* init */ CREATE TABLE t0(c0 DOUBLE);
/* init */ INSERT INTO t0(c0) VALUES (9.1);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t0(c0) VALUES (2.5);
/* t2 */ BEGIN;
/* t2 */ UPDATE t0 SET c0=3.3 WHERE c0 = 2.5;
/* t1 */ COMMIT;
/* t2 */ COMMIT;

Test case 2
/* init */ CREATE TABLE t0(c0 DOUBLE);
/* init */ INSERT INTO t0(c0) VALUES (9.1);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t0(c0) VALUES (2.5);
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t0 WHERE c0 = 2.5; -- blocked
/* t1 */ COMMIT;
/* t2 */ COMMIT;

UPDATE statement is not blocked in test case 1, while DELETE statement that has the same WHERE clause as the UPDATE statement is blocked in test case 2.

Hi Mr. Dinary,

First of all, seems that isolation level is not so relevant here, since both commits are at the end.

There are several possible answers on why is one DML blocked and the other one is not.

First, you are using exact values for the floating point numbers, which is very unreliable, since there is not such thing as 100 % equality with floating points. Hence, either use integers or use BETWEEN conditions.

Second , the way that DELETE and UPDATE lock the tables is not the same. For that matter, when you get to the point that a DML is blocked, we would like to see what does full InnoDB status says about it .

We are waiting your feedback.

We try to use integers. Results are shown in the following.

Test case 1
/* init */ CREATE TABLE t0(c0 INT);
/* init */ INSERT INTO t0(c0) VALUES (9);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t0(c0) VALUES (2);
/* t2 */ BEGIN;
/* t2 */ UPDATE t0 SET c0=3 WHERE c0 = 2; -- not blocked
/* t1 */ COMMIT;
/* t2 */ COMMIT;

Test case 2
/* init */ CREATE TABLE t0(c0 INT);
/* init */ INSERT INTO t0(c0) VALUES (9);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t0(c0) VALUES (2);
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t0 WHERE c0 = 2; -- blocked
/* t1 */ COMMIT;
/* t2 */ COMMIT;

UPDATE statement is also not blocked in test case 1, while DELETE statement is blocked in test case 2.

Hi Mr. Dinary,

We have asked for a full InnoDB status info for both cases, one for UPDATE and second for DELETE.

We shall proceed when you provide us with one .......

Hi Mr. Dinary,

We have asked for a full InnoDB status info for both cases, one for UPDATE and second for DELETE.

We shall proceed when you provide us with one .......

Hi Mr. Dinary,

When you run InnoDB status, you see clearly why is DELETE blocked and UPDATE is not.

The command that INSERTs the value of 2 takes several locks, of which some gap locks and insert-intention locks are used. Hence, DELETE can not proceed, because, due to these locks, it can not get the exclusive lock on the same row.

UPDATE, on the other hand, changes that column to the value of 3, for which there are no locks held, hence there is no need to wait for anything.

Not a bug.

Hi Mr. Dinary,

When you run InnoDB status, you see clearly why is DELETE blocked and UPDATE is not.

The command that INSERTs the value of 2 takes several locks, of which some gap locks and insert-intention locks are used. Hence, DELETE can not proceed, because, due to these locks, it can not get the exclusive lock on the same row.

UPDATE, on the other hand, changes that column to the value of 3, for which there are no locks held, hence there is no need to wait for anything.

So, here are the locks after update :

*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:1199:140573003520592
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 24
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140573003520592
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:136:4:1:140573005095456
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 26
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005095456
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:136:4:2:140573005095456
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 26
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005095456
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000C02
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:136:4:3:140573005095456
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 26
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005095456
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000C03
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:136:4:4:140573005095808
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 26
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005095808
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000C04
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:136:4:4:140573005096160
ENGINE_TRANSACTION_ID: 37680
            THREAD_ID: 49
             EVENT_ID: 26
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005096160
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000C04

But, here are the locks after DELETE:
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:1203:140573003520592
ENGINE_TRANSACTION_ID: 37777
            THREAD_ID: 49
             EVENT_ID: 42
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140573003520592
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775981960:140:4:3:140573005095808
ENGINE_TRANSACTION_ID: 37777
            THREAD_ID: 49
             EVENT_ID: 42
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005095808
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 0x000000000C0D
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775982952:1203:140573003521680
ENGINE_TRANSACTION_ID: 37772
            THREAD_ID: 50
             EVENT_ID: 67
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140573003521680
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140572775982952:140:4:3:140573005100064
ENGINE_TRANSACTION_ID: 37772
            THREAD_ID: 49
             EVENT_ID: 42
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140573005100064
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000C0D

Hence, you can easily see why is DELETE waiting ......

Not a bug.

Sorry for repeated comments.

It was a bug in our forum software .........

Hi,

Thanks for your reply.

I get the same InnoDB status after DELETE, but the locks after UPDATE are different:

*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140497130803200:1092:140497037355856
ENGINE_TRANSACTION_ID: 3416
            THREAD_ID: 49
             EVENT_ID: 53
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140497037355856
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140497130802392:1092:140497037349760
ENGINE_TRANSACTION_ID: 3411
            THREAD_ID: 48
             EVENT_ID: 50
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140497037349760
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140497130802392:26:4:3:140497037346768
ENGINE_TRANSACTION_ID: 3411
            THREAD_ID: 49
             EVENT_ID: 53
        OBJECT_SCHEMA: test
          OBJECT_NAME: t0
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140497037346768
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000409
3 rows in set (0.00 sec)

Moreover, the Reference Manual explains that "Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED."

So the INSERT should not use gap locks at Read Committed.