[4.x]: Duplicate key error in revisions table when entry takes too long to save

[elivz]

What happened?

Description

We are getting intermittent Integrity constraint violation: 1062 Duplicate entry '1234-56' for key 'revisions_sourceId_num_unq_idx' errors when saving complex entries that take a few seconds to save. We have tracked this down to an interaction with the Gold Site Copy X plugin, although I don't believe it can be truly solved from that side of things.

Essentially what I believe is happening is:

1. The author saves an entry with one or more sites set to copy using Site Copy
2. Craft begins to update the database with the changes, while at the same time Site Copy creates a queue job to save the same entry into the selected site
3. By the time Craft's main process gets to the stage of creating a revision, the separate queue process is also doing the same thing
4. Since there is a hard-coded 3 second mutex lock on getting the revision ID, if it takes longer than three seconds to create the revision, you can get a collision

Steps to reproduce

1. Run Craft's queue in a separate process such that it runs simultaneously with the main web process
2. We are storing Mutex locks in Redis – not sure if that is relevant or not though
3. Save an entry using Site Copy (or presumably any other plugin that spawns a queue job to update the same entry)

References

• https://github.com/craftcms/cms/issues/7598
• https://github.com/Goldinteractive/craft-sitecopy/issues/9

Craft CMS version

4.5.11.1

PHP version

8.1.27

Operating system and version

No response

Database type and version

MySQL 8

Image driver and version

No response

Installed plugins and versions

Amazon S3 2.0.3 Blitz 4.9.3 CP Field Inspect 1.4.4 Embedded Assets 3.1.9 Environment Label 4.0.3 Expanded Singles 2.0.5 Feed Me 5.3.0 Forms 4.1.13 Guide 3.2.2 KeyChain 4.0.0 Linkit 4.0.4.1 MatrixMate 2.1.4 Navigation 2.0.22 Redactor 3.0.4 Redirects 4.1.14 Retcon 2.7.5 SAML Service Provider 4.0.5 Scout 3.3.2 SEO 4.0.36 Site Copy X 1.0.10 Smith 2.0.0 Sprig 2.7.2 Super Table 3.0.12 Template Comments 4.0.0 Translations 3.2.10 Twigpack 4.0.0-beta.4 Wordsmith 4.3.0 Workflow 2.0.6

[brandonkelly]

Thanks for digging into that! I’ve just PR’d a couple fixes to the Site Copy plugin, which will prevent this from happening (Goldinteractive/craft-sitecopy#10).

Since there is a hard-coded 3 second mutex lock on getting the revision ID, if it takes longer than three seconds to create the revision, you can get a collision

That 3 second duration is just how long we’ll wait around to acquire the lock if it’s already in use, before giving up. Once it’s acquired, it doesn’t matter how long the revision process takes; the lock will stay acquired until we release it. The problem was that Site Copy wasn’t trying to acquire the lock as well.