Active Record while_preventing_writes API versus read-only transactions

I was reading about the while_preventing_writes API that was added to Rails 6 in this PR. I’m trying to understand why it was preferred over read-only database transactions, which are supported by both PostgreSQL and MySQL.

I noticed that the while_preventing_writes functionality added a nontrivial amount of internal complexity (e.g. adapters need to handle it), and it seems to me it would be both simpler and more reliable to support an additional :read_only option on the transaction method to set READ ONLY access mode when transaction is opened. I noticed Matthew also asked about utilizing read-only transactions in the PR.

There are many reasons we implemented while_preventing_writes this way in particular that may not be obvious from the PR.

  1. Opening transactions isn’t free and can result in a lot of overhead on queries.
  2. Readonly transactions don’t play nice with some tools used at scale like ProxySQL.
  3. Both Shopify and GitHub used similar approaches in our applications to blocking writes on reading connections and we felt there was enough prior usage to justify upstreaming it.

Implementing a way to optionally use readonly transactions would be fine, but we don’t want to replace while_preventing_writes with readonly transactions.

4 Likes

Thank you for the reply, Eileen. That makes total sense to me.

I saw that some databases like MySQL optimize read-only transactions to be more lightweight than regular ones, but I guess there will always be overhead.