MySQL: multi-thread transactions

Question

Preliminary :

I have an application that supports a thread pool of 100 threads. Each thread can last about 1-30 seconds before a new task replaces it. When a thread ends, this thread will almost always lead to the insertion of 1-3 records into a table, this table is used by all threads. There is no transaction support right now, but I'm trying to add it now. In addition, the table in question is InnoDB. So that...

purpose

I want to implement a transaction for this. The rules for whether or not this transaction commits or rollbacks are in the main thread. Basically, there is a simple function that will return a boolean value.

• Is it possible to implement a transaction through multiple connections?
• If not, can multiple threads share the same connection? (Note: there are many attachments here, and this is a requirement).

Answer 1

Well, as pointed out in another answer, you cannot create a transaction through multiple connections. And you can share one connection over streams. However, you need to be very careful with this. You must ensure that only one thread is connected to the connection at a time. You cannot just use multiple threads in the same connection without synchronizing their actions in any way. Bad things are likely to happen if you allow two threads to speak right away (memory corruption in the client library, etc.). Using a mutex or critical section to protect connection chains is probably the way out.

-Don

Answer 2

1) No, the transaction is limited to one database connection.

2) Yes, the connection (and transaction) can be divided into several threads.

Answer 3

Exchange of connections between multiple streams is typically accomplished using a connection pool. Each thread can request a connection from the pool, use it for its own purposes (one or more transactions, complete or roll back) and return it to the pool after the task is completed.

This is what application servers offer you. They will also take care of transactions. e. when the method that requested the transaction ends normally, changes are made, if it throws an exception, the database transaction is rolled back.

I suggest you take a look at Java EE 5 or 6 - it is very easy to use and can even be used on embedded systems. For an easy start, take a look at Netbeans and the Glassfish application server. However, the general concepts apply to all application servers.

As for InnoDB, it will not have problems handling a large number of transactions. Under the supervision of the application server, you can focus on business logic and not worry about half-written updates, or anyone who sees updates / inserts before the transaction from which they originate has been completed.

InnoDB uses MVCC (multi version concurrency control), effectively presenting each transaction with a snapshot of the entire database since it was launched. You can learn more about MVCC here in the related question: Question 812512