Select statements starts in read-only transactions

[woblavobla]

Many rails applications performs all statements without explicitly started transactions, so they are started in write mode, which brings db to perform write on even every select statement. This patch allows our instance of redmine(rails based) work faster, because we have large amounts of select's on each request. I'll attach few benchmarks later.

[rowland]

Thank you for the cleaner PR. It will take some time to analyze the implications of the changes on a broad array of use cases.

[rowland]

In my benchmarking, the read-only selects are slower, probably because of the need to prepare each statement twice.

                        user     system      total        real
read-write_selects  1.030000   0.940000   1.970000 (  6.380230)
read-only_selects   1.390000   1.600000   2.990000 ( 10.372940)

 require 'benchmark'

  def test_readonly_selects
    sql_schema = "CREATE TABLE TEST (ID INT, NAME VARCHAR(20))"
    sql_insert = "INSERT INTO TEST (ID, NAME) VALUES (?, ?)"
    sql_select = "SELECT * FROM TEST ORDER BY ID"

    Benchmark.bm do |x|
      x.report("read-only_selects") do
        parms = @parms.merge(:readonly_selects => true)
        Database.create(parms) do |conn|
          conn.execute(sql_schema)
          conn.transaction { 10.times { |i| conn.execute(sql_insert, i, "NAME#{i}") } }
          10000.times do
            result = conn.query(sql_select)
          end
        end
      end
    end
  end

  def test_readwrite_selects
    sql_schema = "CREATE TABLE TEST (ID INT, NAME VARCHAR(20))"
    sql_insert = "INSERT INTO TEST (ID, NAME) VALUES (?, ?)"
    sql_select = "SELECT * FROM TEST ORDER BY ID"

    Benchmark.bm do |x|
      x.report("read-write_selects") do
        Database.create(@parms) do |conn|
          conn.execute(sql_schema)
          conn.transaction { 10.times { |i| conn.execute(sql_insert, i, "NAME#{i}") } }
          10000.times do
            result = conn.query(sql_select)
          end
        end
      end
    end
  end

Do you have benchmarks that show this method to be faster?

[woblavobla]

@rowland I'll attach them a bit later. But i think that statements not preparing twice, because trace not showing this. And this patch decrease count of writes to db and disk.