Update [14/02/2013]: The article title has been changed from “Why use an ORM?” to “InnoDB insert order conflicts”
We at langoor.mobi love opensource technologies. Mysql is our preferred db of choice to build a scalable infrastructure for our product.
We faced an issues recently where the sequence of our ajax calls was messing up the workflow. The problem was related to using innodb engine. Innodb engine is inherently a in memory data storage engine, which syncs to the HDD based on a my.cnf setting for commit after certain number of inserts.
When working with innodb forcing a commit will ensure that innodb writes the data to the database.
Another way to fix this would be to use an ORM like sqlalchemy(python) or doctrine(php). An Object Relational Mapping layer helps abstract a set relational data to in memory objects so as to better work with data. An ORM detects the underlying database engine and ensures that best practices are followed when working with a databases.
Another reason to use an ORM
Using an ORM enforces connection pooling during the life cycle of application request hence ensuring that the database doesn’t lock because of connections not being closed after a query has completed execution.
ORMs have been a boon for us here at langoor.mobi. We recommend using one or at least framework like django which uses one. Save you from reworking your code and helps reduce development time.