How do SQL transactions return immediate results?

Question

I am using transactions to do bulk insert/updates. This is my little test loop:

$now = date('Y-m-d H:i:s');
for ($i=0; $i<60; $i++) {
 $db->insert($cfg['ps_manufacturer'], array(
 'reference' => 'MF'.$i,
 'name' => 'MF NAME'.$i,
 'date_add' => $now,
 'date_upd' => $now,
 'active' => true,
 ));
 echo $db->lastInsertId().'<br>';
}

Now the script above is obviously very slow because the query is in a loop. By using transactions the execution time drops from 2s to 0.06s:

$db->beginTransaction();
$now = date('Y-m-d H:i:s');
for ($i=0; $i<60; $i++) {
 $db->insert($cfg['ps_manufacturer'], array(
 'reference' => 'MF'.$i,
 'name' => 'MF NAME'.$i,
 'date_add' => $now,
 'date_upd' => $now,
 'active' => true,
 ));
 echo $db->lastInsertId().'<br>';
}
$db->commit();

Now what confuses me is how does the second sample return an ID of every inserted row immediatelly? From my understanding the transaction starts and queues all procedures, until the queue is "released" by using commit().

Now obviously the test showed that I'm wrong, could someone explain how does this work? It does essentially the same, but works a whole lot faster. Does using transactions start some kind of "procedural session" which is optimized for continued querying? This code is a bit counter-intuitive to me, almost looks like async programming :)

Answer 1

There are two different questions here, one about the performance behaviour, and one about the auto generated IDs.

From my understanding the transaction starts and queues all procedures, until the queue is "released" by using commit()

That is only partially correct. When the transaction starts, the database makes something like a "private snapshot" and execute your inserts immediately there. All other users of the database won't see your changes until you call commit. So the database can do this typically in main memory. It has to persist your inserts to a hard disk only once, as well as it has to ensure consistency with the concurrent operations of the other users only once at the end. That is why the operation works faster than making a commit after every "insert" (which is what happens automatically in your first example).

And to your second question:

how does the second sample return an ID of every inserted row immediately?

Auto increment columns are incremented outside the scope of your transaction. See this former SO post for an explanation.