2024-09 Automatically close database connections when carried across fork() ↑

Status ↑

Accepted, but we can revisit more complex solutions if we learn something that indicates that effort is worth it.

Context ↑

In August 2024, Andy Croll opened an issue1 describing sqlite file corruption related to solid queue. After investigation, we were able to reproduce corruption under certain circumstances when forking a process with open sqlite databases.2

SQLite is known to not be fork-safe3, so this was not entirely surprising though it was the first time your author had personally seen corruption in the wild. The corruption became much more likely after the sqlite3-ruby gem improved its memory management with respect to open statements4 in v2.0.0.

Advice from upstream contributors5 is, essentially: don’t fork if you have open database connections. Or, if you have forked, don’t call sqlite3_close on those connections and thereby leak some amount of memory in the child process. Neither of these options are ideal, see below.

Decisions ↑

  1. Open writable database connections carried across a fork() will automatically be closed in the child process to mitigate the risk of corrupting the database file.

  2. These connections will be incompletely closed (“discarded”) which will result in a one-time memory leak in the child process.

First, the gem will register an “after fork” handler via Process._fork that will close any open writable database connections in the child process. This is a best-effort attempt to avoid corruption, but it is not guaranteed to prevent corruption in all cases. Any connections closed by this handler will also emit a warning to let users know what’s happening.

Second, the sqlite3-ruby gem will store the ID of the process that opened each database connection. If, when a writable database is closed (either explicitly with Database#close or implicitly via GC or after-fork callback) the current process ID is different from the original process, then we “discard” the connection.

“Discard” here means:

Note that readonly databases are being treated as “fork safe” and are not affected by these changes.

Consequences ↑

The positive consequence is that we remove a potential cause of database corruption for applications that fork with active sqlite database connections.

The negative consequence is that, for each discarded connection, some memory will be permanently lost (leaked) in the child process. We consider this to be an acceptable tradeoff given the risk of data loss.

Alternatives considered. ↑

1. Require applications to close database connections before forking. ↑

This is the advice7 given by the upstream maintainers of sqlite, and so was the first thing we tried to implement in Rails in rails/rails#52931}[https://github.com/rails/rails/pull/52931]{*8. That first simple implementation was not thread safe, however, and in order to make it thread-safe it would be necessary to pause all sqlite database activity, close the open connections, and then fork. At least one Rails core team member was not happy that this would interfere with database connections in the parent, and the complexity of a thread-safe solution seemed high, so this work was paused.

2. Memory arena ↑

Sqlite offers a configuration option to specify custom memory functions for malloc et al. It seems possible that the sqlite3-ruby gem could implement a custom arena that would be used by sqlite so that in a new process, after forking, all the memory underlying the sqlite Ruby objects could be discarded in a single operation.

I think this approach is promising, but complex and risky. Sqlite is a complex library and uses shared memory in addition to the traditional heap. Would throwing away the heap memory (the arena) result in a segfault or other undefined behaviors or corruption? Determining the answer to that question feels expensive in and of itself, and any solution along these lines would not be supported by the sqlite authors. We can explore this space if the memory leak from discarded connections turns out to be a large source of pain.

References ↑

Footnotes ↑

1 [SQLite queue database corruption · Issue #324 · rails/solid_queue](github.com/rails/solid_queue/issues/324)

2 [flavorjones/2024-09-13-sqlite-corruption: Temporary repo, reproduction of sqlite database corruption.](github.com/flavorjones/2024-09-13-sqlite-corruption)

6 3 [How To Corrupt An SQLite Database File: §2.6 Carrying an open database connection across a fork()](www.sqlite.org/howtocorrupt.html#carrying_an_open_database_connection_across_a_fork)

4 [Always call sqlite3_finalize in deallocate func by haileys · Pull Request #392 · sparklemotion/sqlite3-ruby](github.com/sparklemotion/sqlite3-ruby/pull/392)

7 5 [SQLite Forum: Correct way of carrying connections over forked processes](sqlite.org/forum/forumpost/1fa07728204567a0a136f442cb1c59e3117da96898b7fa3290b0063ae7f6f012)

6 3 [How To Corrupt An SQLite Database File: §2.6 Carrying an open database connection across a fork()](www.sqlite.org/howtocorrupt.html#carrying_an_open_database_connection_across_a_fork)

7 5 [SQLite Forum: Correct way of carrying connections over forked processes](sqlite.org/forum/forumpost/1fa07728204567a0a136f442cb1c59e3117da96898b7fa3290b0063ae7f6f012)

8 [SQLite3Adapter: Ensure fork-safety by flavorjones · Pull Request #52931 · rails/rails](github.com/rails/rails/pull/52931#issuecomment-2351365601)