0

I have the following piece of code in Rails 3.2 + mySQL application:

ActiveRecord::Base.connection.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
ActiveRecord::Base.transaction do
              @book = ActiveRecord::Base.connection.execute("select * from books limit 1")
end

As far as I understand the first statement will result in the next ONE transaction within same session to be in "READ UNCOMMITTED" isolation and then isolation will return to default.

My questions are: Can I be sure that the transaction block will always be executed in the same session? Also, can I be sure that no other transaction will take place in the same session between the first and the second statement?

I tried to Google for this topic, but as I am new to Rails I couldn't find any explanation that would make this clear to me. Any help would be appreciated!

Thank you!

Tofig Hasanov
  • 3,303
  • 10
  • 51
  • 81

1 Answers1

4

I think that all your questions can be answered with a "YES". Even in Rails 3.2, the connections to the database are managed by the connection pool. This pool ensures that each thread will have its own dedicated connection to the DB. The pool assigns connections to threads based on their Thread IDs which are unique for each thread. Read the docs for more info.

So I think it should not be possible for two threads to share a connection and thus a MySQL session at the same time. Also, it should be guaranteed that the transaction will always be called using the same connection (i.e. in the same session) as the isolation level setting code.

By the way, if you used Rails 4 or higher, you could achieve the same behavior using the transaction method only. Unfortunately, setting isolation levels is not supported this way in Rails 3. So the following example actually won't work in your particular scenario:

Book.transaction(:isolation => :read_uncommitted) do
  @book = Book.first
end
# => SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
#    BEGIN
#      SELECT  `books`.* FROM `books`  ORDER BY `books`.`id` ASC LIMIT 1
#    COMMIT
Matouš Borák
  • 15,606
  • 1
  • 42
  • 53
  • Thank you. This helps! Yes, it seems that Rails 4 has a better solution to this, but unfortunately I cannot migrate to Rails 4 with the current project now. – Tofig Hasanov Apr 19 '16 at 13:57
  • A follow up question: Is it necessary to wrap the statement in transaction block like I did in the code? Will the isolation still work properly if I just execute sql statement without transaction block? – Tofig Hasanov Apr 19 '16 at 15:12
  • 1
    Yes, as explained [here](http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html), the isolation setting is valid for the next transaction block. Even setting a session-level isolation is valid for all subsequent transaction blocks only. – Matouš Borák Apr 19 '16 at 15:38