I am trying to insert concurrently (heavy inserts by 8 threads) into sql throught hibernate. My pojo consists of two tables, one table references the other through foreign key constraint. I am trying to save a lot of instances of my pojo to the db concurrently. Sometimes the insert is failing and rolling back because of lock wait timeout.
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction.
Suppose there are table A(table edges in the screenshot) and table B. Table B has a foreign key constraint which references it's id to primary key of table A. What I could infer from the locks table is an S lock is being held on table A's record by trx 114888 while try to insert in table B(id corresponding to table B) and 11493 is waiting to acquire X lock to insert new record in table A. Table A has index on some of its columns.
What is meant by supremum pseudo-record here? Is it a gap lock? If so then why is the record type as 'RECORD'? Is there a way around this so as to avoid this gap lock or whatever lock it is?
These are the screenshots of the lock tables. INNODB_LOCKS table screnshot
Some innodb status logs
---TRANSACTION 114893, ACTIVE 2611 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 48609 lock struct(s), heap size 4726992, 585460 row lock(s), undo log entries 1132742 MySQL thread id 12620, OS thread handle 123145553027072, query id 38123782 localhost 127.0.0.1 root update insert into edges (---some values--) Trx read view will not see trx with id >= 114862, sees < 114817 ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 408 page no 135298 n bits 240 index PRIMARY of table `**database**.edges` trx id 114893 lock_mode X insert intention waiting Record lock, heap no 1 1 Answers
Answers 1
You are using the repeatable read isolation level. In the repeatable read isolation level so called gap locks are used and are held for the duration of the transaction (you can read more about gap locks in the documentation). If you switch the isolation level from repeatable read to read committed, the problem will go away. You can set the isolation level with
set transaction isolation level read committed You should check the documentation of the command. The isolation level can be set at a session level or global level.
0 comments:
Post a Comment