MySQL Bugs: #94148: Unnecessary Shared lock on parent table During UPDATE on a child table

Bug #94148	Unnecessary Shared lock on parent table During UPDATE on a child table
Submitted:	31 Jan 2019 5:47	Modified:	1 Mar 2019 13:37
Reporter:	Uday Varagani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.7.24	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	foreign keys, innodb locks, Shared lock

Description:
When foreign keys are defined between two tables and a row on child table is being modified, it is expected that respective row in parent table gets locked so that it will not be modified while this DML is running.

When the update on child table is modifying fields that are not involved in the foreign key, it is not required to lock respective rows in the parent table. In the below case, you can see a contradicting behavior.

This additional locking makes other threads to wait for it to be released if they have to run SELECT FOR UPDATE queries on the parent unnecessarily.

How to repeat:
Create test tables and insert sample data.

-----------------------
drop table if exist parent ;
drop table if exist child  ;

create table parent (
parent_id int unsigned not null,
primary key (parent_id)
);

create table child (
child_id int unsigned not null,
parent_id int unsigned not null,
bs_column int unsigned not null,
primary key (child_id),
key (parent_id,bs_column),
constraint fk_parent_id foreign key (parent_id) references parent (parent_id) on update cascade on delete cascade
);

insert into parent set parent_id=1;
insert into child set child_id=1,parent_id=1,bs_column=0;
insert into child set child_id=2,parent_id=1,bs_column=0;
-----------------------

Now, when I try to update bs_column in the child table, it is locking respective rows in the master table. bs_column is not involved in the foreign key and thus not required to lock any rows in the parent table.  

===================
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_status_output_locks ;
+------------------------------+
| @@innodb_status_output_locks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql> use DB245529
Database changed
mysql> 
mysql> update child set bs_column=bs_column+1 where child_id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
===================

---> INNODB STATUS OUTPUT

---TRANSACTION 3858, ACTIVE 9 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 139711464711936, query id 50 localhost root starting
show engine innodb status
TABLE LOCK table `DB245529`.`child` trx id 3858 lock mode IX
RECORD LOCKS space id 32 page no 3 n bits 72 index PRIMARY of table `DB245529`.`child` trx id 3858 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc    ;;
 1: len 6; hex 000000000f12; asc      ;;
 2: len 7; hex 300000018a0110; asc 0      ;;
 3: len 4; hex 00000001; asc    ;;
 4: len 4; hex 00000001; asc    ;;

TABLE LOCK table `DB245529`.`parent` trx id 3858 lock mode IS
RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table `DB245529`.`parent` trx id 3858 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 00000001; asc    ;;
 1: len 6; hex 000000000f0a; asc      ;;
 2: len 7; hex aa0000011e0110; asc        ;;
--------------------------------------------

NOTE: If we create a separate index on parent_id on child table those additional locks are not happening for the same UPDATE query.

mysql> DROP TABLE if exists child ;
mysql> create table child (
child_id int unsigned not null,
parent_id int unsigned not null,
bs_column int unsigned not null,
primary key (child_id),
key (parent_id),
key (parent_id,bs_column),
constraint fk_parent_id foreign key (parent_id) references parent (parent_id) on update cascade on delete cascade
);
mysql> insert into child set child_id=1,parent_id=1,bs_column=0;
mysql> insert into child set child_id=2,parent_id=1,bs_column=0;
 
With an additional index, the same update query has no shared lock on the parent table.

---TRANSACTION 3880, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 139711464711936, query id 250 localhost root starting
show engine innodb status
TABLE LOCK table `DB245529`.`child` trx id 3880 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 72 index PRIMARY of table `DB245529`.`child` trx id 3880 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000000f28; asc      (;;
 2: len 7; hex 3e0000018e0110; asc >      ;;
 3: len 4; hex 00000001; asc     ;;
 4: len 4; hex 00000001; asc     ;;
-------------------------------------------

Suggested fix:
Avoid those locks on the parent when the query on child table is not modifying any fields in FK constraint. When we tested the same from parent table perspective, it's working as expected.

i.e
When I try to modify a field on the parent table and that field is not part of FK, no child row is getting locked.

Hi,

Thank you for your bug report.

The row(s) on the parent table are locked because you have this index in the child table:

key (parent_id,bs_column)

Drop that index and try again. You should not get any locks on the parent table.

So, "drop a necessary index" is not a good workaround for a bug.

A slightly better one is "add a redundant index" as if you have one on (parent_id) as well as one on (parent_id,bs_column), it will avoid the lock, but then you're stuck with an extra index that incurs write load, wastes memory space (it is used by the fk, so will be in buffer pool, but useless to queries), wastes disk space and IO, and might mess up query optimization.

Fixing the code to separate constraint columns from supporting-index columns is the only real solution.

Sinisa,

I agree that if the table has only one key (parent_id, not-related-field) which works together with the foreign key constraint - it is safer to set lock when updating such a key. However, I believe foreign key constraint processing could be rewritten in such a way that this limitation is be avoided.

Please verify it as a feature request.

Hi Sveta,

When a segmented index exists which comprises a foreign key is included, then locks have to be put in place in order to avoid changing foreign key by concurrent transactions.

You wrote:

"
I believe foreign key constraint processing could be rewritten in such a way that this limitation is be avoided.

"

I would be happy to accept this as a feature request, once when you explain the basics of such a new algorithm.

Many thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".