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

Paras Diwan
  • 333
  • 1
  • 5
  • 13
  • 3
    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. – Vatev Feb 13 '19 at 14:35
  • 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 insertions – Dave Stokes Feb 13 '19 at 14:42
  • thanks @Vatev do you have any link to any documentation regarding this – Paras Diwan Feb 13 '19 at 15:05
  • 2
    https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html you can find the reference on this page "InnoDB sets shared row-level locks on child or parent records it has to look at" – huhushow Feb 13 '19 at 15:33

2 Answers2

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks @Bill i tried exactly the same thing yesterday, got the same results – Paras Diwan Feb 14 '19 at 10:24
  • 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 here – georaldc Jul 30 '20 at 22:25
  • @georaldc 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. – Bill Karwin Jul 30 '20 at 23:38
  • 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. – Bill Karwin Jul 30 '20 at 23:40
  • @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 (https://stackoverflow.com/questions/41015813/avoiding-mysql-deadlock-when-upgrading-shared-to-exclusive-lock) and the accepted answer was that it was a mysql quirk/bug/edgecase? – georaldc Jul 30 '20 at 23:48
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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • How can batch insert help with table locking and transactions? Question is not about performance. Please explain, and I'll mark it as useful. – otocon Apr 22 '20 at 06:56
  • @otocon - 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). – Rick James Apr 22 '20 at 23:12