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:
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.
In fd5111b: Refs #4186 Adding advisory locking before running the DELETE query
I believe this bug is fixed, the error did not happen in travis recently.
Refs #4186 Adding advisory locking before running the DELETE query