Rails In Transaction, why is entire database locked instead of single record?

Question

I am experiencing this. In transaction, the database itself is blocked instead of single record.

Here is the snippet of my codes.

code 1)

lesson = Lesson.last
ActiveRecord::Base.transaction do
lesson.start_time = Time.now
  lesson.save
  sleep(10.seconds)
  raise "let's roll back lesson!!"
end

code 2)

lesson = Lesson.first
lesson.start_time = Time.now
lesson.save

I executed code1 in the console1 and code2 in the console2 which was open in the another terminal. Obviously two consoles share the database in local.

Code 2 threw error like

ActiveRecord::StatementInvalid: SQLite3::BusyException: database is locked: UPDATE "lessons" SET "start_time" = ?, "updated_at" = ? WHERE "lessons"."id" = ?

I was assuming the code 1 will prevent lesson(Lesson.last) from being modified because of the exception raised. It works fine but what works against my expectation is code 2 doesn't update the lesson(Lesson.first).

If this is the nature (blocking the entire database, instead of single record), then complex business logic will end up being influenced due to the halt of the transaction.

Kind of curious! What will be good strategy to allow code 2 to work nicely!

I think this limitation is on the DB connection and not DB itself — Raj, Mar 14 '19 at 03:15
Yes, I agree to @LeninRajRajasekaran, you could see relating to lock in https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html . 'This is because transactions are per-database connection, not per-model.' — ogelacinyc, Mar 14 '19 at 03:45
Thanks guys, when I increased the database connection pool number in local (sqlite3), it actually didn't help. However when I test in the production mode with mysql2, everything works as expected. ! — Sungpah Lee, Mar 14 '19 at 07:06

score 0 · Accepted Answer · answered Mar 14 '19 at 06:36

Elaborating what we wrote in the comments:

ActiveRecord Transactions are per-database connection, not per-model.

https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

In development environment, Sqlite3 cannot support concurrent modification to the database.

Rails In Transaction, why is entire database locked instead of single record?

1 Answers1