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

https://github.com/matrix-org/synapse/blob/69048f7b4848ab6a4ae6cb233f8cbf36d73c0ba1/synapse/storage/databases/main/devices.py#L1911-L1919

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 https://github.com/matrix-org/synapse/blob/69048f7b4848ab6a4ae6cb233f8cbf36d73c0ba1/synapse/storage/databases/main/devices.py#L1907-L1909 if we're using REPEATABLE READ isolation

[DMRobertson]

Spotted in https://github.com/matrix-org/synapse/issues/16138#issuecomment-1705533637

[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