The Very Hungry Transaction

The story begins with a little database transaction. As the days go by, more and more business requirements cause the transaction to grow in size. We soon discover that it isn't a little transaction anymore, and it now poses a serious risk to our application and business. What went wrong, and how can we fix it?

In this talk we'll witness a database transaction gradually grow into a liability. We'll uncover some common but problematic patterns that can put our data integrity and database health at risk, and then offer strategies for fixing and preventing these patterns.

Transcript

2. Cat R. Pillar • Job — Developer at BugHub • Dislikes — Birds, Software Bugs • Likes — Leaves, Actual Bugs
3. BugHub • Large Online Community of Bugs • More Bugs Than Any Other Site
4. transaction do ... end
5. Monday A Transaction is Born
6. Monday A Transaction is Born
7. “ I’m very hungry. I want groceries delivered to my chrysalis.
8. Monday def submit @order.save! end
9. Monday Order id
10. Monday Order id Product sku
11. Monday Order id OrderItem order_id sku Product sku
12. Monday Order id OrderItem order_id sku quantity Product sku
13. Monday def submit @order.save! end
14. Monday def submit @order.save! end => BEGIN
15. Monday def submit @order.save! end => BEGIN => INSERT INTO orders ...
16. Monday def submit @order.save! end => BEGIN => INSERT INTO orders ... => INSERT INTO order_items ... => INSERT INTO order_items ... ...
17. Monday def submit @order.save! end => BEGIN => INSERT INTO orders ... => INSERT INTO order_items ... => INSERT INTO order_items ... ... => COMMIT / ROLLBACK
18. Tuesday Slow Transactions
19. “ I was so excited about my order, but it was canceled and now the items are not available.
20. Tuesday Order id OrderItem order_id sku quantity Product sku
21. Tuesday Order id OrderItem order_id sku quantity Product sku inventory
22. Tuesday def submit @order.save! end
23. Tuesday def submit @order.save! @order.update_product_inventory end
24. Tuesday => UPDATE products
25. Tuesday => UPDATE products SET inventory = inventory - :order_item_quantity
26. Tuesday => UPDATE products SET inventory = inventory - :order_item_quantity WHERE sku = :order_item_sku
27. Tuesday => UPDATE products SET inventory = inventory - :order_item_quantity WHERE sku = :order_item_sku => UPDATE products ...
28. Tuesday => UPDATE products SET inventory = inventory - :order_item_quantity WHERE sku = :order_item_sku => UPDATE products ... => UPDATE products ...
29. Tuesday => UPDATE products SET inventory = inventory - :order_item_quantity WHERE sku = :order_item_sku => UPDATE products ... => UPDATE products ... => UPDATE products ...
30. Tuesday def submit @order.save! @order.update_product_inventory end Should be Atomic
31. Tuesday def submit Order.transaction do @order.save! @order.update_product_inventory end end
32. Tuesday => BEGIN => INSERT INTO orders ... => INSERT INTO order_items ... => INSERT INTO order_items ... ... => UPDATE products ... => UPDATE products ... ... => COMMIT
33. !Bug Report Increased 500 responses for placing orders
34. Tuesday Lock Contention "#
35. Tuesday Lock Contention "# Bug 1 Bug 2
36. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN
37. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN => UPDATE ... sku = APL1 => UPDATE ... sku = APL1
38. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN => UPDATE ... sku = APL1 => UPDATE ... sku = APL1 "Row locked ... Waiting ...
39. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN => UPDATE ... sku = APL1 => UPDATE ... sku = APL1 "Row locked ... Waiting ... "Row locked ... Waiting ...
40. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN => UPDATE ... sku = APL1 => UPDATE ... sku = APL1 "Row locked ... Waiting ... "Row locked ... Waiting ... "Row locked ... Waiting ...
41. Tuesday Lock Contention "# Bug 1 Bug 2 => BEGIN => BEGIN => UPDATE ... sku = APL1 => UPDATE ... sku = APL1 "Row locked ... Waiting ... "Row locked ... Waiting ... "Row locked ... Waiting ... => COMMIT "Row locked ...
42. Tuesday Little Opportunity for Contention => BEGIN => UPDATE products ... WHERE sku = APL1 => UPDATE products ... WHERE sku = ORG5 => COMMIT < 1 millisecond
43. Tuesday Large Opportunity for Contention => BEGIN => UPDATE products ... WHERE sku = APL1 => UPDATE products ... WHERE sku = ORG5 => COMMIT > 1 second
44. Tuesday Large Opportunity for Contention => BEGIN => UPDATE products ... WHERE sku = APL1 => UPDATE products ... WHERE sku = ORG5 => COMMIT Slow Queries
45. Tuesday Large Opportunity for Contention => BEGIN => UPDATE products ... WHERE sku = APL1 => UPDATE products ... WHERE sku = PLM3 => UPDATE products ... WHERE sku = BRY4 => UPDATE products ... WHERE sku = PER2 ... (hundreds more) => UPDATE products ... WHERE sku = ORG5 => COMMIT Too Many Queries
46. Tuesday Large Opportunity for Contention => BEGIN => UPDATE products ... WHERE sku = APL1 => UPDATE products ... WHERE sku = PLM3 => UPDATE products ... WHERE sku = BRY4 => UPDATE products ... WHERE sku = PER2 ... (hundreds more) => UPDATE products ... WHERE sku = ORG5 => COMMIT Waiting on Locks
47. Tuesday def submit Order.transaction do @order.save! @order.update_product_inventory end end
48. Tuesday def submit Order.transaction do @order.save! @order.update_product_inventory end end
49. Wednesday Background Jobs
50. “ I don’t have time to waste. I want my orders to submit without delay.
51. Wednesday class Order < ApplicationRecord after_create :finalize_order end
52. Wednesday class Order < ApplicationRecord after_create :finalize_order def finalize_order # Sync with billing platform # Send confirmation email # etc. end end
53. Wednesday class Order < ApplicationRecord after_create :finalize_order def finalize_order OrderFinalizationJob.perform_later end end
54. !Bug Report Confirmation emails delayed by minutes
55. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job
56. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails
57. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ...
58. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails
59. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails => COMMIT
60. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails => COMMIT => Job Retries and Succeeds
61. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails => ROLLBACK
62. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails => ROLLBACK => Job Retries and Fails
63. Wednesday => BEGIN => INSERT INTO orders ... => Enqueue Job => Job Executes and Fails => UPDATE products ... => Job Retries and Fails => ROLLBACK => Job Retries and Fails => Job Retries and Fails
64. Wednesday class Order < ApplicationRecord after_create :finalize_order def finalize_order OrderFinalizationJob.perform_later end end
65. Wednesday class Order < ApplicationRecord after_create_commit :finalize_order def finalize_order OrderFinalizationJob.perform_later end end
66. Wednesday => BEGIN => INSERT INTO orders ... => UPDATE products ... => COMMIT => Enqueue Job => Job Executes and Succeeds
67. Thursday External Services
68. “ I want my orders fulfilled quickly and accurately.
69. Thursday def submit Order.transaction do @order.save! @order.update_product_inventory end end
70. Thursday def submit Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end
71. Thursday => BEGIN => INSERT INTO orders ... => External Call => UPDATE products ... => COMMIT
72. !Bug Report Site-wide failure
73. Thursday def submit Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end Responding Slowly
74. Thursday => BEGIN => INSERT INTO orders ... => UPDATE products ... => COMMIT / ROLLBACK Slow External Call
75. Thursday => BEGIN => INSERT INTO orders ... => UPDATE products ... => COMMIT / ROLLBACK Idle
76. Thursday => BEGIN => INSERT INTO orders ... => UPDATE products ... => COMMIT / ROLLBACK Idle Locks Held
77. Thursday => BEGIN => INSERT INTO orders ... => UPDATE products ... => COMMIT / ROLLBACK Idle Connection in Use
78. Thursday Database
79. Thursday BEGIN Database BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN
80. Thursday Database !Connections Exhausted !Connections Exhausted !Connections Exhausted !Connections Exhausted
81. Thursday def submit Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end Configure Timeout
82. Thursday def submit FulfillmentClient.submit_order(@order) Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end
83. Thursday def submit Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end FulfillmentClient.submit_order(@order) end
84. Thursday def submit Order.transaction do @order.save! @order.update_product_inventory end FulfillmentClient.submit_order(@order) @order.update!(status: :submit) end
85. Friday Multiple Databases
86. “ I expect a reliable website. BugHub has been having a lot of problems lately.
87. Friday Products Orders
88. Friday def submit Order.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end
89. Friday def submit Order.transaction do Product.transaction do @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory end end end
90. !Bug Report All of the above, but worse
91. Friday def submit Order.transaction do Product.transaction do ... end end end Problems Affect Both Databases
92. !Bug Report Products incorrectly marked out-of-stock
93. Friday def submit Order.transaction do Product.transaction do ... end end end Not Atomic
94. Friday Orders Database => BEGIN => INSERT INTO orders ... => COMMIT Products Database => BEGIN => UPDATE products ... => COMMIT Both Commit $
95. Friday Orders Database => BEGIN => INSERT INTO orders ... => ROLLBACK Products Database => BEGIN => UPDATE products ... => ROLLBACK Both Rollback $
96. Friday Orders Database => BEGIN => INSERT INTO orders ... => ROLLBACK Products Database => BEGIN => UPDATE products ... => COMMIT Not Atomic %
97. Friday Orders Database => BEGIN => INSERT INTO orders ... Products Database => BEGIN => UPDATE products ... => COMMIT Idle
98. Friday Orders Database => BEGIN => INSERT INTO orders ... Products Database => BEGIN => UPDATE products ... => COMMIT Idle ❌ Connection Failed
99. Friday def submit Order.transaction do Product.transaction do ... end end end
100. Friday def submit Product.transaction do ... end Order.transaction do ... end end
101. Friday def submit Order.transaction do ... end Product.transaction do ... end end
102. Weekend Rest and Reflect
103. Weekend External Calls within Transactions are a Risk
104. Weekend External Calls within Transactions are a Risk • Data Integrity Problems
105. Weekend External Calls within Transactions are a Risk • Data Integrity Problems • Cascading Failures
106. Weekend External Calls within Transactions are a Risk • Data Integrity Problems • Cascading Failures • Includes: HTTP requests, emails, jobs, queries to other databases, etc.
107. Weekend Slow Transactions are a Risk
108. Weekend Slow Transactions are a Risk • Slow Requests
109. Weekend Slow Transactions are a Risk • Slow Requests • Contention
110. Weekend Slow Transactions are a Risk • Slow Requests • Contention • Resource Exhaustion
111. Monday Metamorphosis
112. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday
113. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Small Incremental Changes
114. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Defer Until After Commit
115. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Defer Until After Commit - Rails 7.2 - Automatically delay Active Job enqueues to after commit #51426
116. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Defer Until After Commit - Rails 7.2 - Automatically delay Active Job enqueues to after commit #51426 - Rails 7.2 - Allow to register transaction callbacks outside of a record #51474
117. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Identify Other Problematic Transactions
118. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Identify Other Problematic Transactions - Database Observability
119. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Identify Other Problematic Transactions - Database Observability - Rails 7.1 - Instrument Active Record transactions #49192
120. def submit Order.transaction do Product.transaction do Chrysalis.transaction do code.not_meant_for_reading! @order.save! FulfillmentClient.submit_order(@order) @order.update_product_inventory @product_suggestions = SuggestionService.build_for(@bug) seriously.stop_reading_this! if @order.bug.caterpillar? @bug.very_hungry! basket = Chrysalis::Basket.create!(@bug) PromotionMailer.new_basket(basket).deliver_later @product_suggestions << Chrysalis.default_items_for(@bug) else @bug.book_suggestions.create!( Book.find_by(author: "Eric Carle”, bug: @order.bug) ) end @bug.suggest(@product_suggestions) @bug.friends.each do |friend| @order.share_with(friend) if @bug.sharing_orders? end rescue => e handle_transaction_error(e) ensure nobody.should_have_read_any_of_this end end end end Monday Prevent Further Problematic Transactions
121. Safe Transactions
122. Safe Transactions Keep Transactions Short - Ideally < 1 Second
123. Safe Transactions Fast Queries
124. Safe Transactions Limit # of Queries - Ideally < 100
125. Safe Transactions No External Calls
126. Safe Transactions As Little Code as Possible
127. Safe Transactions As Little Code as Possible - Default to after_commit callbacks
128. Safe Transactions Do You Really Need a Transaction?
129. Safe Transactions • Keep transactions short • Fast queries • Limit # of queries • No external calls • As little code as possible • Do you really need a transaction?
130. Daniel Colson @composerinteralia Illustrated by ChangHo Kim and DongBeom Kim