P.S. I've been half thinking about running another "DB Is Your Friend"
course in Melbourne. Email me if you would be interested.
http://www.dbisyourfriend.com/
Xavier
On 2/02/11 10:31 AM, Xavier Shay 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.
