MySQL: Transactions across multiple threads

Question

Preliminary:

I have an application which maintains a thread pool of about 100 threads. Each thread can last about 1-30 seconds before a new task replaces it. When a thread ends, that thread will almost always will result in inserting 1-3 records into a table, this table is used by all of the threads. Right now, no transactional support exists, but I am trying to add that now. Also, the table in question is InnoDB. So...

Goal

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

1. Can I implement a transaction across multiple connections?
2. If not, can multiple threads share the same connection? (Note: there are a LOT of inserts going on here, and that is a requirement).

Answer 1

1) No, a transaction is limited to a single DB connection.

2) Yes, a connection (and transaction) can be shared across multiple threads.

Answer 2

Sharing connections between lots of threads is usually implemented by using a connection pool. Every thread can request a connection from the pool, use it for its purposes (one or more transactions, committed or rolled back) and hand it back to the pool once the task is finished.

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

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

As for InnoDB, it will not have any problems handling lots of transactions. Under the supervision of the app server you can concentrate on the business logic and do not have to worry about half-written updates or anyone seeing updates/inserts before the transaction they originate from has been committed.

InnoDB uses MVCC (multi version concurrency control), effectively presenting each transaction with a snapshot of the whole database as of the time when it was started. You can read more about MVCC here in a related question: Question 812512

Answer 3

Well, as stated in a different answer you can't create a transaction across multiple connections. And you can share the single connection across threads. However you need to be very careful with that. You need to make sure that only one thread is writing to the connection at the same time. You can't just have multiple threads talking across the same connection without synchronizing their activities in some way. Bad things will likely happen if you allow two threads to talk at once (memory corruptions in the client library, etc). Using a mutex or critical section to protect the connection conversations is probably the way to go.

-Don