Arvados

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.