If you are NOT in autocommit mode, your connection (or the server, it doesn't matter which) starts a transaction *when you issue your first command*. Every command you issue on that connection is in that initial transaction until you EXPLICITLY commit or rollback (or do something else that commits or rolls-back your transactions like ALTER TABLE) . At that point a new transaction is automatically started when you issue your next command. If I remember correctly, closing a connection with a pending transaction defaults to a ROLLBACK. That way if a transaction is left incomplete due to communications failure, you maintain a consistent database.
If autocommit is enabled (SET autocommit=1) then each command executes within it's own mini-transaction (one little, tight transaction wrapped around each statement). Each SELECT can see what every other INSERT, UPDATE, or DELETE has done (assuming their transactions are committed) because it is not already inside a pending transaction. This is the default mode for user interaction for nearly every database product I have used. With autocommit active, you are required to explicitly issue a START TRANSACTION if you want a transaction that includes several commands. Are you sure that's not how Oracle operates, too? I ask because MS SQL acts the same as MySQL when it comes to autocommits Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths <[EMAIL PROTECTED]> wrote on 09/01/2005 12:33:55 PM: > 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] >
