I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL:
update user_chats set updated_at = 2018-06-29 10:07:13 where id = 1
)
If I execute the same query but without transaction block around it will work without error with many concurrent calls. Why ? (The transaction aquires lock, right ?)
Is there any way to solve this without locking the entire table ? (Want to try avoid table level locks)
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.
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`)
values (1, 2, 'dfasfdfk);
update `user_chats`
set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;
COMMIT;
Above is the raw query, but I do it in PHP Laravel Query Builder as follows:
/**
* @param UserChatMessageEntity $message
* @return int
* @throws \Exception
*/
public function insertChatMessage(UserChatMessageEntity $message) : int
{
$this->db->beginTransaction();
try
{
$id = $this->db->table('user_chat_messages')->insertGetId([
'user_chat_id' => $message->getUserChatId(),
'from_user_id' => $message->getFromUserId(),
'content' => $message->getContent()
]
);
//TODO results in lock error if many messages are sent same time
$this->db->table('user_chats')
->where('id', $message->getUserChatId())
->update(['updated_at' => date('Y-m-d H:i:s')]);
$this->db->commit();
return $id;
}
catch (\Exception $e)
{
$this->db->rollBack();
throw $e;
}
}
DDL for the tables:
CREATE TABLE user_chat_messages
(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
user_chat_id INT(10) unsigned NOT NULL,
from_user_id INT(10) unsigned NOT NULL,
content VARCHAR(500) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id),
CONSTRAINT user_chat_messages_from_user_id_foreign FOREIGN KEY (from_user_id) REFERENCES users (id)
);
CREATE INDEX user_chat_messages_from_user_id_index ON user_chat_messages (from_user_id);
CREATE INDEX user_chat_messages_user_chat_id_index ON user_chat_messages (user_chat_id);
CREATE TABLE user_chats
(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
Best Answer
The FOREIGN KEY
user_chat_messages_user_chat_id_foreign
is the cause of your deadlock, in this situation.Fortunately, this is easy to reproduce given the information you've provided.
Setup
Note that I removed the
user_chat_messages_from_user_id_foreign
foreign key as it references theusers
table, which we don't have in our example. It is not important for reproducing the problem.Reproducing the deadlock
Connection 1
Connection 2
Connection 1
At this point, Connection 1 is waiting.
Connection 2
Here, Connection 2 throws a deadlock
Retrying without the foreign key
Let's repeat the same steps, but with the following table structures. The only difference this time around is the removal of the
user_chat_messages_user_chat_id_foreign
foreign key.Reproducing the same steps as before
Connection 1
Connection 2
Connection 1
At this point, Connection 1 executes, instead of waiting like before.
Connection 2
Connection 2 now is the one waiting now, but it has not deadlocked.
Connection 1
Connection 2 now stops waiting and executes its command.
Connection 2
Done, with no deadlock.
Why?
Let's look at the output of
SHOW ENGINE INNODB STATUS
You can see that transaction 1 has a lock_mode X on the PRIMARY key of
user_chats
, while transaction 2 has lock_mode S, and is waiting for lock_mode X. That is a result of it obtaining a shared lock first (from ourINSERT
statement), and then an exclusive lock (from ourUPDATE
).So, what is happening is Connection 1 grabs the shared lock first, and then Connection 2 grabs a shared lock on the same record. That's fine, for now, as they are both shared locks.
Connection 1 then tries to upgrade to an exclusive lock to perform the UPDATE, only to find that connection 2 has a lock already. Shared and exclusive locks don't mix well, as you can probably deduce by their name. That is why it waits after the
UPDATE
command on Connection 1.Then Connection 2 tries to
UPDATE
, which requires an exclusive lock, and InnoDB goes "whelp, I'm never going to be able to fix this situation on my own", and declares a deadlock. It kills off Connection 2, releases the shared lock that Connection 2 was holding, and allows Connection 1 to complete normally.Solution(s)
At this point, you are probably ready to stop with the yap yap yap and want a solution. Here are the my suggestions, in order of my personal preference.
1. Avoid the update altogether
Don't bother with the
updated_at
column in theuser_chats
table at all. Instead, add a composite index onuser_chat_messages
for the columns (user_chat_id
,created_at
).Then, you can obtain the most recent updated time with the following query.
This query will execute extremely quickly due to the index, and doesn't require you to store the latest
updated_at
time in theuser_chats
table as well. This helps avoid data duplication, which is why it is my preferred solution.Make sure to dynamically set the
id
to the$message->getUserChatId()
value, and not hard coded to1
, as in my example.This is essentially what Rick James is suggesting.
2. Lock the tables to serialize requests
Add this
SELECT ... FOR UPDATE
to the start of your transaction, and it will serialize your requests. As before, make sure to dynamically set theid
to the$message->getUserChatId()
value, and not hard coded to1
, as in my example.This is what Gerard H. Pille is suggesting.
3. Drop the foreign key
Sometimes, it is just easier to remove the source of the deadlock. Just drop the
user_chat_messages_user_chat_id_foreign
foreign key, and problem solved.I don't particularly like this solution in general, as I love data integrity (which the foreign key provides), but sometimes you need to make trade offs.
4. Retry the command after deadlock
This is the recommended solution for deadlocks in general. Just catch the error, and retry the entire request. However, it is easiest to implement if you prepared for it from the start, and updating legacy code may be difficult. Given the fact that there are easier solutions (like 1 and 2 above) is why this is my least recommended solution for your situation.