Let’s say we have users concurrently performing the following queries
inside a transaction (example taken directly from PostgreSQL site):
UPDATE accounts SET balance = balance + 100.00
WHERE acctnum = 12345;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
Am I right assuming that in case of PostgreSQL the UPDATE query of User
B would be working with an updated version of the accounts row, whereas
in case of MySQL it would update the original one?
Both databases support multiple levels of transaction isolation. Read their docs to see how this affects concurrent transactions.
If I use rails’ optimistic locking feature, I would ensure
database-independent behaviour, but would have to implement the handling
of rolled-back transactions on my own, right?
You’d have to rescue ActiveRecord::StaleObjectError and do something sensible, like alert the user of a conflict or try to resolve it yourself.
Are there any best practices for concurrency control with rails out
Use optimistic locking when the chance of conflict is reasonably low (users hate to refill data and resolve conflicts).
Use pessimistic locking when concurrency is high and you want to read a bunch of records and update them in the same transaction.
The best way is to try to avoid locking altogether.