problem with transactions

[anym0re]

An error occurs when trying multiple transactions at once.

"message": "There is already an active transaction",
"exception": "PDOException",

example code

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::rollBack();

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::rollBack();

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::commit();

The code below works normally on mysql, but an error occurs on singlestore.

Is it a natural result due to the difference between single store and mysql?

Or is it an error that needs to be corrected?

environment

php 8.1.13
laravel 9.45
singlestoredb-laravel 1.4.1
singlestore db version 8.0.4

[carlsverre]

SingleStoreDB supports the following flow fine:

MemSQL [test]> start transaction;
Query OK, 0 rows affected (0.001 sec)

MemSQL [test]> insert into foo values (2);
Query OK, 1 row affected (0.039 sec)

MemSQL [test]> rollback;
Query OK, 0 rows affected (0.001 sec)

MemSQL [test]> start transaction;
Query OK, 0 rows affected (0.001 sec)

MemSQL [test]> insert into foo values (2);
Query OK, 1 row affected (0.002 sec)

MemSQL [test]> rollback;
Query OK, 0 rows affected (0.000 sec)

MemSQL [test]> insert into foo values (2);
Query OK, 1 row affected (0.020 sec)

MemSQL [test]> commit;
Query OK, 0 rows affected (0.001 sec)

So I don't think that's the issue. If I had to guess it may be due to how PHP does connection pooling. Can you disable PDO and see if the issue reproduces?

[anym0re]

@carlsverre

There is no problem if I run it on the console as you told me.

and it's the result of testing with the same source.

laravel + mysql driver + mysql = work

laravel + mysql driver + singlestore = didn't work

laravel + singlestore driver + singlestore = didn't work

[carlsverre]

After investigation, it appears that SingleStore does not clear client transaction state the same way that MySQL does after a rollback. This unfortunately means that based on how PDO/MySQL is written, you will need to force the server status to be re-read on the client after issuing a ROLLBACK. This is not needed after COMMIT as SingleStore sets the server status correctly in that case.

The fastest way I currently know how to re-read the server status is the following:

        DB::beginTransaction();
        DB::rollBack();
        DB::insert('select 1');
        DB::beginTransaction();
        DB::rollBack();

I will leave this issue open until SingleStore fixes the bug and it's shipped.