How to Access MySQL from Multiple Threads Concurrently

How to access MySQL from multiple threads concurrently

As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some caveats that I've come across:

• Edit: it seems this bullet only applies to versions < 5.5; see this page for your appropriate version: Like you say you're already doing, link against libmysqlclient_r.
• Call mysql_library_init() (once, from main()). Read the docs about use in multithreaded environments to see why it's necessary.
• Make a new MYSQL structure using mysql_init() in each thread. This has the side effect of calling mysql_thread_init() for you. mysql_real_connect() as usual inside each thread, with its thread-specific MYSQL struct.
• If you're creating/destroying lots of threads, you'll want to use mysql_thread_end() at the end of each thread (and mysql_library_end() at the end of main()). It's good practice anyway.

Basically, don't share MYSQL structs or anything created specific to that struct (i.e. MYSQL_STMTs) and it'll work as you expect.

This seems like less work than making a connection pool to me.

Multiple Java Threads access the same DB record when run concurrently

First, add a nullable datetime column that signifies that the row has been "picked up" at a certain time.

Then in your processing thread:

1. Start a transaction
2. Find the oldest row with a "picked up" time of null
3. Update the picked up time to the current system time
4. Commit the transaction.

Make sure your isolation level is set to at least READ UNCOMMITTED, and no two threads should get the same row. Also, if a processing thread dies and abandons it's row, you can find that out by periodically querying for rows with a "picked up" time of earlier than some value, and reprocess those by setting the picked up time to null.

Or just switch to a transactional message queue, which does most of this for you automatically.

Multithreading in MySQL?

Several background threads run in a MySQL server. Also, each database connection is served by a single thread. Parallel queries (selects using multiple threads) are not implemented in MySQL.

MySQL as is can support "a lot of concurrent users". For example Facebook started successfully with MySQL.

MySQL: Concurrent updates (through threads) on a simple table

The locking is implicit, yes, but it's not being done by executeUpdate(). The storage engine in MySQL handles the locking and the unlocking.

Any time you write to a MyISAM table, your query waits the write lock on the table to be available, the write lock is acquired, the write is done, and the write lock is released. There is no genuine write concurrency in MyISAM because each worker is in fact waiting in line for the write lock. You don't get an error because the write requests are serialized.

The situation with InnoDB is similar but very different, in that InnoDB only locks a portion of the table, typically at the row level, where InnoDB can lock a range within an index, thereby locking the rows at that range in the index (and the gap that precedes them). This locking is more granular than table locking, allowing improved concurrency behavior, but there is no concurrent operation on the same row -- each worker waits for the lock or locks that it needs.

In both cases, the locks are implicitly taken.

can Multiple threads share the same database connection to execute multiple prepareStatement in simultaneously?

In theory, JDBC connections are required to be thread-safe, but in practice it is problematic for two reasons:

1. Not all drivers are actually thread-safe, and even drivers that claim to be thread-safe are likely not robustly tested for this thread-safety, as using a connection concurrently from multiple threads is not recommend and uncommon.

2. Using a single connection concurrently from multiple threads comes with all kinds of additional coordination problems: one thread committing or rolling back, or switching from auto-commit false to auto-commit true, will break things for all other threads on the same connection: work gets lost, or active result sets are suddenly closed (e.g. due to commit, or the JDBC requirement that execution of a statement in auto-commit will close any previous result sets on the same connection).

   Also, if a connection breaks (or is closed by one of the threads), this will impact all your other threads as well.

Additionally, if a driver is thread-safe, this thread-safety is usually achieved with a lot of synchronized blocks or other forms of mutual exclusion. This can seriously impair performance over using separate connections, because the other threads will have to wait on each other.

Using a connection per thread is a lot simpler, as it isolates the work from one thread from all others. In addition, it will usually perform better because of the absence of contended access to a connection. If your concern is about the setup cost of having multiple connection, then you should do two things:

1. Use a connection pool, which allows you to easily reuse connections.
2. Use a thread pool with a limited size to execute the actual work.

Doing this has a lot of benefits: you can submit individual units of work to the thread pool; the unit of work obtains a connection from the pool, and returns it when it is done (by closing the connection). Using a connection pool in this way also allows you to easily replace broken connections without having to handle this in your application code itself (other than maybe a retry for the task that broke a connection), and finally using both a connection pool and thread pool allows you to experiment with sizing both to see what is a good size for giving your application the best throughput.

MySQL: Transactions across multiple threads

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

Java server. Multithread access to MySQL. How to manage multiple queries?

You don't have to worry about multiple threads reading or writing on the same row. MySQL will take care of that for you. Depending on the table engine, it will do it in a different manner, but it will always do it. If you have control over the tables, I recommend using the InnoDB engine, since it uses row-level locking. This way, it will handle much higher traffic. The (usual) alternative, however, (MyISAM) does not support row-level locking, it uses table locking.

Hope this helps.

PS:

What if another thread comes in and wants to update the same row
before the first commits ?

MySQL keeps all UPDATEs and INSERTs in a buffer pool. When the pool gets big enough (or after a specific timeout occurs), the buffer is flushed to the tables. If a SELECT is executed before that flush, then MySQL will read information from both the buffer and the tables, in order to provide accurate information. Exception to this: uncommitted transactions.

---

Related Topics