Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbdaabc0

Browse files
committed
fix#52273 [doctrine-messenger] DB table locks on messenger_messages with many failures
1 parent026b36b commitbdaabc0

File tree

2 files changed

+14
-13
lines changed

2 files changed

+14
-13
lines changed

‎src/Symfony/Component/Messenger/Bridge/Doctrine/Tests/Transport/ConnectionTest.php‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -394,32 +394,32 @@ public static function providePlatformSql(): iterable
394394
{
395395
yield'MySQL' => [
396396
class_exists(MySQLPlatform::class) ?newMySQLPlatform() :newMySQL57Platform(),
397-
'SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY available_at ASC LIMIT 1 FOR UPDATE',
397+
'SELECT m.* FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY available_at ASC LIMIT 1 FOR UPDATE',
398398
];
399399

400400
if (class_exists(MariaDBPlatform::class)) {
401401
yield'MariaDB' => [
402402
newMariaDBPlatform(),
403-
'SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY available_at ASC LIMIT 1 FOR UPDATE',
403+
'SELECT m.* FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY available_at ASC LIMIT 1 FOR UPDATE',
404404
];
405405
}
406406

407407
yield'SQL Server' => [
408408
class_exists(SQLServerPlatform::class) && !class_exists(SQLServer2012Platform::class) ?newSQLServerPlatform() :newSQLServer2012Platform(),
409-
'SELECT m.* FROM messenger_messages m WITH (UPDLOCK, ROWLOCK) WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY available_at ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY',
409+
'SELECT m.* FROM messenger_messages m WITH (UPDLOCK, ROWLOCK) WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY available_at ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY',
410410
];
411411

412412
if (!class_exists(MySQL57Platform::class)) {
413413
// DBAL >= 4
414414
yield'Oracle' => [
415415
newOraclePlatform(),
416-
'SELECT w.id AS "id", w.body AS "body", w.headers AS "headers", w.queue_name AS "queue_name", w.created_at AS "created_at", w.available_at AS "available_at", w.delivered_at AS "delivered_at" FROM messenger_messages w WHERE w.id IN (SELECT m.id FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY available_at ASC FETCH NEXT 1 ROWS ONLY) FOR UPDATE',
416+
'SELECT w.id AS "id", w.body AS "body", w.headers AS "headers", w.queue_name AS "queue_name", w.created_at AS "created_at", w.available_at AS "available_at", w.delivered_at AS "delivered_at" FROM messenger_messages w WHERE w.id IN (SELECT m.id FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY available_at ASC FETCH NEXT 1 ROWS ONLY) FOR UPDATE',
417417
];
418418
}else {
419419
// DBAL < 4
420420
yield'Oracle' => [
421421
newOraclePlatform(),
422-
'SELECT w.id AS "id", w.body AS "body", w.headers AS "headers", w.queue_name AS "queue_name", w.created_at AS "created_at", w.available_at AS "available_at", w.delivered_at AS "delivered_at" FROM messenger_messages w WHERE w.id IN (SELECT a.id FROM (SELECT m.id FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY available_at ASC) a WHERE ROWNUM <= 1) FOR UPDATE',
422+
'SELECT w.id AS "id", w.body AS "body", w.headers AS "headers", w.queue_name AS "queue_name", w.created_at AS "created_at", w.available_at AS "available_at", w.delivered_at AS "delivered_at" FROM messenger_messages w WHERE w.id IN (SELECT a.id FROM (SELECT m.id FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY available_at ASC) a WHERE ROWNUM <= 1) FOR UPDATE',
423423
];
424424
}
425425
}
@@ -495,32 +495,32 @@ public function provideFindAllSqlGeneratedByPlatform(): iterable
495495
{
496496
yield'MySQL' => [
497497
class_exists(MySQLPlatform::class) ?newMySQLPlatform() :newMySQL57Platform(),
498-
'SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) LIMIT 50',
498+
'SELECT m.* FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) LIMIT 50',
499499
];
500500

501501
if (class_exists(MariaDBPlatform::class)) {
502502
yield'MariaDB' => [
503503
newMariaDBPlatform(),
504-
'SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) LIMIT 50',
504+
'SELECT m.* FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) LIMIT 50',
505505
];
506506
}
507507

508508
yield'SQL Server' => [
509509
class_exists(SQLServerPlatform::class) && !class_exists(SQLServer2012Platform::class) ?newSQLServerPlatform() :newSQLServer2012Platform(),
510-
'SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY',
510+
'SELECT m.* FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY',
511511
];
512512

513513
if (!class_exists(MySQL57Platform::class)) {
514514
// DBAL >= 4
515515
yield'Oracle' => [
516516
newOraclePlatform(),
517-
'SELECT m.id AS "id", m.body AS "body", m.headers AS "headers", m.queue_name AS "queue_name", m.created_at AS "created_at", m.available_at AS "available_at", m.delivered_at AS "delivered_at" FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?) FETCH NEXT 50 ROWS ONLY',
517+
'SELECT m.id AS "id", m.body AS "body", m.headers AS "headers", m.queue_name AS "queue_name", m.created_at AS "created_at", m.available_at AS "available_at", m.delivered_at AS "delivered_at" FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?) FETCH NEXT 50 ROWS ONLY',
518518
];
519519
}else {
520520
// DBAL < 4
521521
yield'Oracle' => [
522522
newOraclePlatform(),
523-
'SELECT a.* FROM (SELECT m.id AS "id", m.body AS "body", m.headers AS "headers", m.queue_name AS "queue_name", m.created_at AS "created_at", m.available_at AS "available_at", m.delivered_at AS "delivered_at" FROM messenger_messages m WHERE (m.delivered_at is null ORm.delivered_at< ?) AND (m.available_at <= ?) AND (m.queue_name= ?)) a WHERE ROWNUM <= 50',
523+
'SELECT a.* FROM (SELECT m.id AS "id", m.body AS "body", m.headers AS "headers", m.queue_name AS "queue_name", m.created_at AS "created_at", m.available_at AS "available_at", m.delivered_at AS "delivered_at" FROM messenger_messages m WHERE (m.queue_name = ?) AND (m.delivered_atis null OR m.delivered_at < ?) AND (m.available_at <= ?)) a WHERE ROWNUM <= 50',
524524
];
525525
}
526526
}

‎src/Symfony/Component/Messenger/Bridge/Doctrine/Transport/Connection.php‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -345,14 +345,15 @@ private function createAvailableMessagesQueryBuilder(): QueryBuilder
345345
$redeliverLimit = (clone$now)->modify(sprintf('-%d seconds',$this->configuration['redeliver_timeout']));
346346

347347
return$this->createQueryBuilder()
348-
->where('m.delivered_at is null OR m.delivered_at < ?')
348+
->where('m.queue_name = ?')
349+
->andWhere('m.delivered_at is null OR m.delivered_at < ?')
349350
->andWhere('m.available_at <= ?')
350-
->andWhere('m.queue_name = ?')
351351
->setParameters([
352+
$this->configuration['queue_name'],
352353
$redeliverLimit,
353354
$now,
354-
$this->configuration['queue_name'],
355355
], [
356+
Types::STRING,
356357
Types::DATETIME_MUTABLE,
357358
Types::DATETIME_MUTABLE,
358359
]);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp