Serialization failure

[ptrykulich]

What happened?

Description

I programmatically store about 3 thousand entries, each of which contains a Neo matrix and other fields.
I divide the queue into queues of 250 records and they are processed 3-5 at a time.

Steps to reproduce

Restore the entries of the same section in different queues, using 3-5 runners at the same time.

Expected behavior

Performing a resave without errors.

Actual behavior

Errors occur during the resave process:

• Something went wrong: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
  The SQL being executed was: INSERT INTO searchindex (elementId, attribute, fieldId, siteId, keywords) VALUES (6570286, 'slug', 0, 1, '') | File: vendor/yiisoft/yii2/db/Schema.php | Line: 676
• Tried to roll back a savepoint, but it does not exist: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL1 does not exist
  The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL1

Craft CMS version

Craft Pro 4.7.0

PHP version

8.1.28

Operating system and version

Linux 6.5.0-1018-aws

Database type and version

MySQL 8.0.35

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.11-60)

Installed plugins and versions

Plugin/Component	Version
Amazon S3	2.0.3
Cloudflare	2.0.2
CP Field Inspect	1.4.4
Embedded Assets	3.2.0
FastCGI Cache Bust	4.0.1
Feed Me	5.3.0
Formie	2.1.18
Navigation	2.0.24
Neo	4.0.2
Redactor	3.0.4
Redactor Custom Styles	4.0.3
Redirect Manager	2.0.0
Section + Product Type	2.0.3
Sentry Logger	4.1.4
SEOmatic	4.0.38
Typed link field	2.1.5
User Activity	2.3.16
Yii version	2.0.48.1
Twig version	v3.4.3
Guzzle version	7.8.1

[brandonkelly]

Thanks for reporting that @ptrykulich. It looks like you’re hitting deadlocks due to gap locks. We’ll discuss this internally and see what should be done about it.

In the meantime, you should be able to avoid the errors by only having one queue runner handling these jobs.

[ptrykulich]

@brandonkelly

I could use one handler for the queue if I could handle these tasks through a separate runner. Because I need that these big queues did not stop other tasks and this queue was not stopped by others.

I talked about this in Discord with your staff and got a response that I think would be good for me. And get the expected implementation:

// The default Craft queue:
craft queue/listen --channel=default

// On a separate runner:
craft queue/listen --channel=big

• There will be a new config setting where you can define and queue components you want shown in the CP utility
• As long as it is configured there, and implements \craft\queue\QueueInterface, they will both show in the utility.
• Your queue component should register it's own CLI commands, so you have have 2 dedicated listen commands.

But it has not yet been implemented.

[brandonkelly]

It’s already possible to create a custom queue and push those big jobs to it.

// config/app.php

return [
    // ...
    'bootstrap' => [
        'resaveQueue',
    ],
    'components' => [
        'resaveQueue' => [
            'class' => craft\queue\Queue::class,
        ],
    ],
];

// your module
Craft::$app->resaveQueue->push(new MyResaveJob(/* ... */));

The only caveat is that those jobs won’t show up in the Queue Manager utility (#13347). But all the CLI commands will be available.

php craft help resaveQueue

[ptrykulich]

Thanks! We will use this solution! But at some point would be great to have deadlock problem sorted.

[brandonkelly]

Yeah it’s on the list to discuss on our next team call on Monday.

[samhibberd]

@brandonkelly did you work out a solution / plan for this? we run into it in exactly the same scenario, worked around by batching them and running sequentially.

[brandonkelly]

Whoops, forgot to follow up. We decided to take a low-fi approach and just try again a couple times if a deadlock occurs. Just implemented that for the next v4 and v5 releases.

[brandonkelly]

Craft 4.13.4 and 5.5.4 are out with that change.