mysql deadlock multithreaded application

#Mysql deadlock multithreaded application update#

(uniq identity: 239302521) START TRANSACTION įROM `tg_users` WHERE `tg_user_id` = '239302521' FOR UPDATE (Step 2) Open Connection 2 and do select query. Keep connection 1 open, create connection 2. (uniq identity: 341292662) START TRANSACTION Reproduction Steps: - (Step 1) Open Connection 1 and do select query. (so I think the row lock is not relevant since they are working with different row). Connection 1 and connection 2 have different tg_user_id on its queries.Just to emphasize things that may be important to check: If the such data exists, then compare and update.If the user has not been stored in database, then insert it.Select user information from database based on tg_user_id.) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci KEY `private_msg_count` (`private_msg_count`), KEY `group_msg_count` (`group_msg_count`), `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `is_bot` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0', `private_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0', `group_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0', `photo` bigint(20) unsigned DEFAULT NULL, `last_name` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL, `first_name` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `tg_user_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

Below is the detailed information: DDL CREATE TABLE `tg_users` ( I was able to reproduce the deadlock in a simple scenario, but do not understand why does the deadlock happen.

I tried to read the stack trace from my app and report from SHOW ENGINE INNODB STATUS. The error says SQLSTATE: Serialization failure: 1213 Deadlock found when trying to get lock try restarting transaction. I noticed that there are so many errors occur related to deadlock. I have a running app with concurrency transactions in it.

Mysql deadlock multithreaded application