Java MySQL prevent race condition

0
votes

I write a java application that starts asynchronous threads to read and update values from the same database. Each thread gets the connection from a connection pool (c3p0). I have to prevent race conditions because I have to update entries based on their current values. So reading data with a SELECT statement and then update it with an UPDATE statement would cause a race condition and so it would not be thread safe. I already found some solutions how to prevent such race conditions but I still have some questions.

For example I can use kind of that UPDATE ExampleTable SET ExampleValue = ExampleValue + '5' WHERE Id = '10' to increment values thread safe. I read that this is an atomic statement. So my first question is: Is executing a PreparedStatement in java always thread safe? I think so because (if autoCommit is true) every single executed statement is a transaction and transactions are atomic, right? If yes, is that also the case if I call a procedure with a statement or if I put multiple queries in one statement separated through semicolons?

I also read that I can set autoCommit to false and execute multiple statements befor commiting, wich also achieves thread safety because no other statement can interrupt a transaction. Is that right?

Are there any further solutions to prevent such race conditions?

1
javamysqlmultithreadingprepared-statementrace-condition
I think this answers some of your questions, did you check it? Specially: "Give every thread its own connection". I do it this way through a connection pool and I don't have any race conditions (although I use PostgreSQL not MySQL). – m0skit0
I use a Connection Pool and every thread has its own Connection object from the pool. But race conditions are still possible when using separate statements for reading and writing. – stonar96
As EJP answers, use SELECT FOR UPDATE, this way you only have one statement. If you want to execute more queries/updates or SELECT FOR UPDATE is not an option, you need to wrap them into a transaction (of course still one connection per thread). – m0skit0
@m0skit0 Wouldn't I need a second statement to execute the update? For example: SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1;. – stonar96
As for your before last question: the i in ACID should answer that. Quote from the link: "... concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially ...". Just make sure you use the right isolation level. – vanOekel

1 Answers

1
votes

Is executing a PreparedStatement in java always thread safe?

I'm not sure what this means. You would only use a PreparedStatement and indeed its underlying Connection from a single thread, so the issue doesn't arise.

I think so because (if autoCommit is true) every single executed statement is a transaction and transactions are atomic, right? If yes, is that also the case if I call a procedure with a statement or if I put multiple queries in one statement separated through semicolons?

I thought so. What you're really asking is whether PreparedStatement is atomic, whether across threads or processes, and the answer to that is 'no' unless you use auto-commit. If you're using transactions, it is the transaction which is atomic. [In fact it is always the transaction which is atomic, but in auto-commit mode it is co-extensive with the statement.]

I also read that I can set autoCommit to false and execute multiple statements befor commiting, wich also achieves thread safety because no other statement can interrupt a transaction. Is that right?

It achieves atomicity. Thread safety is another thing entirely.

What you may be looking for is SELECT... FOR UPDATE within a non-auto-committed transaction. It locks the returned rows so that they can't be returned by another such statement until this transaction is committed. Or else constructions like INSERT ... ON DUPLICATE KEY [IGNORE|UPDATE].