Multiple database transactions on a single connection

Question

I have a multi-threaded program whereas each thread, at the start of execution, acquires a single connection from a MySql connection pool class. The thread then uses a number of database table wrapper classes to do its business logic by passing its database connection to the wrapper classes. The operations on these wrapper classes are not necessarily sequential and I want these wrapper classes to be able to commit or rollback changes on its database table independent of each other using the single database connection. I'm aware that MySql does not allow nested transactions and have not figure out a way to accomplish this. Any help is greatly appreciated. Thanks!

Answer 1

If I understand the problem correctly I believe that your only option is to implement some kind of transaction queue that allows your multiple threads to share the single connection. Each of your threads can then add "tasks" to the queue which require execution within a transaction. You then have a process which picks these tasks off the queue (you could even implement some kind of prioritisation here) and sends them to the database before returning the results to the thread that initiated the request. The calling threads would block until they receive their results, which isn't ideal, but if you really need to have multiple threads and multiple transactions I can't see another way of doing it without having multiple connections.

Answer 2

Well this is a classic example of a single resource usage by multiple threads.

The solution is all about using mutexes on the mysql connection operations.

Answer 3

Sounds like you have to associate the transactions with the table wrappers better than just on a thread by thread basis. IOW, you need to create a transaction class. The transaction class acquires/releases connections from/to the connection pool as the transaction opens and closes.

The transaction then become the factory for your table wrappers. Any table wrapper created by the transaction is inside the transaction since it has no access to any connection but the one the transaction is on.

The annoying part of this is keeping all the objects from accessing dead objects. Some form of SmartPtr will be needed to handle what happens when the transaction commits but something tries to use the table wrapper again.

Answer 4

There's a connection pool, why don't you acquire more connections? And maybe use semaphores to limit the number of simultaneous connections. Else, you need to lock the single connection or use client transactions (use a framework, this is not simple and you'll need to lock the connection anyway if there's only one, but the waiting time will be very small compared to not having transactions on your code side).

Answer 5

The easiest solution is to just create a new connection for each thread - this is how it's typically done with Apache/PHP.

If you have far fewer writes than reads (only 1 in 10 threads needs to write), you could use one global connection for SELECTs and create a new connection for each thread which needs to update.

After that, you could perhaps have a pool of up to 10 threads used for writing (so you can have up to 10 concurrent transactions). At the end of the day, if you want concurrency then you need multiple connections.