Use Writer DB for select statement

[rrennie6]

Hey there 👋

I have recently sought to begin using AWS read replica in addition to the main RDS instance in my Sequel project. I have added this to the database.yml

  servers:
    read_only:
      host: <%= ENV['DATABASE_REPLICA_HOST'] || ENV['INVENTORY_DATABASE_REPLICA_HOST'] || '' %>

Totally great! By doing so, I am able to see that SELECT statements begin to hit the replica in AWS after I deploy.
The issue comes when I try to continue using my advisory lock which includes the following code:

  ...
  db.synchronize do
     # other great stuff here that uses locking methods
  end
  ...

  def self.obtained_lock?(db, key)
    hash_key = Digest::MD5.hexdigest(key.to_s)
    result = db.fetch("SELECT GET_LOCK(#{db.literal(hash_key)}, 0) AS t#{SecureRandom.hex}")
    db_response(result) == 1
  end

  def self.release_lock(db, key)
    hash_key = Digest::MD5.hexdigest(key.to_s)
    db.run("SELECT RELEASE_LOCK(#{db.literal(hash_key)}) AS t#{SecureRandom.hex}")
  end

  def self.db_response(result)
    result.to_a[0].values[0]
  end

I have tried simply specifying (:read_only) for the synchronize which gets my unit tests to pass but then causes all kinds of locking failures in practice. I believe the appropriate solution is to force these specific SELECT statements to instead go back and use the writer db instance. In the use of db.run, I can pass a second parameter {server: :default} to get the job done. However, for db.fetch, there is no option to specify the server, and I cannot switch to using db.run in that case because I need to harness the return value. How might I go about ensuring that db.fetch uses the writer db instance?

Thank you for your time.

[jeremyevans]

You probably would be best off using the server_block extension for this: https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/server_block_rb.html

[rrennie6]

Thank you very much for the quick response. This is what my team and I have tried today so we are thinking along the same lines. This in particular:

  ...
  db.extension :server_block
  db.synchronize do
     # other great stuff here that uses locking methods
  end
  ...

And inside the method...

    result = db.with_server(:default) do
      db.fetch("SELECT GET_LOCK(#{db.literal(hash_key)}, 0) AS t#{SecureRandom.hex}")
    end

This, however, does not result in passing tests, which suggests to me most likely that the fetch is still using the :read_only server. I notice in the docs that the examples uses are with db[:some_table_name], but in this case it is a bit different as locking does not involve a specific table. Perhaps that could be the reason.

My follow up questions are: 1) Is this the correct way to specify the writer instance for the given scenario? 2) Is there a way to receive feedback from the db about whether it used :default or :read_only for the query it just made?

Thanks again!

[jeremyevans]

You can log connection info to log which connection was used for each query DB.log_connection_info = true.

fetch (and similar methods) should work inside a with_server block. Here's an example if you want to test with:

require "sequel"
require "logger"

DB = Sequel.sqlite(:servers=>{:read_only=>{}}, :logger=>Logger.new($stdout))
DB.extension :server_block
DB.log_connection_info = true

DB.with_server(:default) do
  DB.create_table(:d){Integer :i}
  DB[:d].insert(1)
end
DB.with_server(:read_only) do
  DB.create_table(:r){Integer :i}
  DB[:r].insert(2)
end

DB.with_server(:default) do
  p DB.tables
  p DB.fetch("SELECT * FROM d").get
end
DB.with_server(:read_only) do
  p DB.tables
  p DB.fetch("SELECT * FROM r").get
end

I think the issue you are running into is that Database#fetch returns a dataset, it doesn't actually issue a query unless a block is passed, so your code using fetch you put above is not actually running a query. Looks like the documentation for Database#fetch could be improved.

[rrennie6]

Thank you for this! We have solved our problem!

I am responding here to explain our findings/confirm with you and make the information available in case anyone else comes to look at this. Logging the connection info showed me three things. The first thing is expressed by these extraneous operations that appear in my log output:

I, [2023-03-29T14:44:40.474893 #21170]  INFO -- : (0.000278s) (conn: 59460) SELECT NULL
I, [2023-03-29T14:44:40.475737 #21170]  INFO -- : (0.000800s) (conn: 59460) SHOW FULL TABLES
I, [2023-03-29T14:44:40.476032 #21170]  INFO -- : (0.000172s) (conn: 61380) SELECT NULL
I, [2023-03-29T14:44:40.484723 #21170]  INFO -- : (0.008647s) (conn: 61380) TRUNCATE TABLE `ar_internal_metadata`
I, [2023-03-29T14:44:40.485132 #21170]  INFO -- : (0.000261s) (conn: 61380) SELECT NULL
I, [2023-03-29T14:44:40.492191 #21170]  INFO -- : (0.007014s) (conn: 61380) TRUNCATE TABLE `background_tasks`

The learning for me here is that the chosen connection for an operation that does not query the database is the reader (59460)! Then, other operations that do truncates against the table as part of db cleaning in our tests use the writer (61380).

The second learning is that without .get on a fetch, the with_server wrapper gets ignored and the db chooses the reader, for the reason that it is an operation that does not query the database:

    result = db.with_server(:default) do   <-----IGNORED!
      db.fetch("SELECT GET_LOCK(#{db.literal(hash_key)}, 0) AS t#{SecureRandom.hex}")
    end

    ...

I, [2023-03-29T15:01:21.150421 #32615]  INFO -- : (0.000232s) (conn: 59460) SELECT GET_LOCK('2cd299f447b99ad60072dda780ca8099', 0) AS t8e8d8dde3b4a948671502b18b40f0141
I, [2023-03-29T15:01:21.150749 #32615]  INFO -- : (0.000219s) (conn: 61380) SELECT RELEASE_LOCK('2cd299f447b99ad60072dda780ca8099') AS tf1a921bbdacc54370dd6db662997708b

The third learning is that with .get on a fetch, the with_server wrapper is accepted! Furthermore, this is interesting because it is inside of the db.synchronize where db.run is also run and does not require the wrapper to achieve the same success:

    result = db.with_server(:default) do   <-----ACCEPTED!
      db.fetch("SELECT GET_LOCK(#{db.literal(hash_key)}, 0) AS t#{SecureRandom.hex}").get
    end

    ...

    db.run("SELECT RELEASE_LOCK(#{db.literal(hash_key)}) AS t#{SecureRandom.hex}")  <----USES WRITER!

    ...

I, [2023-03-29T14:44:40.528766 #21170]  INFO -- : (0.000173s) (conn: 61380) SELECT GET_LOCK('52229cbdcb1387d94dadbd4d6a837b0f', 0) AS t2720a669867a453b41f655a839bce8fc
I, [2023-03-29T14:44:40.528992 #21170]  INFO -- : (0.000146s) (conn: 61380) SELECT RELEASE_LOCK('52229cbdcb1387d94dadbd4d6a837b0f') AS t9ffd8986418fc07af41781ae8f29e4e9