Transaction behaviour inside another transaction from another connection

[koenhandekyn]

we had issues using the default setup for a use case where we want to execute some code on a read only connection. a short example is below. evidently this would not work if the normal connection pool is used because the cache would not be able to write, which indeed is what we where observring.

first remark is that this initially surprised us, and might make sense to add some documentation and/or warnings around the use of the standard connection pool.

User.transaction { User.connection.execute("SET TRANSACTION READ ONLY"); puts Rails.cache.fetch("2") { "hoho" }; raise ActiveRecord::Rollback; }

the solution we are implementing tries to fix this without introducing a separate database as this would be to costly (we are running on heroku still).

the gist is to configure a cache db that points to the same database URL, something like below

default: &default
  adapter: postgresql
  encoding: unicode
  url: <%= ENV["DATABASE_URL"] %>

production:
  primary:
    <<: *default
  cache:
    <<: *default

and solid cache yml

production: &production
  database: cache
  store_options:
    <<: *default_store_options
    max_entries: <%= 1.gigabytes %>

the good news is it doesn't crash any more. the system

User.transaction { puts Rails.cache.fetch("2") { "hoho" }; raise ActiveRecord::Rollback; }

we actually see the upsert happening:

User.transaction { puts Rails.cache.fetch("2") { "hoho" }; raise ActiveRecord::Rollback; }
D, [2024-04-04T11:53:15.866315 #2] DEBUG -- :   TRANSACTION (0.5ms)  BEGIN
D, [2024-04-04T11:53:15.867177 #2] DEBUG -- :   SolidCache::Entry Load (0.5ms)  SELECT "solid_cache_entries"."key", "solid_cache_entries"."value" FROM "solid_cache_entries" WHERE "solid_cache_entries"."key_hash" = $1  [[nil, -3138060911502289170]]
D, [2024-04-04T11:53:15.868425 #2] DEBUG -- :   SolidCache::Entry Upsert (0.7ms)  INSERT INTO "solid_cache_entries" ("key","value","key_hash","byte_size","created_at") VALUES ('\x32', '\x0004085b06492209686f686f063a064554', -3138060911502289170, 158, CURRENT_TIMESTAMP) ON CONFLICT ("key_hash") DO UPDATE SET "key"=excluded."key","value"=excluded."value","byte_size"=excluded."byte_size" RETURNING "id"
hoho
D, [2024-04-04T11:53:15.869289 #2] DEBUG -- :   TRANSACTION (0.5ms)  ROLLBACK
=> nil

HOWEVER to our surprise in the first approach (read only connection) and the second approach (rollback), in both cases, the cache entry is 'lost' after the transaction on the main db is finished/rolledback.

so somehow, the 'transaction' on the primary database seems to be impacting the transaction on the cache database?

in our case, it is actually the same database 'admitted', but there is a separate database pool (which seems to be correct) and hence the one transaction should not influence the cache writing ?

[djmb]

Hi @koenhandekyn - I've tried to reproduce this and I'm not seeing the same behaviour. As long as the cache connection and the connection used by the User class are different, then as you say a rollback on the User connection shouldn't affect the cache connection.

You could check by outputting the object ids of the connections - User.connection.object_id and SolidCache::Entry.connection.object_id. They should be different.

If they are different then it might be worth checking they are different connections at the server as well - puts User.connection.select_all("select pg_backend_pid()") and puts SolidCache::Entry.connection.select_all("select pg_backend_pid()").

[koenhandekyn]

interesting. in the mean time we did some updates, and somehow it impacted because i can't replicate the issue. it's nicely handled now. both variants.

unclear where/how it got fixed but it is :)