Transaction isolation levels

[hoylen]

There is currently no good way to set the transaction isolation level for sqljocky5 transactions.

It would be useful if the ConnectionPool.startTransaction method allowed the caller to optionally specify the isolation level for the transaction that is created.

The isolation level for a transaction cannot be changed once that transaction has started, so it must be set before the start transaction command is executed. There are three possible categories for setting it: global, session and for the very next transaction that will be started in the session.

This feature needs to be implemented in sqljocky5. Firstly, because it may be inappropriate to expect the Dart developer to change the global transaction isolation level for the database. That will change it for all other applications using the database - not just the Dart application - and it might not be allowed by the database owner/administrator. Even if the Dart program is allowed to execute "set global transaction isolation level..." that affects future sessions and not the currently open sessions (i.e. the application would have to "connect; set the global; disconnect; reconnect" to actually use the global value it sets). It is also unreliable, because other processes could be trying to set it to a different value.

Secondly, with the current sqljocky5 API, it is not possible for the application program to reliably change it at the session level. This has to be done by sqljocky5, since only it can reliably execute "set session transaction isolation level ..." on all the connections it has opened. The connection pooling will get in the way. A work around is to get every connection from the pool as retained connections, but that is messy and impossible if some of the connections are already in use. Also, sessions/connections can be silently re-established by the connection pool, so the level set on a connection could be lost.

Thirdly, with the current sqljocky5 API, it is not possible for the application program to use the next category. It cannot execute "set transaction isolation level..." because it does not know which connection from the pool that startTransaction will use.

Actually, the preferred change is to be able to set it on the ConnectionPool, since I can't imagine too many application needing a mixture of different transaction isolation levels for the same database. It would be simpler for most developers to set the transaction isolation level when they create the connection pool. Maybe add the option to override the level set on the ConnectionPool, for individual startTransaction invocations, for those rare applications that really need different per-transaction isolation levels.

[tejainece]

Since ConnectionPool is moved to another package. How do you think this affects this issue?

Do you have any suggestions on how this can be implemented?

[hoylen]

Yes, that makes it difficult, since these transaction levels are very specific to MySQL/MariaDB.

Originally, I was thinking there just needs to be some sort of extra parameters when creating pools/connections/transactions. Previously, sqljocky5 opened new connections behind the scenes, so it just needed to keep and use those parameters when doing so - the issue was the programmer didn't have enough control, so could not add this functionality on top of the old API.

Right now, I'm thinking it would be much better for the connection pools to be a part of the sqljocky5 package (the way it was before). Programmers, who simply want to use their database, don't want to go hunting around for different packages, understanding how they worked (especially with limited documentation), and then writing extra code to make them work with each other: they would rather have a "complete" working library for using MySQL/MariaDB.

[tejainece]

I understand. But I would like to keep the driver small and maintainable. I will try to improve the documentation for both the packages. We could implement ConnectionManager for mysql however in this package.

[hoylen]

That would help a lot. Otherwise, every programmer who needs connection pools would have to rewrite the same code over and over again (and some getting it wrong).