Bug #9496
Updated by Brett Smith almost 8 years ago
h2. Background
Via active record, we do a "select count(id)" to get items_available, and a "select * ... limit ..." to get items. With the default transaction isolation level, these queries can reflect different database states: for example, we might return a response with items=[] (empty) and items_available=1. This is difficult (or worse) for clients to handle correctly: see #9435.
Proposed fix:
See https://www.postgresql.org/docs/9.1/static/transaction-iso.html → 13.2.2. Repeatable Read Isolation Level
Using "repeatable read" for _every_ API transaction sounds like it would be nice and safe, but the locking is optimistic, so we would need auto-retry and associated safeguards. OTOH, using it for "index" should be relatively unobtrusive.
* "Applications using this level must be prepared to retry transactions due to serialization failures."
* "Note that only updating transactions might need to be retried; read-only transactions will never have serialization conflicts."
h2. Implementation
Update ApplicationController#object_list so everything in its current body runs inside a single transaction, where @SET TRANSACTION ISOLATION LEVEL REPEATABLE READ@ is set. "This StackOverflow suggests a few possible ways to do that":http://stackoverflow.com/questions/7915209/rails-postgresql-how-to-set-transaction-isolation-level-to-serializable. @model_class.transaction(isolation: :repeatable_read) do …@ would be nice, but is only available in Rails 4, so that's out unless we upgrade first.
@object_list@ is the method that generates the entire hash that represents the return value of a list API call, so this is the correct place to make the change. ApplicationController is the only place where this method is defined; no subclass overrides it.