repeatable reads in mysql and pg

readme.md

Mysql doesn't auto lock or version lock records when select in repeatable read transactions. While PG can forbid concurrent updates.

The test table DDL in PG:

create table articles (
  "id" serial,
  "n" integer
);

For mysql to avoid race conditions, there are 3 ways:

• (pessimistic lock) select ... for lock in share mode in transaction
• (pessimistic lock) select ... for upadte in transaction and other places that updates the record(s)
• (optimistic lock) use optimistic locks that can cause a phantom read -- then it will forbid the update

---

PG transacions (note that PG does avoid phantom reads, see the paragraph after the table of ISO isolation definition table):

http://www.postgresql.org/docs/9.1/static/transaction-iso.html

---

Edit: this example is not phantom read, just a pitfall of repeatable reads in mysql

test_my.rb

require "mysql2"

c = Mysql2::Client.new(:host => "localhost", :username => "root")
c.query 'use dummy'
c.query 'set autocommit=1'
c.query 'delete from articles'
puts "at first n = 1"
c.query 'insert into articles (n) values (1)'
id = c.query('select * from articles').first['id']

t = Thread.new do
  c2 = Mysql2::Client.new(:host => "localhost", :username => "root")
  c2.query 'use dummy'
  c2.query 'set autocommit=1'
  c2.query 'set transaction isolation level repeatable read'

  c2.query 'start transaction'
  # NOTE to really avoid race conditions, you need "select ... for update"
  n = c2.query("select * from articles where id = #{id}").first['n']
  puts "c2 selected n = #{n}"
  sleep 2
  # reading an incorrect snapshot
  n = c2.query("select * from articles where id = #{id}").first['n']
  puts "c2 selected n = #{n}"
  c2.query "update articles set n = #{n+1} where id=#{id}"
  puts "c2 set n = #{n+1}"
  c2.query 'commit'
end

sleep 1
puts "c set n = 2"
c.query 'set transaction isolation level repeatable read'
c.query 'start transaction'
c.query "select * from articles where id = #{id} for update"
c.query "update articles set n = 2 where id = #{id}"
c.query 'commit'

t.join

test_my_optimistic_lock.rb

require "active_record"
require "mysql2"
require "logger"

ActiveRecord::Base.logger = Logger.new($stdout)
ActiveRecord::Base.logger.level = Logger::DEBUG

def connect
  ActiveRecord::Base.establish_connection \
    adapter: 'mysql2',
    database: 'dummy',
    username: 'root',
    host: 'localhost',
    pool: 5
end

def init
  Article.delete_all
  ar = Article.new
  ar.n = 1
  ar.save!
end

level = {isolation: :repeatable_read}

# columns: id serial, n integer, lock_version integer
class Article < ActiveRecord::Base
end

fork do
  connect
  init
  Article.transaction level do
    a = Article.first
    puts "t1: #{a.n}"
    sleep 2
    a.n += 1
    a.save!
    puts "t1 updated"
  end
end

fork do
  connect
  sleep 0.3
  Article.transaction level do
    a = Article.first
    puts "t2: #{a.n}"
    sleep 1
    a.n += 1
    a.save!
    puts "t2 updated"
  end
end

sleep 3
puts '-' * 50
connect
puts Article.first.n

test_pg.rb

require "pg"

c = PGconn.open dbname: 'dummy'
c.exec "set autocommit to on" # NOTE, if off, it will require transaction wrapping single statement updates

c.exec 'delete from articles'
puts "at first n = 1"
c.exec 'insert into articles (n) values (1)'
id = c.exec('select * from articles').to_a.first['id']

t = Thread.new do
  c2 = PGconn.open dbname: 'dummy'
  c2.exec "set autocommit to on"

  c2.exec 'start transaction isolation level repeatable read'
  n = c2.exec("select * from articles where id = #{id}").to_a.first['n'].to_i
  puts "c2 selected n = #{n}"
  sleep 2
  c2.exec "update articles set n = #{n+1} where id = #{id}"
  puts "c2 set n = #{n+1}"
  c2.exec 'commit'
end

sleep 1
puts "c set n = 2"
c.exec "update articles set n = 2 where id = #{id}"

t.join