Bug #4104 BEGIN does not start a transaction

• Submitted: 11 Jun 2004 11:53
• Modified: 11 Jun 2004 13:20
• Reporter: Sergei Golubchik
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23
• CPU Architecture: Any

[11 Jun 2004 11:53] Sergei Golubchik

Description:
BEGIN does not start a transaction

How to repeat:
create table t1 (a int) type=innodb;
insert t1 values (1),(2);
set autocommit=0;
begin;
connect (user1,localhost,root,,);
connection user1;
delete from t1;
connection default;
select * from t1;
drop table t1;

[11 Jun 2004 13:20] Alexander Keremidarski

Begin does start a transaction, but does nothing else and this is how it should be.

An explanation about this spartan test case.

there are 2 transactions. First one is in non-autocommit mode. second one is in autocommit mode.

Transaction isolation level is REPEATABLE_READ meaning that transaction must get the same data as resulf of each read operation for it's whole duration.

When first transaction iniiates with 
BEGIN

it enters REPEATABLE_READ state.

At the next moment the Second transaction does Autocommited DELETE

Note that this happens *before* the First transaction does *any* reads.

At the time when first transaction does first read the data in the table is deleted so first transaction gets consistant read of empty data.

From that point of time up to the end of first transaction it gets repeatable read of empty data regardless of what other transactions do.

Quoting MySQL Manual chapter 

Consistent Non-Locking Read

If you are running with the default REPEATABLE READ isolation level, then all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.