Как обойти deadlock при обновлении таблицы pgsql?
Есть таблица связей пользователь-задание user_tasks
| id | user_id | task_id | is_read | status |
|---|---|---|---|---|
| 1 | 1 | 1 | true | 1 |
| 2 | 1 | 1 | false | 2 |
и конкурентные запросы к ней:
update user_tasks set is_read = true where task_id = and user_id = ...update user task set status = ... where task_id = ...
Когда я меняю статус всем заданиям (условно помечаю неактивным), то сходу ловлю deadlock :
{
"application_name": "unknown - 10.129.0.10",
"backend_type": "client backend ",
"command_tag": "UPDATE",
"connection_from": "localhost:57284",
"context": "while updating tuple (800953,3) in relation \"user_task\"",
"database_name": "platform-prod",
"detail": "Process 374756 waits for ShareLock on transaction 206875731; blocked by process 381421. Process 381421 waits for ShareLock on transaction 206868365; blocked by process 374756. Process 374756: update \"user_task\" set \"status\" \\= $1, \"updated_at\" \\= $2 where \"task_id\" in ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24) Process 381421: update \"user_task\" set \"is_read\" \\= $1, \"read_at\" \\= $2, \"updated_at\" \\= $3 where \"ctid\" in (select \"user_task\".\"ctid\" from \"user_task\" inner join \"tasks\" on \"tasks\".\"id\" \\= \"user_task\".\"task_id\" and \"tasks\".\"client_id\" \\= $4 where \"user_id\" \\= $5 and \"is_read\" \\= $6)",
"error_severity": "ERROR",
"hint": "See server log for query details.",
"hostname": "rc1a-.mdb.yandexcloud.net",
"internal_query": "",
"internal_query_pos": "0",
"leader_pid": "0",
"location": "",
"message": "deadlock detected",
"process_id": "374756",
"query": "update \"user_task\" set \"status\" \\= $1, \"updated_at\" \\= $2 where \"task_id\" in ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24)",
"query_id": "0",
"query_pos": "0",
"session_id": "63188569.5b7e4",
"session_line_num": "1",
"session_start_time": "2022-09-07T14:50:01+03:00",
"sql_state_code": "40P01",
"transaction_id": "0",
"user_name": "platform-pro",
"virtual_transaction_id": "68/4657275"
}
Как мне это обойти и сделать обновление статуса, чтобы пользователи могли одновременно с обновлением статуса обновлять "прочитанность"?