Why does #lock_tables need to be the outermost transaction?

[keithpitt]

Consider the following code:

class Invoice::Payer
  # ...

  def pay
    @invoice.lock do
      payment = CreditCard::Charger.charge(@invoice.account.credit_card, @invoice.description, @invoice.amount)
      if payment.success?
        @invoice.update(paid: true)
      end
    end
  end
end

class CreditCard::Charger
  # ...

  def charge
    # ...

    from_account = DoubleEntry.account(:credit_card, scope: @credit_card.account)
    to_account = DoubleEntry.account(:sales, scope: @credit_card.account)

    DoubleEntry.transfer(@amount, from: from_account, to: to_account, code: :purchase)
  end
end

This seems like pretty reasonable code to me, but unfortunately doesn't work because in the Invoice class, I'm locking the invoice (which creates a transaction), and then the DoubleEntry#transfer method fails because it's transaction is not the outer-most one.

What's the reasoning behind making it the outer-most transaction? What would the world be like if it "just worked"?

[eadz]

eadz#6

That branch passes, so there isn't any test coverage to help explain it.

There also isn't any git history on the locking file to help us :/ https://github.com/envato/double_entry/commits/master/lib/double_entry/locking.rb

In theory, if the code inside CreditCard::Charger doesn't depend on @invoice I can't see how this can cause any issue, assuming @credit_card is loaded and doesn't reload, and no other SQL queries.

I think a main point is you want all your locks at the top, and DoubleEntry certainly does when it loads and/or creates the accounts. It does make using DoubleEntry very hard in cases like the above, adding itself as an outer layer to any code that wants to do a financial transaction inside it, adding the dependency of knowing the accounts to lock to the code that just wants to do some database query along side a DoubleEntry transfer.

I would prefer if that wasn't the case, as I like the look of the code above. It would be great to know exactly why is is required, so that if we understand the risks can design around them opt to not have DoubleEntry as an outer layer.

[keithpitt]

Thanks for the response @eadz - I think @notahat may know something? I vaguely remember.

[notahat]

It's been a while, and this is complicated, but it goes something like this:

You want to grab all your locks at the start of the transaction, and do them in a consistent order, to minimise deadlocks.

Deadlocks will happen though, particularly when we have to create AccountBalance records on the fly. That means we need the capability to retry the transaction.

The fun is, when you get a deadlock, MySQL will always roll back the outermost transaction. If double entry didn't own the outermost transaction, that could roll back other things you've done.

In the example above, if you wrote anything to the DB between @invoice.lock and DoubleEntry.transfer, you would lose that data whenever a deadlock happened.

Make any sort of sense?

[notahat]

Or, put another way:

You want reliable locking and deadlock handling? Hard things are hard.

[pablolee]

@rabidcarrot had also mentioned something about concurrency issues if all accounts weren't locked at the outermost level

[notahat]

Yep. If you don't do all your locking in one place and in a consistent order, you're way more likely to get deadlocks.

[keithpitt]

Thanks @notahat, I wonder how much of that is MySQL specific? I'm trying to find some docs on how deadlocks are handled in MySQL and Postgres, but as you put it...hard things are hard :P

[notahat]

I'm not sure whether Postgres behaves the same way.

[eadz]

DoubleEntry doesn't prevent deadlocks from within the lock accounts block. You could do

DoubleEntry.lock_accounts(*accounts) do
  @user.lock
  @product.lock
end

in one place
and

DoubleEntry.lock_accounts(*accounts) do
  @product.lock
  @user.lock
end

in another, which could cause a deadlock so it's really up to the programmer to avoid this.

But the current locking API has really caused issues due to the outer transaction requirement ( the main issue is demonstrated by the lack of ability to write code like in the first post). Maybe there is a way to improve this API?

The fun is, when you get a deadlock, MySQL will always roll back the outermost transaction. If double entry didn't own the outermost transaction, that could roll back other things you've done.

In the Keith's example above, is that a problem?

Deadlocks will happen though, particularly when we have to create AccountBalance records on the fly. That means we need the capability to retry the transaction.

If this is the main reason, I don't see any need for the creation of account balance records to have to happen during the transaction. It could be a separate transaction that happened some point earlier, for example by calling DoubleEntry.ensure_account_balance(@account) and that transaction may have to be the outer most transaction, but at least we have separated out the creating new balance records from the lock balance records concern, maybe allowing a more flexible locking API.

[notahat]

Maybe there is a way to improve this API?

I don't think there is a way to improve the locking without either a) changing ActiveRecord's behaviour, b) changing MySQL's behaviour, or c) changing the database model used.

Keep in mind that this database model is a legacy model that had locking grafted on after the fact. If you were designing to make locking easy, it would likely look pretty different.

(If I was designing from scratch, I'd actually design for eventual consistency and not have to muck around with all this locking. That's the way industrial strength financial systems work in most places.)

In the Keith's example above, is that a problem?

Keith's example has a "...". It depends on what's in the "...".

More to the point, there's no way to programmatically know if something happened in the "..." that might cause a problem, so if you allow stuff to happen, you'll get random nasty failures. The failures will be timing related, so are very unlikely to show up in testing. (See the jack_hammer script in this project for what you actually have to do to detect problems.)

[eadz]

Maybe there is a way to improve this API?

I don't think there is a way to improve the locking without either a) changing ActiveRecord's behaviour, b) changing MySQL's behaviour, or c) changing the database model used.

Requiring DoubleEntry.lock_tables to be the outermost transaction can result in less than ideal code.

Keith's example would be great to be able to do. I believe it is possible to change the api without a) b) or c), but by doing d) - requiring account balance records to be created before doing a double entry transfer.

The creation of the account balance records is the tricky thing that really needs that outer lock, due to the rollback and retry stuff. Looking at the locking code, it is very AccountBalance specific.

I believe Keith's example could work provided he simply guaranteed that all required double entry account records already existed. This would require more thought on the part of someone implementing a double entry solution, but it could be as simple as creating a a matching DoubleEntry::AccountBalance every time you create a CreditCard record. That way, when DoubleEntry tries to lock accounts, the lock won't fail due to no record existing.

[notahat]

The AccountBalance records exist primarily to enable locking! If you don't do per-account locks somehow, the totals in the lines table will get out of whack because of race conditions around knowing the current balance. (This is exactly the problem we had back when this code was first written, and it's what all this locking magic is there to fix.)

If I was building finance code from scratch today, I'd make it eventually consistent. i.e. Just record that money was moved inside the transaction, and calculate balances after the fact. That's a better model in many ways, but it's not the model of this library, and there's not a way to make this library use that model without fundamentally changing the underlying data structures.

[eadz]

@notahat I'm not saying do away with account balance records, or per-account locking, I'm saying the really complicated "ensure outermost transaction" locking code is due to the creation of account balance records, not locking them. ( or, trying to lock them when they don't exist ).

My hypnosis is: that if, as part of your application, you ensure that all account balance records are created separately before using them ( and you may need to "ensure outermost transaction" for that ), once you have that assurance, you can safely do DoubleEntry transactions within another transaction.

[notahat]

@eadz: There's definitely a lot of complexity in there to support lazy creation of account balances. And yes, you could simplify things if you assumed all account balances were created up front.

I don't think that lazy creation is the only reason for the outermost transaction requirement though.

[eadz]

@eadz: There's definitely a lot of complexity in there to support lazy creation of account balances. And yes, you could simplify things if you assumed all account balances were created up front.

I don't think that lazy creation is the only reason for the outermost transaction requirement though.

I think it is the only reason that really matters.

I think this is the essence of this Issue "Why does #lock_tables need to be the outermost transaction?" So we can agree that it's because of the account balance creation, but why else?

What is the other reason for being an outermost transaction? ( Other than deadlocks which IMHO having an outermost transaction requirement doesn't solve that problem on its own, although it may help ).

And the other question is, whatever the reason, is it so important that it prevents us from having a cleaner API?