Description:
With the new auto increment handling, simple INSERT statements do not require table level auto-inc lock unless there is another transaction holding the auto-inc lock. The issue is that a long running bulk or mix-mode INSERT would make subsequent simple INSERTs to upgrade to auto-inc lock and when the bulk or mix-mode INSERT finishes, the awaiting simple INSERTs continue to acquire auto-inc lock in order, which reduced concurrency and increased chances of deadlock.
How to repeat:
This example produces a deadlock:
Prepare the data:
-------------------------
use test;
create table parent (id int, name varchar(20), primary key (id)) engine = innodb;
create table child (id int auto_increment, p_id int, primary key (id), key idx_p_id (p_id), constraint fk_p_id foreign key (p_id) references parent (id) ) engine = innodb;
insert into parent values (1, "foo"), (2,"blah");
insert into child (p_id) values (1);
for i in {1..20}; do echo $i; echo "insert into child (p_id) select t.p_id from child t" | mysql test; done
Run the test:
-------------------
session1:
echo “insert into child (p_id) select t.p_id from child t” | mysql test ;
session2: while session1 running
mysql> insert into child (p_id) value (2);
session3, while session1 and session2 running
mysql> begin;
mysql> update parent set name = “aha” where id = 2;
mysql> insert into child (p_id) value (1);
Session2 and session3 would wait for auto-inc lock while session1 is running. When session1 finishes, session2 and 3 would result in deadlock.
Without session1, the concurrent execution of session2 and session3 would not result in deadlock, which is a desired behavior.
Suggested fix:
This feature request is about the feasibility of downgrade the auto-inc lock for the awaiting simple INSERT after the bulk or mixed-mode INSERT finishes.
© 2024, Oracle Corporation and/or its affiliates
Description: With the new auto increment handling, simple INSERT statements do not require table level auto-inc lock unless there is another transaction holding the auto-inc lock. The issue is that a long running bulk or mix-mode INSERT would make subsequent simple INSERTs to upgrade to auto-inc lock and when the bulk or mix-mode INSERT finishes, the awaiting simple INSERTs continue to acquire auto-inc lock in order, which reduced concurrency and increased chances of deadlock. How to repeat: This example produces a deadlock: Prepare the data: ------------------------- use test; create table parent (id int, name varchar(20), primary key (id)) engine = innodb; create table child (id int auto_increment, p_id int, primary key (id), key idx_p_id (p_id), constraint fk_p_id foreign key (p_id) references parent (id) ) engine = innodb; insert into parent values (1, "foo"), (2,"blah"); insert into child (p_id) values (1); for i in {1..20}; do echo $i; echo "insert into child (p_id) select t.p_id from child t" | mysql test; done Run the test: ------------------- session1: echo “insert into child (p_id) select t.p_id from child t” | mysql test ; session2: while session1 running mysql> insert into child (p_id) value (2); session3, while session1 and session2 running mysql> begin; mysql> update parent set name = “aha” where id = 2; mysql> insert into child (p_id) value (1); Session2 and session3 would wait for auto-inc lock while session1 is running. When session1 finishes, session2 and 3 would result in deadlock. Without session1, the concurrent execution of session2 and session3 would not result in deadlock, which is a desired behavior. Suggested fix: This feature request is about the feasibility of downgrade the auto-inc lock for the awaiting simple INSERT after the bulk or mixed-mode INSERT finishes.