create table t1 (i int) engine=InnoDB;

set autocommit = 1;

# Insert '1' with autocommit enabled

insert into t1 values (1);

connect  con1,localhost,root,,;

set session transaction isolation level read committed;

# Make sure the value '1' is visible right away

select * from t1;

i

1

connection default;

# Disable autocommit for inserting the value '2'

set statement autocommit=0 for insert into t1 values (2);

connection con1;

# The value '2' should not be visible

select * from t1;

i

1

2

# ... but it is

connection default;

# Disable autocommit in general

set autocommit = 0;

# Insert '3' with autocommit disabled

insert into t1 values (3);

connection con1;

# Make sure the value '3' is not visible 

select * from t1;

i

1

2

connection default;

rollback;

# Enable autocommit for inserting the value '4'

set statement autocommit=1 for insert into t1 values (4);

connection con1;

# The value '4' should be visible

select * from t1;

i

1

2

# ... but it is not.

disconnect con1;

[Warning] MySQL is closing a connection that has an active InnoDB transaction.  1 row modifications will roll back.

--enable_connect_log

--source include/have_innodb.inc

create table t1 (i int) engine=InnoDB;

set autocommit = 1;

--echo # Insert '1' with autocommit enabled

insert into t1 values (1);

--connect (con1,localhost,root,,)

set session transaction isolation level read committed;

--echo # Make sure the value '1' is visible right away

select * from t1;

--connection default

--echo # Disable autocommit for inserting the value '2'

set statement autocommit=0 for insert into t1 values (2);

--connection con1

--echo # The value '2' should not be visible

select * from t1;

--echo # ... but it is

--connection default

--echo # Disable autocommit in general

set autocommit = 0;

--echo # Insert '3' with autocommit disabled

insert into t1 values (3);

--connection con1

--echo # Make sure the value '3' is not visible 

select * from t1;

--connection default

rollback;

--echo # Enable autocommit for inserting the value '4'

set statement autocommit=1 for insert into t1 values (4);

--connection con1

--echo # The value '4' should be visible

select * from t1;

--echo # ... but it is not.

# Cleanup

--disconnect con1