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!
我有一个多线程程序,而每个线程在执行开始时从MySql连接池类获取单个连接。然后,该线程使用许多数据库表包装类通过将其数据库连接传递给包装类来执行其业务逻辑。这些包装类上的操作不一定是顺序的,我希望这些包装类能够使用单个数据库连接相互独立地提交或回滚其数据库表上的更改。我知道MySql不允许嵌套事务,也没有找到实现这一目标的方法。任何帮助是极大的赞赏。谢谢!
5 个解决方案
#1
2
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.
听起来你必须将事务与表包装器关联起来,而不仅仅是逐个线程。 IOW,您需要创建一个事务类。事务打开和关闭时,事务类从/向连接池获取/释放连接。
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.
令人讨厌的部分是保持所有对象不会访问死对象。将需要某种形式的SmartPtr来处理事务提交时发生的事情,但有些事情试图再次使用表包装器。
#2
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.
如果我正确理解了问题,我相信您唯一的选择是实现某种允许多个线程共享单个连接的事务队列。然后,每个线程都可以将“任务”添加到队列中,这需要在事务中执行。然后,您有一个从队列中选择这些任务的进程(您甚至可以在此处实现某种优先级)并将它们发送到数据库,然后将结果返回给发起请求的线程。调用线程将阻塞,直到它们收到结果,这是不理想的,但如果你真的需要有多个线程和多个事务,我看不到另一种方法,没有多个连接。
#3
1
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.
解决方案就是在mysql连接操作中使用互斥锁。
#4
1
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).
有一个连接池,你为什么不获得更多的连接?并且可能使用信号量来限制同时连接的数量。否则,您需要锁定单个连接或使用客户端事务(使用框架,这不简单,如果只有一个,您将需要锁定连接,但与没有事务相比,等待时间将非常短在你的代码方面)。
#5
1
The easiest solution is to just create a new connection for each thread - this is how it's typically done with Apache/PHP.
最简单的解决方案是为每个线程创建一个新连接 - 这通常是使用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.
如果写入的数量远远少于读取(10个线程中只有1个需要写入),则可以为SELECT使用一个全局连接,并为需要更新的每个线程创建新连接。
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.
在那之后,您可能拥有一个最多10个用于写入的线程池(因此您最多可以有10个并发事务)。在一天结束时,如果您想要并发,那么您需要多个连接。
#1
2
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.
听起来你必须将事务与表包装器关联起来,而不仅仅是逐个线程。 IOW,您需要创建一个事务类。事务打开和关闭时,事务类从/向连接池获取/释放连接。
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.
令人讨厌的部分是保持所有对象不会访问死对象。将需要某种形式的SmartPtr来处理事务提交时发生的事情,但有些事情试图再次使用表包装器。
#2
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.
如果我正确理解了问题,我相信您唯一的选择是实现某种允许多个线程共享单个连接的事务队列。然后,每个线程都可以将“任务”添加到队列中,这需要在事务中执行。然后,您有一个从队列中选择这些任务的进程(您甚至可以在此处实现某种优先级)并将它们发送到数据库,然后将结果返回给发起请求的线程。调用线程将阻塞,直到它们收到结果,这是不理想的,但如果你真的需要有多个线程和多个事务,我看不到另一种方法,没有多个连接。
#3
1
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.
解决方案就是在mysql连接操作中使用互斥锁。
#4
1
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).
有一个连接池,你为什么不获得更多的连接?并且可能使用信号量来限制同时连接的数量。否则,您需要锁定单个连接或使用客户端事务(使用框架,这不简单,如果只有一个,您将需要锁定连接,但与没有事务相比,等待时间将非常短在你的代码方面)。
#5
1
The easiest solution is to just create a new connection for each thread - this is how it's typically done with Apache/PHP.
最简单的解决方案是为每个线程创建一个新连接 - 这通常是使用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.
如果写入的数量远远少于读取(10个线程中只有1个需要写入),则可以为SELECT使用一个全局连接,并为需要更新的每个线程创建新连接。
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.
在那之后,您可能拥有一个最多10个用于写入的线程池(因此您最多可以有10个并发事务)。在一天结束时,如果您想要并发,那么您需要多个连接。