We are working on a restful application that has a 'whiteboard' like feature. I had asked a related question on it at one point: Is there a concurrency problem here? How to test it during development?
We first chose to implement the core functionality without concurrency in place just 'cause the application was largely unfamiliar and we had to iron out implementation risks. Since I am well aware of concurrency concepts/issues I felt that it could be possible to delay the concurrency/conflict management code till the best possible time. It seems it was until now :)
On the whiteboard multiple team members are able to create/read/update/delete records. The conflicts that can primarily arise are due to update and delete (a bit stale read is considered okay, we fetch periodic updates)
Scenario: Every record that is returned has an id as well as the last timestamp as set by MySQL's NOW() when the record was first created.
Conflict Scenario:
- Just before updating check if timestamp is valid (or if the row even exists) Basically a
select * from ... where... query followed by a timestamp check and update if same else 'merge' (i.e., just append to current data with -------CONFLICT/MERGED------- as delimiter)
This seems to be the only conflict scenario so to speak (can anyone spot anything else that I may be missing?)
So the question is how to actually implement this concurrency/conflict check? The scenario needs to be atomic i.e., check if value exists/or is same and then update. Is creating a transaction the way to go? I'm not sure if just going via Spring transaction and firing 2 queries (one for select and the other for update) works or is there an SQL syntax that could help me with this atomicity requirement.
My question is "how to do it"? I've read about SELECT...FOR...UPDATE but don't understand how does it work nor do I understand how SELECT...LOCK IN SHARE MODE could/would solve the problem.
Any ideas/pointers would be really helpful :) Using Restlet/Spring-Jdbc/MySql