TRANSACTION 42061, ACTIVE 55 sec starting index read Let's look at the output of SHOW ENGINE INNODB STATUS. Connection 1 commit Ĭonnection 2 now stops waiting and executes its command. Connection 2 update `user_chats` set `updated_at` = ' 08:33:14' where `id` = 1 Ĭonnection 2 now is the one waiting now, but it has not deadlocked. Reproducing the same steps as before Connection 1 USE dba210949 Īt this point, Connection 1 executes, instead of waiting like before. The only difference this time around is the removal of the user_chat_messages_user_chat_id_foreign foreign key. Let's repeat the same steps, but with the following table structures. ![]() Restarting transaction Retrying without the foreign key Connection 2 update `user_chats` set `updated_at` = ' 08:33:14' where `id` = 1 ĮRROR 1213 (40001): Deadlock found when trying to get lock try Insert 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: ![]() I provide below the raw MySQL query and also the code in which I do that programatically.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |