| [reply] |
If all tables are using the InnoDB engine, then you can certainly use transactions. The way to do this in DBD::mysql is to turn AutoCommit off, then issue your statements. You can optionally send an explicit "BEGIN WORK" statement, but you don't have to. (I do, but that's cause I'm paranoid.) When you're ready to commit, issue a $dbh->commit() to commit or $dbh->rollback(), if you're rolling back. (Those functions are documented in the DBI documentation.)
If any of your tables are non-InnoDB, then you would have to issue a LOCK TABLES statement, pairing that with the appropriate UNLOCK TABLES at the appropriate time. The big issue with this is that you don't have any method to rollback if you run into a problem. The other problem is that you have a lock on those tables, which means all other attempts to modify the tables (and possibly even read, depending on the lock) are blocked until such time as you release the lock. Because of this, you can enter a race condition where process 1 locks tables A and B and process 2 locks tables B and A. If they run at the same time, process 1 locks table A, then tries to get a lock on table B. But, process 2 already locked table B and is trying to get a lock on table A. This is called deadlock, which is bad.
As for the process by which you do this, you would use the $dbh->{mysql_insertid} to find the last inserted id. (This is documented in the DBD::mysql documentation.) This is a step up from, say Oracle or Sybase, where you have to explicitly issue a SELECT statement to retrieve this information.
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply]
[d/l]
[select] |
Actually DBD::Sybase will honor the $dbh->last_insert_id() method in a lot of cases.
Michael
| [reply] |
I don't understand what the problem is. Set id to autoincrement. Insert that record. The use last_insert_id (i think thats it) to get the ID of the record you just inserted and use it when adding order_products. No chicken and egg here. Maybe I missed where you think the delima is.
| [reply] |
Without transactions, I would be sure to run audit querys afterwards to check for broken RI (e.g. order_products rows where the order_id does not exist in order table), and also to see if there's any "product-less" order table rows (though maybe that's legal.. depending on your use you can delete them or know that they represent an error). | [reply] |
I believe that MySQL server used doesn't support it (older version).
well, better check:
Feature Comparison
| [reply] |