Yeah, you're right actually. Which makes sense, I didn't really look into it much because I've generally used more isolated isolation levels.
On Wed, Feb 2, 2011 at 10:31, Xavier Shay <[email protected]> wrote: > > > On 2/02/11 10:16 AM, Simon Russell wrote: >> >> I'm reasonably sure that depending on your MySQL transaction isolation >> level, if you run the same query twice inside a transaction, it'll >> return the same results -- even if you've inserted data that should >> appear in the second query. >> >> Look at repeatable read here: >> http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html >> >> (unless I'm just misinterpreting things) > > You are not quite correct - you will always see data that you have inserted, > regardless of your isolation level [1]. > > Isolation levels are only relevant when talking about isolation between > different transactions. > > Also repeatable read does allow you to see data from other transactions in > some circumstances (phantom reads), for full protection you need to bump up > to SERIALIZABLE (side note: this is why acts_as_list is broken by default > with mysql + rails). Please see the blog post I linked up in a previous > reply for more detail. > > I encourage everyone to try out these different scenarios using minimal test > cases, it really helps you get your head around it. > > Cheers, > Xavier > > [1] > mysql> SELECT @@tx_isolation; > +-----------------+ > | @@tx_isolation | > +-----------------+ > | REPEATABLE-READ | > +-----------------+ > 1 row in set (0.00 sec) > > mysql> BEGIN; > Query OK, 0 rows affected (0.00 sec) > > mysql> SELECT * FROM users; > +----+ > | id | > +----+ > | 1 | > +----+ > 1 row in set (0.00 sec) > > mysql> INSERT INTO users VALUES (2); > Query OK, 1 row affected (0.00 sec) > > mysql> SELECT * FROM users; > +----+ > | id | > +----+ > | 1 | > | 2 | > +----+ > 2 rows in set (0.00 sec) > > mysql> ROLLBACK; > Query OK, 0 rows affected, 1 warning (0.00 sec) > > >> >> Simon. >> >> On Tue, Feb 1, 2011 at 18:54, Chris Mayan<[email protected]> wrote: >>> >>> :) :) >>> >>>> So in rough pseudo code, >>>> >>>> In real code: >>>> https://gist.github.com/8e2403de7ad332c3c20b [1] >>> >>> Thanks Xavier , I'll have a play and see if the differences might be >>> something else altogether. >>> >>> >>>> >>>> Running the above script says you can see your own inserts, as long as >>>> you >>>> explicitly reload your association (since it gets cached). There are >>>> circumstances where you won't be able to see them across transactions, >>>> but >>>> that's perhaps out of scope for this question. >>>> >>>> This also gives you a minimal test case to play around with to see >>>> exactly >>>> when, what and where is executed. >>>> >>> >>> >>> Cheers >>> Chris >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Ruby or Rails Oceania" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group at >>> http://groups.google.com/group/rails-oceania?hl=en. >>> >> > > -- > You received this message because you are subscribed to the Google Groups > "Ruby or Rails Oceania" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/rails-oceania?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
