Oracle12 visitor - Combination of limit and lock is not supported

Oracle12 Arel visitor does not handle sub-queries with LIMIT properly and this makes locking a row impossible. While it works in normal queries, in sub-queries that doesn't seem to be the case.

Steps to reproduce

# just a minimal project `rails new --database=oracle
git clone https://github.com/akostadinov/rails-ora-02014.git
cd rails-ora-02014
vi config/database.yml # set your DB connection details
rails c
> a = Article.create!(title: "test", body: "test body")
> a.with_lock {}

Expected behavior

  Article Load (1.8ms)  SELECT "ARTICLES".* FROM "ARTICLES" WHERE "ARTICLES"."ID" = :a1 AND ROWNUM <= :a2  FOR UPDATE  [["id", 23], ["LIMIT", 1]]
=> nil

Actual behavior

Traceback (most recent call last):
/home/avalon/.asdf/installs/ruby/2.7.3/lib/ruby/gems/2.7.0/gems/activerecord-oracle_enhanced-adapter-7.0.0/lib/arel/visitors/oracle12.rb:10:in `visit_Arel_Nodes_SelectStatement': Combination of limit and lock is not supported. Because generated SQL statements (ArgumentError)
`SELECT FOR UPDATE and FETCH FIRST n ROWS` generates ORA-02014.

Possible workaround - use Arel::Visitors::Oracle

Arel::Visitors::Oracle works fine though because it always translates LIMIT statements to ROWNUM.

The example above properly works, if we add to application.rb:

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
    # Use old visitor for Oracle 12c database
    self.use_old_oracle_visitor = true
  end
end

System configuration

Oracle Enhanced Adapter version 7.0.0

Rails version: Rails 7.0.1

Ruby version: ruby 2.7.3p183 (2021-04-05 revision 6847ee089d) [x86_64-linux]

Oracle version: 19.3.0

Seems to be related also to #1070

@yahonda Can we get a little clarification on this lock subject. Most of the discussions on it are pretty old, and I can't seem to come up with a definitive approach to get row locking to work.

Most of my errors are ArgumentError (Combination of limit and lock is not supported. Because generated SQL statements) SELECT FOR UPDATE and FETCH FIRST n ROWS generates ORA-02014.

Doesn't matter, if I find_by_id, where(id: ) etc etc. Is with_lock just not supported? A little guidance would help. I can volunteer to put in a patch if I know what's the latest state on this and what's the history behind it.

AJ

@yahonda

I see a possible solution from 2016. https://github.com/rsim/oracle-enhanced/issues/1066

Is this still the current approach?

AJ

One possible approach is using a query without limits. Assuming you're using where on a unique value it shouldn't really make a difference in amount of objects returned (and if you're not doing it on a unique value then the setting a limit of 1 is not going to reliably work anyway).

a = Article.create!(title: "test", body: "test body")
a = Article.lock(true).where(id: a.id).to_a[0]
...
a.save! # Make sure to unlock the row (if you are running in a transaction then ending the transaction should work too I think)