Occasional UniqueViolation in device_lists_remote_cache_unique_id

[DMRobertson]

e.g.

• https://sentry.tools.element.io/organizations/element/issues/71346/
• https://sentry.tools.element.io/organizations/element/issues/78907/
• https://sentry.tools.element.io/organizations/element/issues/84351/

From looking at the source, the only insert that isn't an upsert I can see is

synapse/synapse/storage/databases/main/devices.py

Lines 1911 to 1919 in 69048f7

	self.db_pool.simple_insert_many_txn(
	txn,
	table="device_lists_remote_cache",
	keys=("user_id", "device_id", "content"),
	values=[
	(user_id, content["device_id"], json_encoder.encode(content))
	for content in devices
	],
	)

which might emit this error if

• the same (user, device) ID appears twice in this batch of queries, or
• this (user, device) pair already exists in the DB.

Tracebacks in Sentry seems to corroborate my suspicions --- they have the quoted function in their stack.

[DMRobertson]

this (user, device) pair already exists in the DB.

Although you'd expect that to be ensured by

synapse/synapse/storage/databases/main/devices.py

Lines 1907 to 1909 in 69048f7

	self.db_pool.simple_delete_txn(
	txn, table="device_lists_remote_cache", keyvalues={"user_id": user_id}
	)

if we're using REPEATABLE READ isolation

[DMRobertson]

Spotted in #16138 (comment)

[DMRobertson]

I also spotted SerializationFailures for this table:

• https://sentry.tools.element.io/organizations/element/issues/83491
• https://sentry.tools.element.io/organizations/element/issues/85253
• https://sentry.tools.element.io/organizations/element/issues/84404

[erikjohnston]

This is probably happening when we call that function twice at the same time, while having no existing entries in the DB. That way the DELETE is a no-op that doesn't lock any rows, so both transactions try to insert