#Rails + #MySQL / Transaction & INSERT RECORD & ROLLBACK / Lock and wait example

Pattern1 - ProcessA

ActiveRecord::Base.transaction { User.create!(unique_id: 'X1'); sleep 15; raise; }
# (0.7ms)  BEGIN
# User Create (0.9ms)  INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('X1', '2020-01-09 08:18:34')
#
# ... wait ...
#
#  (10.4ms)  ROLLBACK
# from (pry):14:in `block in <main>'

Pattern1 - ProcessB

ActiveRecord::Base.transaction { User.create!(unique_id: 'X1'); puts 'OK!'; }
# 
# (0.5ms)  BEGIN
#
# ... wait ...
#
# User Create (2573.1ms)  INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('X1', '2020-01-09 08:18:41')
# OK!
#  (9.4ms)  COMMIT
# => nil

Pattern2 - ProcessA

ActiveRecord::Base.transaction { User.create!(unique_id: 'X2'); sleep 15; puts 'OK!'; }
# (1.0ms)  BEGIN
# User Create (2.2ms)  INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('X2', '2020-01-09 08:22:14')
#
# ... wait ...
#
# OK!
#  (19.2ms)  COMMIT
# => nil

Pattern2 - ProcessB

ActiveRecord::Base.transaction { User.create!(unique_id: 'X2'); puts 'OK!'; }
#
# ... wait ...
#
#    (0.6ms)  BEGIN
# User Create (6831.2ms)  INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('X2', '2020-01-09 08:22:22')
# (5.2ms)  ROLLBACK
# ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry 'X2' for key 'index_users_on_unique_id': INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('X2', '2020-01-09 08:22:22')

Pattern3 - ProcessA

ActiveRecord::Base.transaction { User.create!(unique_id: 'X3'); sleep 15; puts 'OK!'; }

Pattern3 - ProcessB

ActiveRecord::Base.transaction { User.create!(unique_id: 'Y1'); puts 'OK!'; }
# (0.8ms)  BEGIN
# User Create (0.9ms)  INSERT INTO `users` (`unique_id`, `created_at`) VALUES ('Y1', '2020-01-09 08:18:38')
# OK!
#  (2.9ms)  COMMIT
# => nil

Original by Github issue

チャットメンバー募集

何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

Twitter