Caching of database values

Rails
1

If do something like this:
dbObj = MyTable.find_by_id(5)
a = dbObj.name
b = dbObj.name

How many times will an SQL call be made to get the value of
MyTable.name for id 5? In other words, are the values for dbObj
cached in the object? Is there a way of controlling that? (I can
imagine that sometimes you might not want to cache them if you wanted
to be sure of having the latest value.)

Thanks,
–Paul

2

Only one db query is performed. The result is stored in your dbObj
variable. If you want to “refresh” your in memory copy call reload:

dbObj.reload

A good way to monitor what rails is doing on the back end is to run a
tail -f on the log. You’ll see every sql query and result.

Aaron

3

Hello Paul,

You are indeed correct; all results from the find are cached in
memory as attributes of the MyTable object.

I believe ActiveRecord gives you an option to achieve what you want
through the use of transactions and locking. You can do the following:

MyTable.transaction do
dbObj = MyTable.find_by_id(5, :lock => true)
a = dbObj.name
b = dbObj.name
end

The above will ensure that you get an exclusive lock to the table/row
you want to work with, inside the transaction block. No other process
will get access to that row until your transaction is finished.

If you don’t want to lock the row (or table depending on the DB you
use) then you can use optimistic locking. Optimistic locking makes
sure that you can not save stale data to the DB. It doesn’t help with
reading of stale data though.

Put the following ‘special’ column in your migrations for MyTable:

t.column :lock_version, :integer, :default => 0

The version number is automagically incremented on every save by
Rails. If your lock_version is out of sync with that of the DB, i.e.
someone else has updated your record while you had it in memory, then
you get an exception.

As a final solution you could write attribute accessors for your
model that force the object to reload itself every time you access
the data. It is not a 100% guarantee of freshness (and it will floor
your DB), but it works. For example:

class MyTable < ActiveRecord::Base
def name
self.reload
read_attribute(:name)
end
end

Hey, you can even make it into a plugin so you can do:

class MyTable < ActiveRecord::Base
acts_as_nearly_fresh :name, :title, :address
end

(I’ll leave the plugin implementation to you; I find naming act_as_*
plugins more fun than writing them)

Hope this helps,

-christos