orm: Syntax error or access violation: 1305 SAVEPOINT DOCTRINE_x does not exist

also if you’re tracking transaction level in custom connection middleware, the count will be totally broken after switching to savepoints.

Because without savepoints, the transaction level is pretty much a boolean?

Just read the full thread while trying to find out what @ro0NL meant by “eg. due SAVEPOINT DOCTRINE_2 does not exist” in #11423 … wouldn’t the next logical step be to file a DBAL bug report?

So, if I understand the issue correctly, the DBAL does not detect a deadlock correctly and thus assumes a wrong transaction nesting level? This needs to be fixed in the DBAL then.

both errors are real to me 😅 i mean, in both cases “concurrent mysql” is the issue

im not sure how to deal with it on DBAL/ORM side either. That’s future work for maintainers 😃

for now im going to opt-out our command from running transactional (it’s not a real requirement for this specific case), that solves the issue as well. Im not really happy with try/catching the commit/rollback flow really, even though the “return bool” API allows for it.

eventually maybe just take some error percentage for granted as well, but time will tell.

If savepoints are required for application, then developer should total control it.

but we cant in case of concurrency, because “mysql”. Note im not an infrastructural expert 😉

also IIUC doctrine/orm:^3 will enforce savepoints either way

i just thought it was nice to have command execution wrapped in a transaction by default

i think i’ll rewrite our transaction middleware so that it only creates an outer transaction, and thus avoid nested savepoints in this case.

I can reproduce it locally 😃

DROP TABLE IF EXISTS tbl1 ;
DROP TABLE IF EXISTS tbl2 ;

CREATE TABLE tbl1
(
    id VARCHAR(255) PRIMARY KEY,
    b  VARCHAR(255)
);
CREATE TABLE tbl2
(
    id VARCHAR(255) PRIMARY KEY,
    b  VARCHAR(255)
);

INSERT INTO tbl1 (id, b)
VALUES (1, 1);
INSERT INTO tbl2 (id, b)
VALUES (1, 1);

Test without savepoint

Test with savepoints

Sorry for more SQL.

By documentation from MySQL https://dev.mysql.com/doc/refman/8.0/en/innodb-error-handling.html DEADLOCK will lead rollback the entire transaction. As result, in session #2 after deadlock error we CANNOT execute rollback to savepoint, because MySQL roll back ENTIRE transaction, but by default logic in EntityManager::flush (UnitOfWork::commit) - after catched error system try to execute rollback.

P.S. This is only possible if save points are enabled. If savepoints are disabled (by default in Dbal 3.*) - developers not see this error, because system not open savepoints.

I’m guessing this is MySQL? Do you by change perform any DDL operations

Correct, and not that i’m aware of 😅

Since we already had a custom connection middleware, i’ve added this gem to exec() for now:

        if (str_starts_with($sql, 'ROLLBACK TO SAVEPOINT ')) {
            // @see https://github.com/doctrine/orm/issues/11230
            try {
                return parent::exec($sql);
            } catch (\Throwable $e) {
                $this->logger->warning('Failed to rollback to savepoint', ['exception' => $e]);

                return 0;
            }
        }

        return parent::exec($sql);

Will monitor prod today to see if it reveals our issue 👍