Re: autcommit vs. manual commit behavior

[Bill Karwin]

Sounds like autocommit is disabled in session 2 as well, and your transaction isolation level is REPEATABLE READ or SERIALIZABLE. This means that the transaction in session 2 sees only data that was committed at the moment its transaction began.

Try this scenario to illustrate it:
Session 1: SET AUTOCOMMIT = 0. Make a change but do not commit yet.
Session 2: SET AUTOCOMMIT = 0. START TRANSACTION.
Session 1: COMMIT.
Session 2: SELECT to try to see the change. You should not see it, because it wasn't committed when the transaction began.
Session 2: START TRANSACTION again. Now SELECT. Now you should see the change from session 1 (it is not necessary to quit and reconnect, just start a new transaction).

Try this different scenario:
Session 1: SET AUTOCOMMIT = 0. Make a change but do not commit yet.
Session 2: SET AUTOCOMMIT = 0. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED. START TRANSACTION.
Session 1: COMMIT.
Session 2: SELECT to try to see the change. You should see it this time, because session 2's transaction is set to be able to view data committed by other transactions as soon as they occur.

This is intended behavior, and should function the same in all versions of MySQL.

See http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html for more details on transaction isolation levels.

Regards,
Bill K.