I believe you - I'm just a but surprised. I guess I had a singular view
of how a session should work based on Oracle. I would have expected that
until you execute SQL that requires a commit or a rollback, you wouldn't
be in a transaction. Unfortunately, if you have connections that are
read and write, and one connection ends up being used for SELECTs only
(just bad luck) , it's going to have an out-date view of the database.
To me, a transaction is something you commit or rollback. You can't
commit or rollback a SELECT unless you've done a locking-read. I guess
Oracle is just smarter about it, only starting a transaction behind the
scenes if you've actually done something that warrants a transaction.
David
Michael Stassen wrote:
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]
