Open anym0re opened 1 year ago
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?
@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
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.
An error occurs when trying multiple transactions at once.
example code
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