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
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