CODEWITHSUNDEEP
javamysqlmultithreadingjdbc
jim
jim

Reputation: 9148

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

I'm currently planning out a Java server that is running a thread pool that accepts client connections that will require that a database read/update occurs.

What about the case of multiple reads/writes on a single row? How should I manage this? Should I create a queue in Java or can MySQL handle this kind of stuff?

E.g. I plan on using transactions where I will use the following to lock a row:

Select <columns> from <table> where <condition> FOR UPDATE;

Question: What if another thread comes in and wants to update the same row before the first commits? Will the query fail or will MySQL hold it for a while and wait for the first lock to be release?

My current solution: I'm thinking a way to do it could be to create a static queue/pipe of all queries so that only one is fed to the DB at a time. This is obviously a bottleneck and general bad idea?

Also, are there any Java frameworks that handle this kind of thing and MySQL connections in general? Thanks.

EDIT: I am planning on using a threadpool so all queries will be coming from seperate connections.

Upvotes: 2

Views: 3920

Answers (2)

Radu Murzea
Radu Murzea

Reputation: 10920

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.

Upvotes: 5

J&#233; Queue
J&#233; Queue

Reputation: 10637

First off: Concurrent access has been handled for many decades by RDBMSs :) MySQL will properly manage this for you as to not have to have you queue the requests. There are however various concurrency (isolation) levels that you can set via JDBC.

http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#setTransactionIsolation(int)

The big question in record concurrency is not so much the appropriate semantics, but user-level notification if the data is dirty. BUT, if you simply need to ensure proper serial access to the record (row) nearly any DB will handle this beautifully.

Upvotes: 0

Related Questions