What are some methods of dealing with race conditions between stateless servers on a RDBMS?
We want to avoid zookeeper for now so we don’t have too many moving parts. We may add it later. For now, I am wondering how I can deal with race conditions on
- insert
- update
For update, we can use optimistic locking(with a version column in the table). Two inserts just plain fails. In this way, I suspect I can look for two exception types and retry preventing any race condition. Under high load, our retries in some records may go to 3 tries to get the record in (I think).
Anyways, I am wondering how others deal with race conditions among a large cluster of server on an RDBMS?
I remember in noSQL, instead of exceptions it returns the row that was just replaced so you know there was a race so to speak and can deal with that if needed.
If you expect lots of conflicts, use pessimistic locking:
START TRANSACTION; -- will lock the row about to be modified SELECT col FROM tab WHERE id = 42 FOR UPDATE; /* application activity */ UPDATE tab SET col = 'newval' WHERE id = 42; COMMIT; If you expect few conflicts, use optimistic locking, either with application tools or by using the REPEATABLE READ transaction isolation level.
