The rails console is a powerful tool. It provides a repl into your application, allowing you to run and debug code, and interact with your database. The code reloading functionality isn’t as nice as what you’d get with a lisp, but generally you’re a single reload! away from getting whatever changes you’ve made in your editor loaded into the current rails console session, ready to use.

It also has a sandbox mode, which is briefly mentioned in the official rails docs, and is well-described here The Power of Rails Console with the –sandbox Option.

However, it comes with a big gotcha - one that is so problematic that the feature should in my opinion not even exist. (DHH’s philosophy is to “provide sharp knives” even if if the developer may cut themselves – fine but if we want to use that metaphor, this is a sharp knife that will eventually give you tetanus.)

The way it provides safety against accidentally modifying data is by wrapping everything you do in a database transaction, then just rolling that transaction back at the end of your session. This will work, and isn’t an unreasonable thing to do, but depending on what database you’re using, it will increase the likelihood of other queries and database operations failing or deadlocking.

Postgres table locks

With postgres, whenever you read from a table in a transaction, it will automatically take an ACCESS SHARE lock on that table (and its indexes, as far as I can tell), as per the docs on table-level locks. This is the lowest level of lock (as in, the one that conflicts with the fewest other lock modes), so it won’t prevent other transactions in other processes from normal querying, but it will prevent other processes from doing anything that requires an ACCESS EXCLUSIVE lock, for example many table and index operations. These will all fail to execute due to your sandboxed rails console, and will be taken if are you doing pretty much anything at all involving the database in your console session.

Postgres row locks

If, in your sandboxed rails console session, you accidentally or intentionally update some rows, this will (in addition to table level locks) take a row-level lock on the rows you are modifying. The precise lock level will depend on the nature of the field you are modifying (mostly, whether it is indexed or not) but either way, you can still run into a situation where your sandbox session causes other transactions to either hang or deadlock.

Consider the this case:

  • your console session starts, and you modify user 1’s name to “foo”, acquiring lock 1a on this row

  • an application session starts a transaction, and modifies user 2’s name to “bar”, acquiring lock 2a on this row

  • your console session then modifies user 2’s name to “foo2”, attempting to acquire lock 2b on the same row; this has to wait on lock 2a to be released

  • the same application transaction attempts to modify user 1’s name to “bar2”, attempting to acquire lock 1b on that row

  • we now have a deadlock, as there is a cycle in the lock dependency graph. postgres will detect this and abort one of the transactions, with no guarantee as to which one will be selected for termination

Other reasons

Even if you aren’t concerned about interfering with DDL statements modifying the database schema, or you “know” you won’t be modifying data, so don’t have to worry about deadlocks, long running transactions are problematic for various reasons, including preventing vacuum and potentially introducing replication lag.

What about mysql and other databases?

My familiarity is primarily with postgres, and the mysql docs are not as clear on this point, but Transaction Isolation Levels does state that at the READ COMMITTED (and above, presumably) isolation level, innodb will hold “locks only for rows that it updates or deletes”, so I suspect rails console sandbox on mysql will have most of the same problems as postgres.

What to do instead?

Instead of this, you can create a read only postgres (or mysql) user, and have the rails console session use that instead. This could be configured by an environment variable, or an interactive startup script you run before enabling a console session.

This way, you get the safety of preventing any accidental data modification, while avoiding the most serious pitfalls: causing your application queries to deadlock or hang. However, your console queries will still take table level locks which will block table modification and related operations.

You can reduce that risk by keeping your rails console sessions short, but the best solution is just to not use the rails console in production at all.

  • Ensure you have a staging (or similar) environment that includes a copy (or subset) of your production data, scrubbed of any sensitive info like credentials, access tokens, and PII.

  • And/or, set up replication to another database and do all your queries on it, or even extract your data to a warehouse for analytical querying.