Сегодня наша производственная база данных (Aurora PostgreSQL 9.6.3) столкнулась с тупиковой ситуацией, когда несколько процессов пытались выполнить один и тот же UPDATE
запрос к одной строке. Мы думали, что взаимоблокировка может возникнуть только при неупорядоченном обновлении нескольких строк, так что это было неожиданностью; однако это произошло в самое загруженное для нас время дня.
Вот транзакция в нашем коде Python, которая содержит оператор UPDATE
(это UPSERT
бедняка):
with self.connection.cursor() as cursor:
cursor.execute("""UPDATE students SET name = %s WHERE uuid = %s AND activity_id = %s""", (name, uuid, activityId))
if cursor.rowcount <= 0:
cursor.execute("""INSERT INTO students (name, uuid, activity_id) VALUES (%s, %s, %s)""", (name, uuid, activityId))
if cursor.rowcount <= 0:
self.connection.rollback()
raise BaseDao.NotUpserted("No student name was updated or inserted for activity_id %d and uuid %s" % (activityId, uuid))
else:
self.connection.commit()
Вот несколько важных строк из журнала, включая простой запрос, который обновляет только одну строку:
...
2018-01-19 16:21:27 UTC:[38161]:ERROR: deadlock detected
2018-01-19 16:21:27 UTC:[38161]:DETAIL: Process 38161 waits for ShareLock on transaction 90490253; blocked by process 25147.
Process 25147 waits for ShareLock on transaction 90490267; blocked by process 38161.
Process 38161: UPDATE students SET name = 'foobar' WHERE uuid = 'ca1b2d153cbdc9574cce' AND activity_id = 35473237
Process 25147: UPDATE students SET name = 'foobar' WHERE uuid = 'ca1b2d153cbdc9574cce' AND activity_id = 35473237
...
И вот две соответствующие таблицы:
db=> \d students
Table "public.students"
Column | Type | Modifiers
-------------+------------------------+-------------------------------------------------------------------
id | integer | not null default nextval('students_id_seq'::regclass)
name | character varying(128) | not null
uuid | character varying(40) | not null
activity_id | integer | not null
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
"students_activity_id" btree (activity_id)
Foreign-key constraints:
"activity_id_refs_id_76c08098" FOREIGN KEY (activity_id) REFERENCES activities(id) DEFERRABLE INITIALLY DEFERRED
db=> \d activities
Table "public.activities"
Column | Type | Modifiers
-------------------+--------------------------+----------------------------------------------------------------------
id | integer | not null default nextval('activities_id_seq'::regclass)
start_time | timestamp with time zone | not null
end_time | timestamp with time zone |
activity_type | character varying(2) | not null
activity_id | integer | not null
started_by_id | integer | not null
activity_state | integer | not null
legacy_id | integer |
hide_report | boolean | not null
report_status | integer |
students_finished | text | not null
room_name | text |
last_updated | timestamp with time zone |
state | integer |
Indexes:
"activities_pkey" PRIMARY KEY, btree (id)
"activities_end_time" btree (end_time)
"activities_room_name_c1f9997a_like" btree (room_name text_pattern_ops)
"activities_room_name_c1f9997a_uniq" btree (room_name)
"activities_started_by_id" btree (started_by_id)
Foreign-key constraints:
"started_by_id_refs_id_5ea35c7a" FOREIGN KEY (started_by_id) REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "students" CONSTRAINT "activity_id_refs_id_76c08098" FOREIGN KEY (activity_id) REFERENCES activities(id) DEFERRABLE INITIALLY DEFERRED
Как мы можем попасть в тупик, когда обновляется только одна строка?
students
? Переписать правила? Какие две транзакции выполняются помимо этого обновления? Можете ли вы воспроизвести, выполнив обновления вручную из двух экземпляровpsql
? - person giorgiga   schedule 20.01.2018students
. К сожалению, я не знаю, какие еще две транзакции выполнялись помимо этого обновления, и я не могу воспроизвести вручную изpsql
. Однако с тех пор мы обнаружили, что непреднамеренно позволяли учащимся запускать несколько HTTP-запросов, чтобы установить свое имя (путем ввода имени и удерживания клавиши Enter). Мы думаем, что это может быть то, что произошло, но мы не знаем, как это доказать. - person Rob Johansen   schedule 20.01.2018REFERENCES activities(id) DEFERRABLE INITIALLY DEFERRED
может сыграть в этом роль, так как откладывает проверку FK до конца транзакции. Почему ваши ограничения FKDEFERRABLE INITIALLY DEFERRED
для начала? (А почему бы не использовать правильныйUPSERT
?) - person Erwin Brandstetter   schedule 20.01.2018rollback()
илиcommit()
в конце). Мне придется провести небольшое исследование, чтобы получить ответы на другие ваши вопросы. - person Rob Johansen   schedule 22.01.2018