0

I have a program (in this case in QT) which tries to insert 4000 records simultaneously into source_names table by 8 threads: 500 records by each thread.

My table is as follows:

CREATE TABLE `source_names` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`source_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=101952 DEFAULT CHARSET=latin1;

As specified in the above code, my source_name column is UNIQUE .

So I have 8 threads that each of them executes this codes at the same time:

INSERT IGNORE INTO source_names (source_name) VALUES ('a001'),('a002'),...,('a500')

and if there is a duplicate source_name, there is a warning and nothing will be happen.

But the main problem is that sometimes there is Deadlock error and I can not handle it:

Deadlock found when trying to get lock; try restarting transaction QMYSQL3: Unable to execute statement

I think this is because of UNIQUE column. If I use just one thread there is no problem, but how can I solve that with multiple threads?

morteza ali ahmadi
  • 435
  • 7
  • 15

1 Answers1

1

From MySQL Reference Manual https://dev.mysql.com/doc/refman/5.7/en/concurrent-inserts.html

If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements.

Since MySQL does not insert records simultaneously, you can use a single thread and avoid deadlocks.

If you must have multi threads that INSERT, consider using try-catch blocks. If deadlock occurs, try to insert again. If you end up in an infinite loop, put a counter and don't try to insert forever. Instead try to insert only a predefined time(counter) by you.

ero
  • 26
  • 1
  • 3