ActiveRecord and consistent timestamps in transactions

rubyonrails-talk
1

Hello, All:

I am grappling with an interesting problem that I can’t believe I’m the first to tackle. Hoping someone has covered this ground before, or is experienced enough to give good suggestions, and can suggest a reasonable path forward.

The issue is related to transactions and ActiveRecord::Timestamp.

Specifically, if we insert or update multiple rows in a single transaction, each row gets a slightly different updated_at/created_at timestamp.

The reason is because instead of using the SQL function NOW() to set the created/updated times, the ruby code generates the timestamps at the time the SQL for the insert/update is created.

The problem is that we need the timestamps on rows added/updated in the same transaction to match (for auditing and perceived atomicity reasons).

As I allude to above, if ActiveRecord used the sql function NOW() to set the created_on/updated_on times, they would all be the time that the database server began the transaction. (See now() explanation at e.g. PostgreSQL: Documentation: 8.2: Date/Time Functions and Operators )

But I understand that setting updated_on/created_on times to NOW() is tricky through ActiveRecord.

Here are some ideas we’ve thrown around to fix this, and our thoughts on each:

Idea 1: Fetch NOW() from the database at the beginning of the transaction, and use that verbatim for all updated_on/created_on times that need to get set

Thoughts: inconsistent with the Rails model, which always uses the time on the ruby side, not the database. Also adds a roundtrip to the database.

Idea 2: Mixin new modules to ActiveRecord::Base to override the transaction() call, record the time, and then call super(). Also override the Timestamp current_time_from_proper_timezone method to return the time that our transaction() call squirrelled away. Theres a little more to this (clearing the timestamp when the transaction is committed or rolled back), but you get the idea

Thoughts: Modifies ActiveRecord::Base to be more different than might be obvious. We think we prefer the next idea (3).

Idea 3: Subclass ActiveRecord::Base (to say ActiveRecordLocal) and mix in the modules mentioned above into this class.

Thoughts: This makes is clearer that when you’re using ActiveRecordLocal, you’re not getting the stock ActiveRecord::Base behavior. Otherwise this solution is identical to Idea 2.

Idea 4: when then transaction starts, use Timecop::freeze to freeze the time that ruby sees.

Thoughts: We can’t freeze the time for all code in the app while a transaction is pending. Plus Timecop is for testing, not production use-- but most importantly see previous point.

We’ve seen other ideas which we rejected and won’t bring up here.

So, after all this motivation – the questions;

Q1: Has anyone tacked this issue before? How did you solve it? Is there an easy way to accomplish our goal?

Q2: What strategy do you think we should take to fix this issue? (Either one explained above, or another strategy)?

At this point we’re moving forward with Idea 3, but I’d love to hear some experienced people’s input on this topic

Best,

“RubyRailHead”

2

Of the ideas you outline, I would either do this or:

  • use SomeModel.record_timestamps = false to stop active record setting timestamps

  • use before_save / before_create callbacks (or override save) to set your own timestamps

  • continue overriding transaction in order to get your start of transaction timestamp.

The reason for this slightly different strategy is that it means you’re only overriding public methods, so their api will remain stable (or at least go through the usual deprecation cycle) across versions of active record.

Fred

3

Hello, Frederick:

Thanks for your response, that was very helpful both in validating my approach, and suggesting other ways to accomplish this.

I’m still surprised this issue hasn’t come up for others … does anyone else have input on how this should/shouldn’t be performed, or how they solved this issue?

(For those just tuning in, I need all the updated_on/created_on timestamps, in a series of AR object created/updated within a transaction, to match exactly)

Thanks again,

Ruby

4

Hello,

If I am understanding correctly, you need some mechanism to correlate several models together, you are hoping to use the created_at and modified_at timestamps provided by active record, to match the models using a timestamp.

I see the created_at and modified_at timestamp’s as database level information - I try not to rely on these for my business logic as they represent when the database record was created and modified, this is a separate concern to the actual business transaction.

Anything in my business domain that I want to record a time against I try model that explicitly. In this case I would consider introducing a transaction_completed_at column (or a UUID). Or take it a step further and model a transaction separately and add a foreign key in each of the other models.

The way I see it, transaction_completed_at will tell me when the transaction completed and created_at and modified_at will tell me when it was persisted and modified in the database.

Thanks,

Henry

5

I think this is a very important topic, but you posted it in the ruby-forum. You should repost it over in the rails forum or on the rails google group.

As for my opinion on the matter, I think AR should not be setting the time itself and it should be using the database NOW function.

6

Hi guys,

same problem here.

As for my opinion on the matter, I think AR should not be setting the time itself and it should be using the database NOW function.

Totally agree.

I feel like the Rails' behavior is very counterintuitive here. Have you taken the discussion else where?

I would love to see a discussion on this on the rails repo (GitHub - rails/rails: Ruby on Rails).

Maybe this is something that could be tackled with Rails 5 or 6.