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

Question

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.

Are the threads sharing a single DB connection, or does each thread get its own? If they're sharing, server-side locking won't work as MySQL won't be able to tell that it's two different threads pounding on the same link. — Marc B
– Marc B, Commented Feb 16, 2012 at 20:44
Before answering, a better understanding of what you want to accomplish would be essential: do you want threads hitting the same row to queue in line? Why do you want to lock the row at SELECT time instead of creating a lock when effectively updating? Why did you discard an optimistic locking approach? — Alessandro Santini
– Alessandro Santini, Commented Feb 16, 2012 at 20:46
I'm going to use a connection pool, so seperate connections. — jim
– jim, Commented Feb 16, 2012 at 20:46
@AlessandroSantini I don't know if want threads to queue in line. Not sure if it's needed. Is the SELECT FOR UPDATE method a good approach if I want to get the users account balance before I update? — jim
– jim, Commented Feb 16, 2012 at 20:50
I think this is what you need to figure out first. If you potentially have multiple threads updating the same account at the same time and you want to ensure that all updates are executed on a first-come, first-served basis, a SELECT FOR UPDATE potentially is what you are looking for. If instead multiple threads hitting the same account may raise an error condition (e.g. update coming from a user interface with an outdated account balance displayed) a version column may be more effective. — Alessandro Santini
– Alessandro Santini, Commented Feb 16, 2012 at 20:57

Radu Murzea · Accepted Answer · 2012-06-02 18:04:10Z

5

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.

edited Jun 2, 2012 at 18:04

answered Feb 16, 2012 at 20:43

Radu Murzea

11k10 gold badges50 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jim Over a year ago

What If I lock a row using Select <columns> from <table> where <condition> FOR UPDATE; and another thread tries to write to that row? Will the new write query still be buffered or will it just fail instantly?

Radu Murzea Over a year ago

@conor According to the MySQL Documentation, you cannot do a SELECT FOR UPDATE unless autocommit is disabled (either by setting it to 0 or by starting a transaction). To answer your question: if a UPDATE is issued on a row which is still locked by SELECT FOR UPDATE, then the UPDATE will wait until SELECT FOR UPDATE is completed and will execute after that.

Jé Queue · Accepted Answer · 2012-02-16 20:58:05Z

0

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.

answered Feb 16, 2012 at 20:58

Jé Queue

10.7k14 gold badges59 silver badges62 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related