Do MySQL transactions for INSERT lock foreign key referenced tables?
Asked Answered
P

2

8

I am trying to do a huge transaction in my Java application and doing single insert entries (volume in thousands) for user_account_entry table which has foreign key reference to user table.

When the transaction is running, i am not able to update the any user entity for which belongs to the transaction getting the LockAcquisitionException

I am using MySQL InnoDB and using DEFAULT isolation level for transaction which translated to REPEATABLE-READ level for InnoDB, can anyone shed some light on foreign key locking during mysql transactions

Peonage answered 13/2, 2019 at 14:31 Comment(4)
MySQL locks all related (by foreign keys) rows using shared mode (read only) locks to ensure consistency. To stop this you need to either remove the foreign keys or disable them.Irv
Hopefully you are on a recent version of MySQL and using InnoDB. Performance has greatly improved in 8.0 (with new locking options too!) and MyIsam is not designed for concurrent insertionsNoctilucent
thanks @Irv do you have any link to any documentation regarding thisPeonage
dev.mysql.com/doc/refman/5.7/en/… you can find the reference on this page "InnoDB sets shared row-level locks on child or parent records it has to look at"Elielia
A
13

Yes.

Demo: In one window, create parent & child tables.

mysql1> create table parent (id int primary key, x int );
Query OK, 0 rows affected (0.04 sec)

mysql1> create table child (id int primary key, parentid int,
    foreign key(parentid) references parent(id));
Query OK, 0 rows affected (0.03 sec)

Insert a row into the parent table:

mysql1> insert into parent values (1, 1);
Query OK, 1 row affected (0.02 sec)

Start a transaction and add a row to the child table, referencing the parent row:

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

mysql1> insert into child values (42, 1);
Query OK, 1 row affected (0.00 sec)

Open a second window, and try to update the row referenced in parent:

mysql2> update parent set x = 2 where x = 1;

It hangs, waiting on the lock held by the first session.

Go back to the first window and commit the transaction, which releases locks:

mysql1> commit;
Query OK, 0 rows affected (0.02 sec)

In the second window, the update proceeds, and the timing shows that it waited almost six seconds, the time it took me to get back to the first window to commit.

Query OK, 1 row affected (5.92 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Auramine answered 14/2, 2019 at 0:25 Comment(5)
Thanks @Bill i tried exactly the same thing yesterday, got the same resultsPeonage
In this case, before committing, if session 1 does it's own update statement against the same parent row (update parent set x = 3 where x =1), why does this end in a deadlock with session 2 being rolled back? Wouldn't session 2 just continue to block until session 1 was committed? Also, if session 1 did the UPDATE statement first followed by session 2's own UPDATE statement then followed by another UPDATE statement in session 1 for some reason, why does a deadlock no longer occur and session 2 just ends up blocking? I feel like I'm missing something basic hereBaroness
@Baroness A deadlock is a circular lock wait. Session 2 is waiting for for locks held by session 1, so session 1 must commit to unblock session 2. Then session 1 tries to update something requiring locks that session 2 is already in the queue to acquire. Therefore session 1 must wait. They're both waiting for each other. MySQL notices this immediately (there's a server thread watching for cases like this), and forces one to roll back its transaction, releasing its locks.Auramine
In your second example, if session 1 acquires locks first, it keeps those locks and can re-use them as many times as it wants until it commits, even if another session is waiting. Once it acquires locks, a session does not give up those locks until the end of its transaction.Auramine
@BillKarwin not sure if that answers my question (sorry if I'm asking this within the comments). I was just wondering which locks session 2 is even holding in the first place to cause a deadlock if all it wants to do is attempt to update a specific row. I just found someone asking something extremely similar to my question here (#41016313) and the accepted answer was that it was a mysql quirk/bug/edgecase?Baroness
S
0

Java has a "batch" insert capability. Use it to insert up to 100 rows at a time. That will run 10 times as fast, thereby helping make a variety of problems less frequent.

Seat answered 14/2, 2019 at 23:23 Comment(2)
How can batch insert help with table locking and transactions? Question is not about performance. Please explain, and I'll mark it as useful.Salicin
@Salicin - Deadlocks, for example, are impossible if no other threads are running. Batching will make that thread take action much less often. On the other hand, when it runs it will run somewhat longer. That is, the risk of deadlocks (etc) is less (but not eliminated).Seat

© 2022 - 2024 — McMap. All rights reserved.