Recently we've taken over support for a new, externally developed application that sends text messages to mobiles in response to shortcode requests for more information about cars, houses etc for sale.
Under development and testing everything was fine. However, when it went live the registration process occasionally failed. After a brief examination I noticed this method for inserting records in the database class:
$newId = $this->conn->nextID($tableName . "_id");
I suspected that this did something a little unusual.
It had been written in Php5 backed by MySQL and was using the Pear DB package. It rapidly became clear the book "Professional PHP5" had been used a little too religiously. The book suggests this facade method for generating and getting the next ID for a new record that hides the operational differences between MySQL, PostreSQL, Oracle and other RDBMS.
Under the hood, Pear DB creates an id tracking table of the same name suffixed with "_id_seq" for each table storing data with unique ids. It gets the value from this tracking table, adds one to the value, stores it back, and returns this value as the next id to be used in the insert statement for the new record.
So I heard websites and the internet was a concurrent multiuser enviroment? Can anyone guess what might be the problem generating unique ids this way?
MySQL gives the us the auto_increment feature. It also gives us the mysql_insert_id method. This allows us to get the last auto generated id if we need it:
$newId = mysql_insert_id($this->conn->connection);
"But surely, when inserting new records in a concurrent multiuser environment, we need actions like this to be as atomic as possible?" I hear you ask. The force (read SQL) is strong with this one. Correct.
MySQL allows us to provide a value for an auto_increment field, or we can supply 'null' and allow MySQL to do it for us.
So to fix this intermittent registration failure, we replace the line:
$newId = $this->conn->nextID($tableName . "_id");
with:
And LO, the bug is gone.
Needless to say Pear DB has been replaced by something else. And interestingly if you read on a little in the book you'll see "we strongly recommend... ideally, allow your RDBMS to [generate the id]"
So what? Read books carefully. Know the technology you're using. Concurrency requires atomic superness.