bug #45714 [Messenger] Fix cannot select FOR UPDATE from view on Oracle (rjd22)

nicolas-grekas · nicolas-grekas · commit deb90309e40b · 2022-04-01T12:14:40.000+02:00
This PR was merged into the 4.4 branch. Discussion ---------- [Messenger] Fix cannot select FOR UPDATE from view on Oracle | Q | A | ------------- | --- | Branch? | 4.4  | Bug fix? | yes | New feature? | no | Deprecations? | no | Tickets | Fix #33718 | License | MIT I realize this solution is quite getto. I hope to get some feedback on it so we can solve this oracle issue properly but ATM this is a good starting point to reach a proper solution. For this reason I didn't add tests yet. What happens is that Oracle doesn't like query's that combine ROWNUM and FOR UPDATE. To solve this the query needs to be wrapped in a subquery that limits the results by ID so: ```sql SELECT a.* FROM ( SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null OR m.delivered_at < ?) AND (m.available_at <= ?) AND (m.queue_name = ?) ORDER BY available_at ASC ) a WHERE ROWNUM <= 1 FOR UPDATE; ``` becomes: ```sql SELECT * FROM messenger_messages WHERE id in ( SELECT a.id FROM ( SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null OR m.delivered_at < ?) AND (m.available_at <= ?) AND (m.queue_name = ?) ORDER BY available_at ASC ) a WHERE ROWNUM <= 1 ) FOR UPDATE; ``` I don't really know another way to solve this better. But feel free to nitpick at my solution. Commits ------- d4a695f [Messenger] Fix cannot select FOR UPDATE from view on Oracle
diff --git a/src/Symfony/Component/Messenger/Transport/Doctrine/Connection.php b/src/Symfony/Component/Messenger/Transport/Doctrine/Connection.php
@@ -19,6 +19,7 @@
 use Doctrine\DBAL\Exception\TableNotFoundException;
 use Doctrine\DBAL\LockMode;
 use Doctrine\DBAL\Platforms\MySQLPlatform;
+use Doctrine\DBAL\Platforms\OraclePlatform;
 use Doctrine\DBAL\Query\QueryBuilder;
 use Doctrine\DBAL\Result;
 use Doctrine\DBAL\Schema\AbstractSchemaManager;
@@ -187,6 +188,18 @@ public function get(): ?array
                 );
             }
 
+            // Wrap the rownum query in a sub-query to allow writelocks without ORA-02014 error
+            if ($this->driverConnection->getDatabasePlatform() instanceof OraclePlatform) {
+                $sql = str_replace('SELECT a.* FROM', 'SELECT a.id FROM', $sql);
+
+                $wrappedQuery = $this->driverConnection->createQueryBuilder()
+                    ->select('w.*')
+                    ->from($this->configuration['table_name'], 'w')
+                    ->where('w.id IN('.$sql.')');
+
+                $sql = $wrappedQuery->getSQL();
+            }
+
             // use SELECT ... FOR UPDATE to lock table
             $stmt = $this->executeQuery(
                 $sql.' '.$this->driverConnection->getDatabasePlatform()->getWriteLockSQL(),
