Skip to content

Instantly share code, notes, and snippets.

@pjambet

pjambet/atomic.rb

Last active Oct 13, 2020
Embed
What would you like to do?
Companion code for atomic operations in sql
require 'minitest/autorun'
require 'pg'
require 'mysql2'
require 'sequel'
def get_db(database_name)
case database_name
when :pg
Sequel.connect('postgres://pierre:@localhost:5432/pierre')
when :mysql
Sequel.connect('mysql2://root:@localhost:3306/test')
end
end
def execute(database_name, isolation_level, update_mode: :relative)
main_db = get_db(database_name)
main_db["update inventories set quantity = 0 where sku = 'ABC';"].first
t0 = Time.now
threads = []
5.times do |i|
threads << Thread.new do
db = get_db(database_name)
100.times do
done = false
while !done
begin
db.transaction(isolation: isolation_level) do
val = nil
if update_mode == :relative
db.run("update inventories set quantity = quantity + 1 where sku = 'ABC';")
elsif update_mode == :absolute
val = db["select quantity from inventories where sku = 'ABC';"].first[:quantity]
db.run("update inventories set quantity = #{val + 1} where sku = 'ABC';")
end
end
done = true
rescue StandardError => e
# Simply retrying until the transaction commits successfully, this
# is only useful for REPEATABLE READ and SERIALIZABLE isolation
# levels
end
end
end
end
end
threads.map(&:join)
puts "Database: #{database_name} with #{isolation_level} took: #{Time.now - t0}"
main_db["select * from inventories where sku = 'ABC';"].first[:quantity]
end
describe "Relative updates" do
describe "with pg" do
# Local setup:
# $> psql
# $> CREATE TABLE inventories(sku VARCHAR(3) PRIMARY KEY, quantity INTEGER);
# $> insert into inventories values ('ABC', 0);
# $> insert into inventories values ('DEF', 0);
describe "read uncommitted" do
it "works" do
execute(:pg, :uncommitted).must_equal 500
end
end
describe "read committed" do
it "works" do
execute(:pg, :committed).must_equal 500
end
end
describe "repeatable read" do
it "works" do
execute(:pg, :repeatable).must_equal 500
end
end
describe "serializable" do
it "works" do
execute(:pg, :serializable).must_equal 500
end
end
end
describe "with mysql" do
# Local setup:
# $> mysql
# $> CREATE DATABASE test;
# $> CREATE TABLE inventories(sku VARCHAR(3) PRIMARY KEY, quantity INTEGER);
# $> INSERT INTO inventories VALUES ('ABC', 0);
# $> INSERT INTO inventories VALUES ('DEF', 0);
describe "read uncommitted" do
it "works" do
execute(:mysql, :uncommitted).must_equal 500
end
end
describe "read committed" do
it "works" do
execute(:mysql, :committed).must_equal 500
end
end
describe "repeatable read" do
it "works" do
execute(:mysql, :repeatable).must_equal 500
end
end
describe "serializable" do
it "works" do
execute(:mysql, :serializable).must_equal 500
end
end
end
end
describe "Absolute updates" do
describe "with pg" do
describe "read uncommitted" do
it "works" do
execute(:pg, :uncommitted, update_mode: :absolute).wont_equal 500
end
end
describe "read committed" do
it "works" do
execute(:pg, :committed, update_mode: :absolute).wont_equal 500
end
end
describe "repeatable read" do
it "works" do
execute(:pg, :repeatable, update_mode: :absolute).must_equal 500
end
end
describe "serializable" do
it "works" do
execute(:pg, :serializable, update_mode: :absolute).must_equal 500
end
end
end
describe "with mysql" do
describe "read uncommitted" do
it "works" do
execute(:mysql, :uncommitted, update_mode: :absolute).wont_equal 500
end
end
describe "read committed" do
it "works" do
execute(:mysql, :committed, update_mode: :absolute).wont_equal 500
end
end
describe "repeatable read" do
it "works" do
# This is a major difference with postgres:
execute(:mysql, :repeatable, update_mode: :absolute).wont_equal 500
end
end
describe "serializable" do
it "works" do
execute(:mysql, :serializable, update_mode: :absolute).must_equal 500
end
end
end
end
@pjambet

This comment has been minimized.

Copy link
Owner Author

@pjambet pjambet commented Apr 10, 2020

The second revision addresses a blatant mistake where the repeatable read and serializable tests in the absolute case where not actually using the absolute update pattern (read the value first, increment it second).

Interestingly it highlights that postgresql behaves the same in this use case for both repeatable read and serializable and mysql doesn't. My understanding is that MySQL is closer to the actual spec and PostgreSQL is aggressively making repeatable read more restrictive than it needs to be to follow the spec.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.