Why getting 'Combination of limit and lock is not supported' error using Oracle12 visitor

Oracle enhanced adapter 1.7 supports better top N query by this pull request Support Oracle 12c top-N query.

There are some feedbacks about this argument error if limit and lock are used at the same time.

ArgumentError:             'Combination of limit and lock is not supported.
            because generated SQL statements
            `SELECT FOR UPDATE and FETCH FIRST n ROWS` generates ORA-02014.`

This ArgumentError is intentionally added by this pull request Raise ArgumentError if limit and lock are used for Oracle12 visitor to explain to users why it is not supported.

In this article I have executed some tests how limit and lock works using Oracle and MySQL database.

Software Versions

  • Rails 5.0.0.1
  • Oracle enhanced adapter 1.7.8
  • MySQL Server version: 8.0.0-dmr MySQL Community Server (GPL)
  • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Setup

Create a sample rails application per database and create a Post model as follows:

rails generate scaffold post title:string
rails db:migrate
10.times do |i|
  Post.create! :title => "title_#{i+1}"
end

Example 1 : Post.first.lock!

Let's start with very basic one. Post.first.lock!

  • Oracle
irb(main):001:0> first = Post.first.lock!
... snip ...
ArgumentError:             'Combination of limit and lock is not supported.
            because generated SQL statements
            `SELECT FOR UPDATE and FETCH FIRST n ROWS` generates ORA-02014.`

It gets ArgumentError as expected.

  • MySQL
irb(main):001:0> first = Post.first.lock!
  Post Load (0.7ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1
  Post Load (1.3ms)  SELECT  `posts`.* FROM `posts` WHERE `posts`.`id` = 1 LIMIT 1 FOR UPDATE
=> #<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">

It just works.

  • Oracle with modified Arel

Created another Arel which rollbacks Raise ArgumentError if limit and lock are used for Oracle12 visitor to show which SQL statement will be executed and how it will get ORA-02014 error:

irb(main):001:0> first = Post.first.lock!
... snip ...
  Post Load (7.7ms)  SELECT  "POSTS".* FROM "POSTS" WHERE "POSTS"."ID" = :a1 FETCH FIRST :a2 ROWS ONLY FOR UPDATE  [["id", 10000], ["LIMIT", 1]]
ActiveRecord::StatementInvalid: OCIError: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.: SELECT  "POSTS".* FROM "POSTS"                                WHERE "POSTS"."ID" = :a1 FETCH FIRST :a2 ROWS ONLY FOR UPDATE

Although the record is exactly defined by WHERE "POSTS"."ID" = :a1 since "ID" is a primary key. Unfortunately, it gets ORA-2014 error it is due to FETCH FIRST :a2 ROWS ONLY FOR UPDATE.

Let's try another example using MySQL and see how each transaction works. It is easy to tet with MySQL which has default timeout configuration.

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.01 sec)

Example2 : Rows do not conflict before applying LIMIT

In this example let's test how each transaction works if both transactions does not conflict with rows selected before filtered by limit.

  • MySQL console #1
irb(main):001:0> ActiveRecord::Base.connection.begin_transaction
irb(main):002:0> first = Post.first.lock!

  Post Load (1.0ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1
  Post Load (1.6ms)  SELECT  `posts`.* FROM `posts` WHERE `posts`.`id` = 1 LIMIT 1 FOR UPDATE
=> #<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">
  • MySQL console #2
irb(main):001:0> ActiveRecord::Base.connection.begin_transaction
irb(main):002:0> last = Post.last.lock!

  Post Load (1.0ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` DESC LIMIT 1
  Post Load (0.7ms)  SELECT  `posts`.* FROM `posts` WHERE `posts`.`id` = 10 LIMIT 1 FOR UPDATE
=> #<Post id: 10, title: "title_10", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">

Both transactions are running in concurrently, the transaction at console #2 can proceed.

Example 3 : Rows conflicted before applying LIMIT

In this example let's test how each transaction works if both transactions does not conflict with rows selected after limit but conflicts before applying the limit.

  • MySQL console #1
irb(main):001:0> ActiveRecord::Base.connection.begin_transaction
irb(main):002:0> first =  Post.order(:id).limit(1)
  Post Load (1.4ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1
=> #<ActiveRecord::Relation [#<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):003:0> first =  Post.order(:id).limit(1).lock!
  Post Load (0.9ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1 FOR UPDATE
=> #<ActiveRecord::Relation [#<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):004:0>
  • MySQL console #2
irb(main):001:0> ActiveRecord::Base.connection.begin_transaction
irb(main):002:0> second =  Post.order(:id).limit(1).offset(1)
  Post Load (2.1ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1 OFFSET 1
=> #<ActiveRecord::Relation [#<Post id: 2, title: "title_2", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):003:0> second =  Post.order(:id).limit(1).offset(1).lock!
  Post Load (51025.3ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1 OFFSET 1 FOR UPDATE
ActiveRecord::StatementInvalid: Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1 OFFSET 1 FOR UPDATE

The transaction at console #2 gets Mysql2::Error: Lock wait timeout exceeded. I assume that row lock is done before applying the limit clause.

Example 4 : Rows do not conflict before applying LIMIT

This example is similar with the example 2, rows do not conflict before applying LIMIT by using order(:id) and order(id: :desc).

  • MySQL console #1
  Post Load (0.6ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1
=> #<ActiveRecord::Relation [#<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):003:0> first =  Post.order(:id).limit(1).lock!
  Post Load (1.2ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` ASC LIMIT 1 FOR UPDATE
=> #<ActiveRecord::Relation [#<Post id: 1, title: "title_1", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):004:0>
  • MySQL console #2
irb(main):001:0> ActiveRecord::Base.connection.begin_transaction
irb(main):002:0> another_second = Post.order(id: :desc).offset(8).limit(1)
  Post Load (2.1ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` DESC LIMIT 1 OFFSET 8
=> #<ActiveRecord::Relation [#<Post id: 2, title: "title_2", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):003:0> another_second = Post.order(id: :desc).offset(8).limit(1).lock!
  Post Load (7.1ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`id` DESC LIMIT 1 OFFSET 8 FOR UPDATE
=> #<ActiveRecord::Relation [#<Post id: 2, title: "title_2", created_at: "2016-11-29 18:39:44", updated_at: "2016-11-29 18:39:44">]>
irb(main):004:0>

Both transactions are running in concurrently, the transaction at console #2 can proceed.

Summary

Oracle 12c finally has implemented a great new feature supporting FETCH FIRST n ROWS ONLY equivalent with limit used by other databases. MySQL behavior is straightforward for me. Rows locked look defined by where or order by clause not limit. I do not have 12cR2 environment yet, wanted to test using 12cR2 and provide some feedback.