DaveWentzel.com            All Things Data

Object Relational Mappers Series - Concurrency and the Non-Updating Update

This is the next post on my series on ORMs.  

Most ORMs use optimistic concurrency.  When the ORM saves an entity back to the data source its persistence manager will set every column value, whether or not it thinks the value has changed.  The proponents will tell you that this approach is vital to the consistency of the entire entity.  The ORM cannot know if some other user changed another column value in the interim and will of course not attempt to merge changes from different users.  The only safe approach is to write every column back to the row.  This is where the strict adherence to optimistic concurrency may not be efficient in the ORM, but is rarely user-configurable.  When it is user-configurable, can it be changed for only some queries or is it system-wide?  

This concurrency issue I like to call the "non-updating UPDATE".  When you look at SQL Profiler you will see all columns are being set to new values, even if you know only one given data value was changed.  This essentially means that each bit is rewritten, even though it was not changed.  This causes unnecessary transaction logging and dirty page flushes in your RDBMS.  

Not every ORM has this problem.  Some have implemented a "dirty" flag that can be interrogated.  If the user changes a data element the dirty flag is set, meaning that the data must be persisted to the RDBMS.  This is generally a good implementation, when you can find it.  

Most ORM vendors will tell you somewhere in fine print that concurrency control can be changed in their tool but they generally won't give you much detail.  Why?  Because it really depends on the RDBMS you are using.  Oracle's default concurrency is different than SQL Server.  The ORM tool will tell you to go study the RDBMS and set up concurrency at the db or connection level and manage it there.  Seems again like the ORM hasn't given me any real advantage here.  I still need to know the RDBMS (I thought one of the selling points of an ORM was less database knowledge), I still need to know my data, and I still need a good knowledge of SQL.