[BUG]: Transaction manager does not create a new transaction after first commit

[serge-webspark]

Hi there! I am trying to figure out the transaction manager flow. In the documentation, I see that the manager should always return the active transaction (https://docs.phalcon.io/5.6/db-models-transactions/#dependency-injection), but in my case, I always receive the same transaction even after the commit. The transactional mode works only until the first commit or rollback, all future commits and rollbacks will not work because the "active" transaction is not under actual DB transaction. Please explain the behavior.

        // Inside controller action
        $user = Users::findFirst(173431);
        $transactionManager = $this->di->get('transactionManager');

        $transactionManager->get(true);
        $user->balance = 10;
        $user->save();
        $transactionManager->commit(); // Successful commit, balance changed to 10

        $transactionManager->get(true);
        $user->balance = 20;
        $user->save();
        $transactionManager->rollback(); // Rollback has no effect, the balance changed to 20

Expected behavior
Here I expect that when I call the $transactionManager->get(true) I will receive an active DB transaction with autobegin so I can use the transactional mode in the loop. In my code example I want rollback to cancel the last save so the user balance will be 10.

Details

• Phalcon version: 5.3.0

phalcon => enabled
Author => Phalcon Team and contributors
Version => 5.3.0
Build Date => Dec 14 2023 01:13:10
Powered by Zephir => Version 0.17.0-9f99da6
Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.case_insensitive_column_map => Off => Off
phalcon.orm.cast_last_insert_id_to_int => Off => Off
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.column_renaming => On => On
phalcon.orm.disable_assign_setters => On => On
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.enable_literals => On => On
phalcon.orm.events => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.exception_on_failed_metadata_save => On => On
phalcon.orm.ignore_unknown_columns => Off => Off
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.not_null_validations => On => On
phalcon.orm.resultset_prefetch_records => 0 => 0
phalcon.orm.update_snapshot_on_save => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.orm.dynamic_update => On => On
phalcon.warning.enable => On => On

• PHP Version: 8.1.20
• Operating System: Debian GNU/Linux 10
• Installation type: installing via package manager
• Server: Nginx
• Other related info (Database, table schema): MySQL 5.7

[kgrammer]

Stupid questions since I haven't worked with transaction handling in Phalcon in a long time...

Does the find() need to be inside the transaction for the transaction to work on the $user object?

[serge-webspark]

Not necessarily, more important is the part where we execute actual DB queries. The same behavior with plain queries:

        $transactionManager = $this->di->get('transactionManager');

        $transactionManager->get(true);
        $this->db->execute('update users set balance = 10 where id = 1');
        $transactionManager->commit();

        $transactionManager->get(true);
        $this->db->execute('update users set balance = 20 where id = 1');
        $transactionManager->rollback(); // Rollback has no effect, the balance changed to 20

[niden]

I need to write a test for this. I see a potential issue with the commit() but need to verify

[serge-webspark]

It works for the transaction received from the transaction manager

        $user = Users::findFirst(173431);
        $transactionManager = $this->di->get('transactionManager');

        $transaction = $transactionManager->get(true);
        $user->balance = 10;
        $user->save();
        $transaction->commit(); // Successful commit, balance changed to 10

        $transaction = $transactionManager->get(true);
        $user->balance = 20;
        $user->save();
        $transaction->rollback(); // Successful rollback, balance stays the same (10)

but it would be nice if the manager could properly commit and rollback.