InnoDB insert order conflicts

By Jose Manimala
4 Replies | On January 23, 2013 |

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.

The problem

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.

The Fix

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.

Cheers,
Jose

Share
  • walesmd

    Uh… No. Those benefits have nothing to do with ORM as a concept. These are merely lower-level benefits of the libraries you considered.

    This would be like someone stating client-side libraries are great because they use JSON. Sure, the ones you looked at do, but it has literally nothing to do with the technology you are attempting to promote.

  • Jose

    Hey there, thanks for the reply.

    The benefits are some I have seen that people don’t pay attention to ( and agreed are specific to the quoted libraries ). The post is about a problem we faced internally. The answer to it was connection pooling. An ORM also enforced good way to interact with the database. The headline of the article was incorrect. Its a learning and thank you for bringing it up.

    Cheers,
    Jose

  • http://codeflow.org/ Florian Bösch
  • J. Grizzard

    Wait… How does (or even, how can) innodb disk sync delay effect an app using the database? Is your problem actually that you’re not correctly committing transactions when you should be, or using the wrong isolation mode?