CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordruby-on-rails-5
Tallboy
Tallboy

Reputation: 13407

Rails transaction isn't acting atomic?

I have an accounting system I wrote which follows standard dual-entry accounting practices.

There is a feature of dual entry accounting called 'trial balance' where you can verify the entire system is correct because when you run it, it will always equal 0.00

I have written tests and always run my trial balance when the system is 'stopped', but under write-heavy database load during seeding lots of records, I noticed my trial balance is WRONG about 1 out of 10 tries.

When it's at rest (no inserts), its always correct at 0.00 however.

When I insert transactions they're always in a transaction, like this:

2000.times do |i|
  ActiveRecord::Base.transaction do
    puts "#{i} ==================================================================="

    entry = JournalEntry.create!(description: 'Purchase mower on credit', user: user)
    entry.line_items.create!(amount: Money.from_amount(1551.75).cents, account: property.accounts.find_by(name: 'Equipment'), side: :debit)
    entry.line_items.create!(amount: Money.from_amount(1551.75).cents, account: property.accounts.find_by(name: 'Accounts Payable'), side: :credit)
  end
end

The fact it breaks under load makes me think I'm not understanding something vital about how Rails transactions work......

What could be causing this?

FWIW my trial balance function (GeneralLedger.new(property).trial_balance) executes the following pseodo-SQL (NOT in a transaction):

SELECT sum(...) WHERE account = 'asset'
SELECT sum(...) WHERE account = 'liability'
SELECT sum(...) WHERE account = 'equity'
SELECT sum(...) WHERE account = 'income'
SELECT sum(...) WHERE account = 'expense'

I then add them together according to the Accounting Formula to arrive at 0.00:

  def trial_balance
    balance_category(:asset) - (balance_category(:liability) + balance_category(:equity) + balance_category(:income) - balance_category(:expense))
  end

The balance_category function is what triggers each SELECT for a total of 5 times, once for each category.

Because its returning 0 it means it's somehow selecting when it's halfway inserted........... I have no idea how this is happening?

I could understand if the creation of the journal entry/line item was not in a transaction and it was SELECTing halfway inserted rows, but it should only select from the entire group as a whole after the transaction ends?

Upvotes: 1

Views: 84

Answers (1)

tadman
tadman

Reputation: 211560

If you want to avoid repeated statements, collapse it into one, something of this form:

SELECT account, SUM(...) AS amount FROM ...
   WHERE account IN ('asset', 'liability', ...)
   GROUP BY account

You can fetch these like this:

where(account: ACCOUNT_TYPES).group(:account).pluck('account, SUM(...)')

Where ACCOUNT_TYPES is an array of the account types you need to fetch.

You can always take this pluck form and convert to a quick look-up hash with .to_h then use it like this:

balance_category = ...where(...)...pluck(...).to_h

balance_category[:asset]

If you need a default value, consider:

balance_category = Hash.new(0).merge(...where(...)...pluck(...).to_h)

Where that default can be integer (0) or a float (0.0) or anything at all.

Upvotes: 1

Related Questions