If I understand it correctly, Rails already enables WAL mode by default via #49349

https://github.com/rails/rails/blame/5a47bb840eb7652de26d5ecf651a44c3d0ed349c/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L762

Then setting EXCLUSIVE and IMMEDIATE are identical. I think it should be commented/documented.

https://www.sqlite.org/lang_transaction.html

EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway.

Thanks @yahonda! I've updated that now.

I figured that anyone changing the transaction mode should be aware of how they work, but you are right that it's a good idea to point out how the Rails defaults effect them.

FYI there is a open pull request #49691 to make journal_mode configurable.

Some additional context on transaction mode, why it's important we're able to change this.

There is a documented deadlock condition that will lead to SQLITE_BUSY errors which bypass the busy_handler

The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY to the application instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed.

The two solutions are to not select from the database during transaction (difficult to adhere to) or to use an exclusive (or immediate) lock to throw early SQL_BUSY errors which the BUSY_HANDLER can take care of. There is a big stack overflow discussion on this issue related to rails that started 15! years ago.

We've seen these intermittent busy exceptions errors under load which were a result of having DEFERRED as the transaction mode. i.e. Don't use deferred as a transaction mode when you have more than one connection to your database.

I'd suggest we consider changing the Rails default to use EXCLUSIVE to avoid the issues that anyone using sqlite beyond a dev/test database will inevitably run into.

For this PR, I think we should first determine where we want SQLite specific configuration to be specified in the database config yaml. There is some conversation in #49691 on whether they should live in a variables sub key (I would prefer not to). Depending on that decision, there may be a change needed for this PR.

I'm so surprised because I have submitted a very similar pull request to sqlite3-ruby as PR426 coincidentally. With my pull request, we can specify the default transaction mode in config/database.yml.
Therefore, I think that my pull request can cover this pull request.

Regards,
Murase

Excellent @masamitsu-murase I agree, with the change upstream this PR is no longer necessary. ty

Hi @masamitsu-murase - that's a much better solution, thank you! I'll close this now since we don't need it any more 👍

For reference, let me share the configuration to set the default transaction mode for SQLite3 in config/database.yml.
Please note that sqlite3 v1.6.9 or later is required.

# config/database.yml
default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  default_transaction_mode: immediate