looking into random failures and deadlock situations with regards to running several archive.php crons in parallel (what is done when triggering the segments to pre-archive).

The related code is at: https://github.com/piwik/piwik/blob/master/core/DataAccess/ArchiveWriter.php#L95

There is an example of travis failure showing the ENGINE INNODB STATUS with output:
https://travis-ci.org/piwik/piwik/jobs/11862693

From Anthon:

There are S (shared) locks and X (exclusive) locks. And there can't be simultaneous S and X locks on the same row.

The DELETE is the older transaction. It tries to acquire a X lock on each row to be deleted. Before it can complete, the SELECT sub-query -- which I believe comes from max(idarchive) -- tries to acquire a S lock on one of these locked rows.

So, it looks like advisory locks are needed to avoid this race condition.