mysql_ruby_performance.rb

gistfile1.txt

# In MySQL you cannot have nested transactions, instead you have 'savepoints'.
#
# Without `:requires_new` AR realizes that you are in transaction, but does not
# create 'savepoint' automatically.

def nested_trx(requires_new: false, rollback: false)
  ActiveRecord::Base.transaction do
    # Load some data to generate sql
    User.find_by_id(1)
    # now start nested transaction.
    # MySQL does not support nested transactions, but it has 'checkpoints'.
    ActiveRecord::Base.transaction(requires_new: requires_new) do
      # Load some data to generate sql
      User.find_by_id(2)
      raise ActiveRecord::Rollback if rollback
    end
    User.find_by_id(3)
  end
end

nested_trx(requires_new: false)
#  (0.2ms)  BEGIN
# User Load (0.4ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
# User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
# User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1
#  (0.1ms)  COMMIT

nested_trx(requires_new: false, rollback: true)
#  (0.3ms)  BEGIN
# User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
# User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
# User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1
#  (0.1ms)  COMMIT

nested_trx(requires_new: true)
#  (0.3ms)  BEGIN
# User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
#  (0.2ms)  SAVEPOINT active_record_1
# User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
#  (0.2ms)  RELEASE SAVEPOINT activ