1

I came across the situation where deadlock was found while inserting the records in the child table.

Let me explain the scenario in detail

I am having two tables

1. table_a | (parent table)

TABLE_NAME COLUMN_NAME COLUMN_KEY
table_a ID PRIMARY_KEY
table_a NAME -
table_a DESCRIPTION -

2. table_b (child table)

TABLE_NAME COLUMN_NAME COLUMN_KEY
table_b ID PRIMARY_KEY
table_b NAME -
table_b VALUE -
table_b REF_ID FOREIGN_KEY (from table_a ID)

Below are the steps that I am following to insert records:

  1. Begin Transaction; (auto commit is disabled)
  2. INSERT INTO table_a;
  3. Preparing required objects
  4. INSERT INTO table_b;
  5. COMMIT/ROLLABACK Transaction;

While reaching at step 4 :

"Deadlock found when trying to get lock; try restarting transaction Query being executed when exception was thrown: INSERT INTO table_b (NAME, VALUE, REF_ID) VALUES ("TEST", 'TEST_VALUE', 10) com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction Query being executed when exception was thrown: INSERT INTO table_b (NAME, VALUE, REF_ID) VALUES ("TEST", 'TEST_VALUE', 10)"

Note : I am not doing any other alter operation on the same row.

ISOLATION_LEVEL : REPEATABLE-READ

Please let me know, if any other information are needed. I will update the question as per the request.

Jaypal Sodha
  • 2,238
  • 2
  • 10
  • 22
  • See if this post helps you out with your Deadlock issue [Stack Post](https://stackoverflow.com/q/2332768/15405352) – Anil Parshi Sep 29 '21 at 13:14
  • Thanks for the reply, But my use case different. – Jaypal Sodha Sep 30 '21 at 04:23
  • why the isolation_level is repeatable-read is it on purpose as this might cause problems,you should use read-committed and also create an index for ref_id column in table_b as this is needed in case you update the parent rows. – user06062019 Oct 01 '21 at 05:55
  • We have created the index for ref_id column, let me update the question. – Jaypal Sodha Oct 01 '21 at 06:37

0 Answers0