I’ve been making a lot of progress learning the ins and outs of ROM. While I’m learning to like it a lot, I’ve found that there are many functionalities that seem inconsistent between rom-core (interfaces) and rom-sql. Several issues are changeset-related, and I look forward to seeing how that progresses (and how I can help contribute).
A recent issue that I ran into has to do with testing concurrent database connections and committing 2 changesets within a
savepoint transaction. While it’s unclear why MySQL gets deadlocks and PostgreSQL does not, the issue pertains to allowing Sequel to retry a transaction when there’s a deadlock.
This example raises a
ROM::SQL::Error exception when there is a deadlock in MySQL and Sequel does not retry when it should:
changesets = [ build_changeset1, build_changeset2 ] gateway.transaction(savepoint: true, retry_on: Sequel::SerializationFailure) do changesets.each(&:commit) end # ROM::SQL::Error: # Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction
The workaround was to call
multi_insert with the changeset data:
changesets = [ build_changeset1, build_changeset2 ] gateway.transaction(savepoint: true, retry_on: Sequel::SerializationFailure) do changesets.each do |changeset| changeset.relation.multi_insert(changeset.to_a) end end
If there’s a deadlock, the workaround will retry. It’s possible to output a message with Sequel’s
before_retry option to indicate that a deadlock was retried.
Here’s the commit that addresses the issue in the project we discovered this in: https://github.com/RailsEventStore/rails_event_store/pull/302/files
All this said, we didn’t dig any deeper to see if the ROM/Changeset functionality we are still using is a culprit in causing the deadlocks. However, the ability to retry is sufficient at the moment.
P.S. Anyone know why PostgreSQL doesn’t deadlock when MySQL does? We’re able to use the normal ROM changeset commit functionality for PG and only use the workaround for MySQL.