Skip to content

Instantly share code, notes, and snippets.

@ollieh-m
Last active March 17, 2022 16:07
Show Gist options
  • Save ollieh-m/0b2a4261f77758d3049d0787cae177ae to your computer and use it in GitHub Desktop.
Save ollieh-m/0b2a4261f77758d3049d0787cae177ae to your computer and use it in GitHub Desktop.
# I tried a few test scenarios to see when a transaction might read a value that is stale.
#
# * * *
#
# Test 1
# 1. Transaction 1 gets count from recipe: 0
# 2. Transaction 2 gets count from recipe: 0
# 3. Transaction 2 updates count: 666
# 4. Transaction 1 gets count: 0
# 5. Transaction 1 commits
# 6. Transaction 2 commits
#
# Lessons:
# Transaction 1 can read the count before Transaction 2 has committed - the row isn’t locked for reading.
# The value at step 4 isn’t what Transaction 2 will ultimately update it to.
#
# Here's the code for the test. I won't show the code for the other scenarios - I used this as a template and commented out lines to change what was happening.
#
RSpec.describe "locking" do
it "explores", use_transactional_tests: false do
recipe = create(:recipe)
wait = true
threads = []
threads << Thread.new do
true while wait
Rails.logger.info "START THREAD 1"
recipe.transaction do
comments_count = recipe.reload.comments_count # 1. get count
Rails.logger.info("Thread 1 comments count: #{comments_count}")
sleep(0.5)
comments_count = recipe.reload.comments_count # 4. get count
Rails.logger.info("Thread 1 comments count: #{comments_count}")
end
end
threads << Thread.new do
true while wait
Rails.logger.info "START THREAD 2"
sleep(0.05)
recipe.transaction do
comments_count = recipe.reload.comments_count # 2. get count
Rails.logger.info "Thread 2 comments count: #{comments_count}"
recipe.update!(comments_count: 666) # 3. update count
Rails.logger.info "Thread 2 comments count updated"
sleep(2)
end
end
wait = false
threads.each(&:join)
ensure
# Need to perform manual clean up since transactional tests are disabled
Rails.logger.info "Final count: #{Recipe.last.comments_count}"
User.destroy_all
end
end
#
# * * *
#
# Test 2
#
# 1. Transaction 1 doesn’t read count (it just sleeps a bit)
# 2. Transaction 2 gets count from recipe: 0
# 3. Transaction 2 updates count: 666
# 4. Transaction 1 gets count: 0
# 5. Transaction 1 commits
# 6. Transaction 2 commits
#
# Lessons:
# Transaction 1 can read the count before Transaction 2 has committed - the row isn’t locked for reading.
# The value at step 4 isn’t what Transaction 2 will ultimately update it to.
# The value at step 4 isn't based on a value read previously in the transaction.
#
# * * *
#
# Test 3
#
# 1. Transaction 1 gets count from recipe: 0
# 2. Transaction 2 gets count from recipe: 0
# 3. Transaction 2 updates count: 666
# 4. Transaction 2 commits
# 5. Transaction 1 gets count: 0
# 6. Transaction 1 commits
#
# Lessons:
# If Transaction 1 reads the count _after_ Transaction 2 has committed a change, the value is not what Transaction 2 updated it to.
# The value in Transaction 1 is unaffected by Transaction 2.
# Transaction 1 does not prevent Transaction 2 committing a change.
#
# * * *
#
# Test 4
#
# 1. Transaction 1 doesn’t read count - it reads something unrelated (the first User record) to start the transaction
# 2. Transaction 2 gets count from recipe: 0
# 3. Transaction 2 updates count: 666
# 4. Transaction 2 commits
# 5. Transaction 1 gets count: 0
# 6. Transaction 1 commits
#
# Lesson:
# The count value throughout Transaction 1 is whatever it is when the transaction starts,
# even if Transaction 1 doesn't read the value until after Transaction 2 commits a change.
# 'Repeatable Reads' means the value is unchanged _throughout_ the transaction, not just that it's unchanged _once it is read_.
#
# * * *
#
# Based on the above, we can simulate a count being set incorrectly when two overlapping transactions want to increment it.
#
# 1. Transaction 1 doesn’t read count - it reads something unrelated (the first User record) to start the transaction
# 2. Transaction 2 gets count from recipe: 0
# 3. Transaction 2 increments count: 1
# 4. Transaction 2 commits
# 5. Transaction 1 gets count: 0
# 6. Transaction 1 increments count: 1
# 7. Transaction 1 commits
#
# Both transactions increment the count, so it should end up as 2, but they both increment it from 1.
# This happens even though Transaction 1 reads the 'current count' after Transaction 2 has committed its change.
#
require "rails_helper"
RSpec.describe "locking" do
it "explores", use_transactional_tests: false do
recipe = create(:recipe)
wait = true
threads = []
threads << Thread.new do
true while wait
Rails.logger.info "START THREAD 1"
recipe.transaction do
User.first # 1. start transaction
sleep(0.5)
comments_count = recipe.reload.comments_count # 4. get (stale) count
Rails.logger.info("Thread 1 comments count: #{comments_count}")
recipe.update!(comments_count: comments_count + 1) # 5. increment count
Rails.logger.info "Thread 1 comments count updated"
end
end
threads << Thread.new do
true while wait
Rails.logger.info "START THREAD 2"
sleep(0.05)
recipe.transaction do
comments_count = recipe.reload.comments_count # 2. get count
Rails.logger.info "Thread 2 comments count: #{comments_count}"
recipe.update!(comments_count: comments_count + 1) # 3. increment count
Rails.logger.info "Thread 2 comments count updated"
end
end
wait = false
threads.each(&:join)
ensure
# Need to perform manual clean up since transactional tests are disabled
Rails.logger.info "Final count: #{Recipe.last.comments_count}"
User.destroy_all
end
end
#
# But if we bypass the counts read during the transactions, we avoid the problem. Specifically, if we increment like this:
#
Recipe.where(id: recipe.id).update_all("comments_count = comments_count + 1")
#
# Now the final count is 2.
#
# Another way to ensure both transactions increment the count is for both transactions to first lock the recipe row.
# For the second transaction to start, it needs to lock the row, but has to wait for the first transaction to release its lock first.
# So the second transaction only starts after the first transaction has committed its change.
#
recipe.with_lock do
# read and update the count
end
#
# Locking the row for updates like this doesn't prevent other transactions _reading_ the row, but it does prevent other updates.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment