How does getting mysql's last insert ID work with transactions? + transaction questions

Question

A two part question:

1. In my CodeIgniter script, I'm starting a transaction, then inserting a row, setting the insert_id() to a php variable, inserting more rows into another table using the new ID as a foreign key, and then I commit everything.

   So my question is: if everything does not commit before ending the transaction, how is mysql able to return the last insert ID, if nothing was even inserted? My script works (almost) perfectly, with the new ID being used in subsequent queries.

   (I say "almost" because, using the PDO mysql driver, sometimes the first insert that is supposed to return the insert_id() is duplicated--it get's inserted twice. Any idea why that would be? Is that related to getting the last ID? It never happens if using the mysqli or mysql driver.)

2. I first wrote the script without transactions, so I have code that checks for mysql errors along the way, such as:

   if(!$this->db->insert($table, $data)) {     //log message here } 

   How does this affect the mysql process once I wrapped all my mysql code in a transaction? It's not causing any visible errors (hopefully unrelated to the problem stated above), but should it be removed?

Thank you.

Answer 1

To answer your first question...

When using transactions, your queries are executed normally as far as your connection is concerned. You can choose to commit, saving those changes, or rollback, reverting all of the changes. Consider the following pseudo-code:

insert into number(Random_number) values (rand());  select Random_number from number where Number_id=Last_insert_id(); 

//php

if($num < 1)    $this->db->query('rollback;'); // This number is too depressing. else    $this->db->query('commit;'); // This number is just right. 

The random number that was generated can be read prior to commit to ensure that it is suitable before saving it for everyone to see (e.g. commit and unlock the row).

If the PDO driver is not working, consider using the mysqli driver. If that is not an option, you can always use the query 'select last_insert_id() as id;' rather than the $this->db->insert_id() function.

To answer your second question, if you are inserting or updating data that other models will be updating or reading, be sure to use transactions. For example, if a column 'Number_remaining' is set to 1 the following problem can occur.

Person A reads 1 Person B reads 1 Person A wins $1000! Person A updates 1 to be 0 Person B wins $1000! Person B updates 0 to be 0 

Using transactions in the same situation would yield this result:

Person A starts transaction
Person A reads '1' from Number_remaining
(The row is now locked if select for update is used)
Person B attempts to read Number_remaining - forced to wait
Person A wins $1000
Person A updates 1 to be 0
Person A commits
Person B reads 0
Person B does not win $1000
Person B cries

You may want to read up on transaction isolation levels as well.

Be careful of deadlock, which can occur in this case:

Person A reads row 1 (select ... for update)
Person B reads row 2 (select ... for update)
Person A attempts to read row 2, forced to wait
Person B attempts to read row 1, forced to wait
Person A reaches innodb_lock_wait_timeout (default 50sec) and is disconnected
Person B reads row 1 and continues normally

At the end, since Person B has probably reached PHP's max_execution_time, the current query will finish executing independently of PHP, but no further queries will be received. If this was a transaction with autocommit=0, the query will automatically rollback when the connection to your PHP server is severed.