From below logs I see that both transactions have aquired lock on same space id and page no. They are also waiting for lock to be granted on same.
This is happening as mulitiple instances of application are trying to insert at same time.
How can I avoid this?
-----------------
LATEST DETECTED DEADLOCK
------------------------
2023-05-24 05:45:00 0x7f65f09fc700
*** (1) TRANSACTION:
TRANSACTION 15999259, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 4541303, OS thread handle 140058336655104, query id 142989411 10.100.146.68 F_API update
insert into categorised_domains
(domain_id, domain, crc32_domain_id, c_category_name, c_category_code, i_cat_id, i_cat_name)
values
(84533729, 'a.co.kr', 2926358864, 'Clothing and Accessories', 'C-0', 'I18', 'Style & fashion') ,
(84533729, 'a.co.kr', 2926358864, 'Jewelry and Watches', 'C-55', 'I18', 'Style &fashion')
ON DUPLICATE KEY UPDATE update_timestamp = CURRENT_TIMESTAMP
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 30 page no 28 n bits 208 index PRIMARY of table `Sparrow`.`categorised_domains` trx id 15999259 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 28 n bits 208 index PRIMARY of table `Sparrow`.`categorised_domains` trx id 15999259 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 15999258, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 4541311, OS thread handle 140058312537856, query id 142989409 10.100.146.66 F_API update
insert into
categorised_domains
(domain_id, domain, crc32_domain_id, c_category_name, c_category_code, i_cat_id, i_cat_name)
values
(84833196, 'j.com', 210917690, 'Business Services', 'C-1', 'I3', 'Business') ,
(84833196, 'j.com', 210917690, 'Web Services', 'C-6', 'I-18', 'Internet Technology') ,
(84833196, 'j.com', 210917690, 'Consumer Services', 'C-11', 'I3', 'Business') ,
(84833196, 'j.com', 210917690, 'Blogs/Forums/Social Networks', 'C-42', 'I19', 'Technology & Computing')
ON DUPLICATE KEY UPDATE update_timestamp = CURRENT_TIMESTAMP
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 30 page no 28 n bits 208 index PRIMARY of table `Sparrow`.`categorised_domains` trx id 15999258 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 28 n bits 208 index PRIMARY of table `Sparrow`.`categorised_domains` trx id 15999258 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
------------
also the amount of data is not much still deadlock is happening frequently
could this be issue with index?
CREATE TABLE `categorised_domains` (
`id` bigint NOT NULL AUTO_INCREMENT,
`domain_id` bigint NOT NULL,
`domain` varchar(255) NOT NULL,
`crc32_domain_id` int unsigned NOT NULL,
`c_category_name` varchar(2048) DEFAULT NULL,
`c_category_code` varchar(255) NOT NULL,
`i_cat_id` varchar(255) DEFAULT NULL COMMENT 'IAB Category ID',
`i_cat_name` varchar(255) DEFAULT NULL COMMENT 'IAB Category Name',
`create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `domain_key` (`domain`,`c_category_code`),
KEY `domain_id_idx` (`domain_id`),
KEY `create_timestamp_idx` (`create_timestamp`)
