David Griffiths wrote:
"No, with the default transaction isolation level, REPEATABLE READ,
that's how it is supposed to work. You've started a transaction in
Window B, so Window B is immune to changes made in Window A until you
finish the transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html";
I haven't explicitly started any transactions in Window B - it's
select-only (with autocommit set to 0). Are you saying that even though
transactions have happend and been committed in Window A, I won't be able
to see those transactions in Window B?
The key word is "explicitly". You have implicitly started a transaction
with your first SELECT, precisely because you turned AUTOCOMMIT off. That
transaction continues until you COMMIT or ROLLBACK (or perform an action
that implicitly commits
<http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html>).
That's the point of setting AUTOCOMMIT to off. If you only want to start
transactions explicitly (with START TRANSACTION or BEGIN), then you need to
leave AUTOCOMMIT on. See the manual for details
<http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html>.
The relevant part of the documentation in the link you sent is,
"The query see[s] the changes made by exactly those transactions that
committed before that point of time, and no changes made by later or
uncommitted transactions. The exception to this rule is that the query
sees the changes made by the transaction itself that issues the query." >
In otherwords, if you start a query (and it's a long running query), you
won't see the results of any data committed by another session during the
running of that query. Fine. That's expected.
But if I am doing only queries (no transactions) via a connection, and no
query is running when I commit data in another session, then the
query-window should see the results of those changes.
From the AUTOCOMMIT manual page cited above, "In InnoDB, all user activity
occurs inside a transaction."
I suspect that the mysql client is implicitly starting a transaction when
you do a "set autocommit=0". Thus, any changes made by any other sessions
won't be visible till you do a commit or rollback. Each time a commit or
rollback is issued in the non-auto-commit session, you can see data
changed by other sessions.
With AUTOCOMMIT off, the transaction starts, in your case, with your first
SELECT.
Regardless, this is not a repeatable-read issue. I think it's a mysql
client issue, and the fact that the client is creating transactions for
you in the background.
It's not the client. That's how InnoDB works.
This is not how the Oracle client works - you are always in
non-auto-commit mode (and I'd love to figure out how to set that -
autocommit is so dangerous), and until you actually start a transaction
with an update, insert, delete or select-for-update, no transaction is
started, and you can see the changes made by other sessions once they've
been committed (I tested SQL*Plus on Oracle 8i to make sure). >
I'll make no comments on how Oracle works, but what you seem to be
describing is effectively what happens with AUTOCOMMIT on in MySQL. In
general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for
example) to behave in exactly the same way will usually get you in trouble.
David
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
