dml_write_exception SQLSTATE 40001 on deadlock: retrying delegated transactions without duplicate writes

Deadlock retries in a Moodle plugin

I am writing a Moodle 4.5 plugin whose scheduled task performs several related writes inside $DB->start_delegated_transaction(). Under concurrent load, MySQL can abort the transaction with a dml_write_exception carrying SQLSTATE 40001 (deadlock). As far as I can tell, Moodle's DML layer throws this exception but does not retry the transaction automatically, so the retry loop has to live in my own code.

The complication is that the task's execute() method mixes inserts and updates. If the deadlock hits partway through and I simply re-run the whole method, I risk duplicate rows for the inserts that already committed in a prior attempt or in a nested transaction scope. The scheduled task retry flag has the same problem: it re-executes the entire method.

My constraints: I must stay on core DML APIs (no raw driver calls), the task may run on both MySQL and PostgreSQL, and I cannot add unique indexes for every logical write because some tables legitimately allow repeated values.

Is catching dml_write_exception and checking SQLSTATE 40001 the correct, portable way to detect a retryable deadlock in Moodle's DML? What is the recommended pattern for making a multi-write delegated transaction idempotent so a retry cannot duplicate inserts? Should the retry wrap the whole execute() or only the transaction block?