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
>
