7/1/2023 0 Comments Wordpress mysql deadlockInsert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk') Ĭonnection 1 update `user_chats` set `updated_at` = ' 08:33:14' where `id` = 1 Īt this point, Connection 1 is waiting. Reproducing the deadlock Connection 1 USE dba210949 It is not important for reproducing the problem. Note that I removed the user_chat_messages_from_user_id_foreign foreign key as it references the users table, which we don't have in our example. Insert into user_chats (id,updated_at) values (1,NOW()) Setup CREATE DATABASE dba210949 ĬONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id) The FOREIGN KEY user_chat_messages_user_chat_id_foreign is the cause of your deadlock, in this situation.įortunately, this is easy to reproduce given the information you've provided. Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,ĬONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id),ĬONSTRAINT user_chat_messages_from_user_id_foreign FOREIGN KEY (from_user_id) REFERENCES users (id)ĬREATE INDEX user_chat_messages_from_user_id_index ON user_chat_messages (from_user_id) ĬREATE INDEX user_chat_messages_user_chat_id_index ON user_chat_messages (user_chat_id) Id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,Ĭreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, >update() ĭDL for the tables: CREATE TABLE user_chat_messages TODO results in lock error if many messages are sent same time 'from_user_id' => $message->getFromUserId(), 'user_chat_id' => $message->getUserChatId(), $id = $this->db->table('user_chat_messages')->insertGetId([ Public function insertChatMessage(UserChatMessageEntity $message) : int Set `updated_at` = ' 08:33:14' where `id` = 1 Ībove is the raw query, but I do it in PHP Laravel Query Builder as follows: /** Insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) I know that a lock is acquired for inserting/updating/deleting tables in MySql with InnoDB but still do not understand why the deadlock happens here and how to solve it in the most efficient way. Is there any way to solve this without locking the entire table ? (Want to try avoid table level locks) Why ? (The transaction aquires lock, right ?) If I execute the same query but without transaction block around it will work without error with many concurrent calls. SQLSTATE: Serialization failure: 1213 Deadlock found when trying to get lock try restarting transaction (SQL: update user_chats set updated_at = 10:07:13 where id = 1) If two requests are being done at the same time results in the following error pattern: *************************** 1.I provide below the raw MySQL query and also the code in which I do that programatically. Here is the show create table: mysql> show create table n0_table\G Thank you and let me know if case other information is required. In my understanding the auto increment should be working without deadlocks in this case but I am probably missing something. I am trying to figure out why those deadlocks are occurring and also how to prevent them. There is a slave which is configured in case the master fails.The application servers then use one and the same MySQL master database.Requests go to two load balancers and from there are forwarded to 4 application servers.The hosting setup for this website which generates those deadlocks is the following: TABLE LOCK table `staging`.`n0_table` trx id 177706917 lock mode AUTO-INC waitingĬurrently the MySQL InnoDB lock mode is: - - *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 480 page no 44003 n bits 760 index `post_id` of table `staging`.`n0_table` trx id 177706917 lock_mode X INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411901, '_wc_customer_order_csv_export_customer_is_exported', '0') TRANSACTION 177706917, ACTIVE 1 sec setting auto-inc lockġ21 lock struct(s), heap size 13864, 15196 row lock(s), undo log entries 93 RECORD LOCKS space id 480 page no 44003 n bits 768 index `post_id` of table `staging`.`n0_table` trx id 177707078 lock_mode X insert intention waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411902, 'from_header', 'barberklingen ') LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 TRANSACTION 177707078, ACTIVE 0 sec inserting I am experiencing an issue with MySQL deadlocks and here is what I see in the MySQL error log: 09:09:29 7fd5235db700InnoDB: transactions deadlock detected, dumping detailed information.
0 Comments
Leave a Reply. |