Monthly Archives: May 2015

Turn on Snapshot Isolation from the Beginning

My team released a major new version of a system to production. Significant changes were made on all levels. Despite extensive tests and emulation of realistic load scenarios we started encountering deadlocks on our SQL Server DB after go-life.
After a short investigation I realized that enabling Snapshot Isolation on the DB should significantly reduce the likelihood of deadlocks. This article explains it quite well:

“The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.”

Enabling this feature does carry risks. Concurrency control implementation and temp table size have to be considered. Given we had years of development spent on the system project management explored other options. Considerable time was invested in alternative solutions. To make things more difficult we were not able to reproduce the scenarios causing deadlocks in a test or development environment. Multiple releases of new versions failed to reduce the errors in production.

Finally we convinced management that the risk was manageable and turned on Snapshot Isolation. It solved the issue immediately and no side effects ever arose.

After this experience I made it a rule to enable Snapshot Isolation on SQL Server from the beginning on any new project. Why this isn’t turned on by default is a mystery… If you develop on a different RDBMS check your option (most systems have a similar setting). While you develop you will continuously test against Snapshot Isolation level. If you have to enable it late in a project make sure you run regression tests.

To Kill a Mobile App

I started developing mobile apps in 2009. My first ‘professional’ app (my definition of professional here is simple: I got actually paid for the work…) has recently reached ‘end of life’. Not surprising for an app that went life in 2010. It had many downloads and fulfilled it’s mission. After 5 years it was decided not to keep the data in the back-end up to date anymore.
When I received an email requesting to ‘take the app offline’ I started to think about this for the first time: Taking the app off the app store (in this case Apple’s App Store) prevents new customers to download and install the app. However there is no way of ‘wiping’ the app off users phones and tablets! The app will still be on thousands of devices. Doing nothing (not updating the data) will leave users with stale data.
My first thought was releasing a new version of the app with a single screen indicating the app is not supported anymore. No doubt Apple would have some objections releasing an app like that. And of course there is no guarantee people actually update their app! We ended up tweaking the back-end data delivered via a service to show a pseudo entry containing a similar message. Less than ideal, as the message was not prominent and users only see it once they actually want to look at some data. We were also not able to turn off any of the apps functionality.
The solution is straight forward: If your app relies on an service to receive data implement a ‘kill switch’. Design the API to that allows the app to display a message and disable functions. And make sure this is part of your initial design. Trying to do this retrospectively can be difficult. In my case the app wasn’t updated for years, the IDE and targeted OS version were outdated and it would have been very costly to update the app just for this.