Bug #9496
closed[API] Ensure 'items_available' and 'items' (in list responses) both reflect the same database state
Description
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."
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. 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.
Related issues