I wrote 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 before committing, which 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?
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].