Isolation level leak: `:repeatable_read` persists after transaction using activerecord-jdbcpostgresql-adapter

[nbekirov]

Description

When using ActiveRecord::Base.transaction(isolation: :repeatable_read) with the activerecord-jdbcpostgresql-adapter, the adapter emits SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ, which changes the isolation level for the entire session, not just the transaction.

This causes the isolation level to persist across subsequent transactions and statements, which is unexpected.

# Before transaction
jruby-9.4.9.0 :001 > ActiveRecord::Base.connection.select_value('SHOW TRANSACTION ISOLATION LEVEL')
2025-04-08 12:11:42.782928 D [51837:main (irb):1] (7.859ms) ActiveRecord -- {:sql=>"SHOW TRANSACTION ISOLATION LEVEL", :allocations=>0, :cached=>nil}
 => "read committed"
# PG log:
#     2025-04-08 09:11:42.767 UTC [299] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL

# During transaction
jruby-9.4.9.0 :002 > ActiveRecord::Base.transaction(isolation: :repeatable_read) { ActiveRecord::Base.connection.select_value('SHOW TRANSACTION ISOLATION LEVEL') }
2025-04-08 12:12:45.023830 D [51837:main (irb):2] (14.9ms) ActiveRecord -- TRANSACTION -- {:sql=>"BEGIN ISOLATED - repeatable_read", :allocations=>0, :cached=>nil}
2025-04-08 12:12:45.041352 D [51837:main (irb):2] (9.210ms) ActiveRecord -- {:sql=>"SHOW TRANSACTION ISOLATION LEVEL", :allocations=>0, :cached=>nil}
2025-04-08 12:12:45.046712 D [51837:main (irb):2] (1.975ms) ActiveRecord -- TRANSACTION -- {:sql=>"COMMIT", :allocations=>0, :cached=>nil}
 => "repeatable read"
# PG log:
#     2025-04-08 09:12:45.018 UTC [299] LOG:  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ
#     2025-04-08 09:12:45.037 UTC [299] LOG:  execute <unnamed>: BEGIN
#     2025-04-08 09:12:45.037 UTC [299] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
#     2025-04-08 09:12:45.043 UTC [299] LOG:  execute <unnamed>: COMMIT

# After transaction
jruby-9.4.9.0 :006 > ActiveRecord::Base.connection.select_value('SHOW TRANSACTION ISOLATION LEVEL')
2025-04-08 12:14:24.319307 D [51837:main (irb):6] (6.180ms) ActiveRecord -- {:sql=>"SHOW TRANSACTION ISOLATION LEVEL", :allocations=>0, :cached=>nil}
 => "repeatable read"
# PG log
#     2025-04-08 09:14:24.314 UTC [299] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL

Actual

The isolation level is changed at the session level using SET SESSION CHARACTERISTICS, and remains repeatable read even after the transaction has completed. This affects all subsequent transactions and statements using the same connection. Unexpected could not serialize access due to concurrent update errors are logged.

Expected

After executing a transaction with isolation: :repeatable_read, the session's isolation level should return to its previous state (typically read committed). The isolation level should only apply to the scope of the transaction block.

Environment

• ruby '3.1.4', engine: 'jruby', engine_version: '9.4.9.0'
• gem 'activerecord-jdbcpostgresql-adapter', '~> 70.2'
• gem 'rails', '~> 7.0.8.7'