What about transactions?

[crocodile2u]

Atomicity of operations is a vital part of any application. How do you handle transactions? I reckon, one can just make it this (simplified):

$conn->query("BEGIN")
  ->then(function() use ($conn) {
    $conn->query("INSERT/UPDATE ...")
      ->then(function() use ($conn) {
        $conn->query("COMMIT");
      });
    });

Would be nice to have this covered in docs.

[clue]

@crocodile2u Looks about right 👍 Do you feel there's something that needs to be done here? PRs would be much appreciated

[crocodile2u]

Yeah, I think a transaction() method would be great. It can accept a callback which is then executed within a DB Transaction and return (surprise!) a Promise

[jsor]

As this library i quite low-level, i think such a method would be better suited in a package providing a higher-level abstraction on top of this library, think of something like Doctrine DBAL which provides an abstraction on top of PDO.

Other than that, documentation improvements are very welcome 👍

[langabi]

Just on transactions: I think I'm right that the code above has a (possibly) unexpected behaviour. Specifically, while the database is handling BEGIN, other queries may be sent to the database from elsewhere in the PHP script, and then surprise: these will be executed within the scope of this transaction. That is, the entire PHP script's queries will be in the transaction, and not only the queries in the specific chain of ->then() calls above.

I think the only way to really isolate a specific thread of ->then() calls in a transaction that is not shared by the rest of the script, is to open an entirely separate connection instead of running BEGIN.

Let me know if I'm somehow wrong on this; otherwise, I thought worth mentioning for future readers!

[WyriHaximus]

When we do transactions it should be very clear that we start a transactions, preferably from this package's API directly

[crocodile2u]

Specifically, while the database is handling BEGIN, other queries may be sent to the database from elsewhere in the PHP script, and then surprise: these will be executed within the scope of this transaction

That's a nasty behavior. That place elsewhere in the PHP script might send it's own BEGIN. MySQL doesn't handle nested transactions, therefore it's going to result in an error.

[pavarnos]

Could this be something added to the LazyConnectionHandler? transaction(callable) could create a new connection internally to run the query inside the callable, then close the connection after?

[gizahNL]

Specifically, while the database is handling BEGIN, other queries may be sent to the database from elsewhere in the PHP script, and then surprise: these will be executed within the scope of this transaction

That's a nasty behavior. That place elsewhere in the PHP script might send it's own BEGIN. MySQL doesn't handle nested transactions, therefore it's going to result in an error.

Therefore any transaction should probably occur in its own connection.
It could be coded in a way that transaction start opens a new connection and returns a handle to it, and the final commit then closes the handle again.
Could also be handled internally by returning a transaction ID on which work is to be done and providing a transaction id variable in the query method(s)

[nstwfdev]

Hello there, 2.5 years later since last message

I created a simple wrapper around ConnectionInterface class, whats about that idea?

https://github.com/nstwfdev/mysql-connection