Yah, I tested in SQL*Plus - one window could see inserts, updates and
deletes that had been committed in another window (in which a commit or
rollback had not been issued). I ran the test again - delete data from a
table in one window and commit the change, and a select in the other
window displays the results.
Note that SQL*Plus by default does not auto-commit, but the key elements
of the test are the same. Data committed in one session is visible in
another session once committed.
In Oracle/SQL*Plus, data committed in session A will show up in Session
B if Session B has an open transaction. Here's the example (using
session A and B).
Session A:
insert into temp_table (col1) values ('a');
Session B:
insert into temp_table (col1) values ('b');
At this point, neither is committed, and neither session can see what's
the other has done (the left hand doesn't know what the right is doing,
so to speak).
Session A:
commit;
Session B:
SQL> select * from temp_table;
C
-
b
a
Session B has an open transaction, yet can see the data that was
committed in another transaction. It's view of the data is, "Show me all
the data that has been committed to the database at the point where I
started my query, plus all changes that I've made yet not committed or
rolled back".
Oracle runs in READ COMMITTED (the above), while INNODB runs in
REPEATABLE READ. Big difference. And I (stupidly) assumed they ran as
the same transaction isolation level.
Learn something new every day.
David
[EMAIL PROTECTED] wrote:
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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
