Multiple database transactions on a single connection

Question

I have a multi-threaded program, while each thread at the beginning of execution gets one connection from the MySql connection pool class. The thread then uses several database shell classes to execute its business logic, passing the database connection to the shell 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 to their database table independently, using the same database connection. I know that MySql does not allow nested transactions and cannot find a way to do this. Any help is appreciated. Thanks!

Answer 1

It looks like you need to associate transactions with table wrappers better than just downstream. IOW, you need to create a transaction class. A transaction class acquires / releases connections from / to the connection pool when a transaction is opened and closed.

Then the transaction will become a factory for your table wrappers. Any table wrapper created by a transaction is inside the transaction because it does not have access to any connections except the one in which the transaction is included.

, . SmartPtr , , , - .

Answer 2

, , - - , . "" , . , ( ) , , . , , , , , .

Answer 3

, .

mysql.

Answer 4

, ? , , , . , ( , , , , ).

Answer 5

- - Apache/PHP.

If you have far fewer entries than reads (only one of 10 threads is required), you can use one global connection for SELECT and create a new connection for each thread that needs to be updated.

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