Extend Rails ActiveRecord and ConnectionAdapter to support dirty reads on MySQL

Discussed here is a mixin that extends Rails to provide an easy method to switch your database session between clean and dirty reads, otherwise known as transaction isolation level.

( jump to the code )

At Rescuetime, our most interesting analysis of tracked time requires a lot of complicated data munging on the database side. We extensively optimize the structure of the datastore and the plan of queries to produce quick results. However, there is some, however small, amount of row scanning and index hunting that is inevitable. Some of these tables are also subject to rapid, row overlapping, simultaneous insert loads.

In general, reporting or analytical access has no worries about data being up to date to the nearest microsecond, although as near real time as possible is highly desired. This near real time goal rules out an ETL type solution. Additionally, there is the cost factor. If we can make this work on one database, why build two?

In the quest for minimal stress for the online system, we introduced a method for flagging database work to be dirty reads, thus preventing any kind of locking (especially index locking) on the rows in question, and applied these where possible. This is simple enough in straight SQL, but we wanted to expose it to Rails framework in a consistent manner.

What this code does is:

1) Provide stubs in the abstract database adapter for “dirty()”, “clean()”, “reallyclean()”
2) Implement them in the MySQL adapter
3) Expose them to ActiveRecord::Base as a class method, prefixed with “isolation_”

On #1, #2: the choice of really clean versus clean simply describes the read locking strategy used. EG if you have multiple selects in same transaction on same rows, “clean” returns same result from same snapshot. However “reallyclean” will return newer rows if they exist on the later selects.

On #3, the prefix “isolation_” is added (yielding “isolation_dirty() etc.) since there is already some semantic in place for “dirty” in ActiveRecord.

For MySQL we set:

reallyclean = READ COMMITTED

See their reference.

Just put this code in something like lib/mysql_adapter_extensions.rb in your project, then require that in some controller.

Here is the code:

With blog copy: at node.to.
Just the code: at pastie.org.

Example use:

result = Person.find_by_name params[:name] # some crazier query here