When does Innodb Start Transaction ? The answer looks obvious – when you issue “BEGIN” command. This is however wrong answer from engine point of you. Run “SHOW INNODB STATUS” and you will see “not started” status in transaction list.

It is only when you read (or write) to INNODB table you can see transaction is started:

If you’re running in AUTOCOMMIT=0 mode the same logic will apply – setting this mode will NOT start transaction however reading or writing Innodb table will.

I would call it delayed transaction creation – having transaction open is costly, especially in repeatable-read mode as it will require innodb not to purge any old row versions from start of transaction until it is committed so delaying the start makes sense.

There is the side effect of this behavior though. When we say in repeatable read mode reads will see state of data at the time of transaction start we might refer to issuing BEGIN statement while it is actually first access of Innodb table. In most cases you would not care but if you really need all data be seen at the time of transaction start you can use “START TRANSACTION WITH CONSISTENT SNAPSHOT” command.

9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Stewart Smith

Also because there is no clear API in MySQL to say “start transaction”. It basically ends up being “if a transaction hasn’t already been started, start one!” – these calls are scattered all over ha_innodb.cc

I’m surprised that more things aren’t fundamentally broken because of this.

0
Peter Zaitsev

Stewart,

Yeah. I think sometimes quality (as in lack of bugs) is reached by design but sometimes even the thing which was not designed well may work because over years bugs have been polished out. These last ones however can be very dangerous thing to check. I think the big thin in MySQL is Optimizer – it works because it was used millions over years but it is pretty dangerous to change and changes result in unintended consequences all the time 🙂

0
Stewart Smith

I pretty much agree. I much prefer moving to code and design that is much more obviously correct though – means we can change and improve things without being petrified about breaking existing systems.

0
Sergei Golubchik

I’d like to point out that the current behavior is strictly speaking correct and absolutely compatible with the SQL standard (which is not surprising as we’ve studied t the corresponding part of the standard when implementing this).

See “4.35.4 Isolation levels of SQL-transactions” in the “part II – foundations”. Isolation levels are defined in terms of phenomena – “dirty read”, “non-repeatable read”, “phantom”. These phenomena occur when a transaction *reads* or *re-reads* something. How long it had waited between START TRANSACTION and the accessing the data for the first time is completely irrelevant, it cannot possibly cause any phenomena.

So, it does not matter if a transaction is started in InnoDB on START TRANSACTION or on the first table access – it cannot fundamentally cause any bugs.

0
David Shrewsbury

I recently squashed a bug in Drizzle where a transaction was not being started properly when it began with a non-table affecting statement, like SAVEPOINT (transaction was started for you, if it needed to, while locking tables). I didn’t look at how MySQL handles this case. Do you know what happens in this scenario in MySQL?

0
Peter Zaitsev

Sergei,

I think if you look at MySQL World few people know what SQL Standard is and even smaller fraction read it. I see in the practice people use their assumptions a lot more than that. And it is wrong assumptions which cause bug in many cases. I’m not reporting it as a bug just saying this is not what people understand well.

Also note “surprises” in MySQL can come in many cases because of mix of transaction engines as well as interaction with other layers. For example I have things worked when I converted one table to MyISAM because it does not need transactional behavior and that happen to be the first table touched in transaction with long query… hence transaction started a lot later than expected.

0
Sergei Golubchik

@David

SAVEPOINT is not a transaction-initiating SQL-statements. That is, if SAVEPOINT is used outside of a transaction, new transaction should not be started.

@Peter

Different people want and know different things. You understand that it’s impossible to do that will meet everybody’s expectations. So, when different people expect MySQL do different things in the same situation, we needed (and need – but in MariaDB 🙂 an arbitrator, an authoritative opinion that is accepted by most (if not all) users. SQL standard is such an arbitrator. In these cases we often implement a extension to the standard that allows users to get the desired but non-standard behavior. START TRANSACTION WITH CONSISTENT SNAPSHOT is this extension.

0
David Shrewsbury

@Sergei

SAVEPOINT may not officially be a transaction-initiating statement, but use of AUTOCOMMIT=0 complicates things. For example:

SET AUTOCOMMIT=0;
SAVEPOINT a;

ROLLBACK TO SAVEPOINT a;

COMMIT;

The ROLLBACK TO SAVEPOINT does work, so at least from a user’s perspective, the call to SAVEPOINT is part of the transaction that is started for you automatically. Is this correct behavior? I don’t know, but it is at least interesting to discuss. 🙂

0
Sergei Golubchik

ROLLBACK TO SAVEPOINT a; works, but it does not mean that there is any transaction involved. As you did not do anything between SAVEPOINT and ROLLBACK TO SAVEPOINT, no transaction is started and no work is rolled back.

Now, suppose you would have done something that starts a transaction (say, INSERT or SELECT).

Generally, the standard says that on ROLLBACK TO SAVEPOINT “the SQL-transaction is restored to its state as it was immediately following the execution of the “.

But as the transaction simply did not exist back than, your ROLLBACK TO SAVEPOINT will rollback the transaction, and no transaction will exist right after ROLLBACK TO SAVEPOINT.

0