Create a gist now

Instantly share code, notes, and snippets.

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} 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).must_equal 500
end
end
describe "serializable" do
it "works" do
execute(:pg, :serializable).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
execute(:mysql, :repeatable).must_equal 500
end
end
describe "serializable" do
it "works" do
execute(:mysql, :serializable).must_equal 500
end
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment