6.0.0rc1 - Foreign Keys in Transaction Deadlocks Database

[tuxagon]

With multiple databases set up, if a model uses connects_to and has a foreign key, then any transaction will deadlock and timeout.

Steps to reproduce

I've created an example app that shows this issue https://github.com/tuxagon/20190523-rails-multi-db-foreign-key-bug-example

The setup can be seen via the commits and I can walk through the steps here.

I set up the project to work with 2 databases and added connects_to on the models for the secondary database. Each database has a simple parent-child relationship with foreign keys

• songs belong to artists (in primary)
• books belong to authors (in secondary)

The database connection is via MySQL InnoDB.

In the test/models/song_test.rb file, you will find that the test runs fine in the normal transactional test.

In the test/models/book_test.rb file, you will find that the test will timeout

Error:
BookTest#test_the_truth:
ActiveRecord::LockWaitTimeout: Mysql2::Error::TimeoutError: Lock wait timeout exceeded; try restarting transaction
    test/models/book_test.rb:5:in `block in <class:BookTest>'

Expected behavior

Foreign key relationships should be adhered to within the scope of a transaction regardless of whether a model uses connects_to.

Actual behavior

A model that uses connects_to and has a foreign key relationship will lock within a transaction.

System configuration

Rails version: 6.0.0.rc1

Ruby version: 2.6.3

MySQL version: 5.6 & 5.7

---

As a bonus, I originally found this after encountering another issue in tests where the connection was leaking between tests on different models. I added connects_to on all my models to prevent the leakage. It appeared as if you didn't declare the connection explicitly, it used the last used connection.

[tuxagon]

Update: I was searching the source around connects_to and I found a workaround that isn't bad; however, the behavior is still potentially strange since the foreign key constraint causes this blocking behavior under certain conditions.

If you do something like

class SecondaryBase < ApplicationRecord
  self.abstract_class = true

  connects_to database: { reading: :secondary, writing: :secondary }
end

In general, it's probably a better idea to abstract your connects_to into a parent class, but I'm not sure if this surprising behavior regarding foreign keys should happen regardless of whether each model explicitly uses connects_to or if it is abstracted to a parent class.

[eileencodes]

In general, it's probably a better idea to abstract your connects_to into a parent class, but I'm not sure if this surprising behavior regarding foreign keys should happen regardless of whether each model explicitly uses connects_to or if it is abstracted to a parent class.

This is the correct way to do this. You really don't want to connect multiple models to the same database because you'll eventually run out of connections. There is a limit to the number of conns you can open in mysql. I think perhaps the error is confusing but makes sense in that you can't have multiple connections doing the same work. I'm sure if foreign keys weren't used we'd see another kind of error.

I don't know if there would be an easy way to catch that it's not in a parent class...maybe we can check if abstract_class = true is set?

I was planning on working on documentation this week and will be sure to mention that it should be used in a parent model and not more than once.

[ScottRobbins]

For what it's worth, it wasn't clear to me that connects_to created a new connection.

[eileencodes]

There was a bug here and I didn't realize it. #36330 noted that Rails 6 is leaking connections and establish_connection was misbehaving. Between the fixes for #36330 and #36389 I think this is completed. Let me know if you think the docs need more!