MySQL Transaction Isolation Levels and Locks
Recently, an application that my team was working on encountered problems with a MySQL deadlock situation and it took us some time to figure out the reasons behind it. This application that we deployed was running on a 2-node cluster and they both are connected to an AWS MySQL database. The MySQL db tables are mostly based on InnoDB which supports transaction (meaning all the usual commit and rollback semantics) as well as row-level locking that MyISAM engine does not provide. So the problem arose when our users, due to some poorly designed user interface, was able to execute the same long running operation twice on the database.
As it turned out, due to the fact that we have a dual node cluster, each of the user operation originated from a different web application (which in turn meant 2 different transaction running the same queries). The deadlock query happened to be a “INSERT INTO T… SELECT FROM S WHERE” query that introduced shared locks on the records that were used in the SELECT query. It didn’t help that both T and S in this case happened to be the same table. In effect, both the shared locks and exclusive locks were applied on the same table. An attempt to explain the possible cause of the deadlock on the queries could be explained by the following table. This is based on the assumption that we are using a default REPEATABLE_READ transaction isolation level (I will explain the concept of transaction isolation later)...read more
When it comes to Software Version/Configuration Management, there might be a whole lot of vendor or open source implementations to choose from but in recent years, there’s none that could parallel Git in terms of being the most development/hacking friendly.
I’ve used quite a few different forms of software management tools, from the CVS/SVN family to the Clearcase/Perforce family (which I personally feel is absolutely horrible) but it is with Git that I finally think that Software Versioning is no longer a necessary evil but something that actually helps in the software development process. Perhaps, I will need to corroborate my statement with some examples later but using Git can actually encourage developers to experiment and be creative in their code, knowing that they can always reset back any code changes without any penalty or overheads....read more
Rails Development using Vim
Finding a suitable editor for Ruby on Rails development can be quite long journey. I started out trying Aptana’s Studio but later found it simply too resource intensive and heavy. The main benefit it brought was the ability to perform debugging on a Windowing environment (with the inclusion of the ruby-debug-ide gem. In the end, I decided to go with Vim. Many developers may recoil in horror at the thought of using something as arcane as Vim to do development work but I soon found out that my earlier misgivings were all uncalled for as Vim proved itself to be as powerful and much more nimbler than Aptana Studio....read more
Living a Vagrant life
Anyone that had been working on multiple projects on the same boxes will know that there is never a certainty if the development environment you have worked on for a particular project will still be able to run months down the road. It’ll probably be filled with all sorts of gunk and remnants of other similar projects which would cause problems when you try to run your code again. This problem was especially pertinent if you run code that is very environment sensitive e.g. Ruby on Rails and its gems.
Thus, the idea of an automatically provisioned development environment that can be spun up instantly in a clean state was something that had been talked about for a long time in my agile development team. Recently, we came across Vagrant (http://www.vagrantup.com) and it seemed like the panacea to our coding environment problems. I had it setup for a Ruby On Rails project and it will automatically install RVM, Ruby gems and all the native packages for the Vagrant box. It will also automatically provision MySQL and even installs an Aptana Studio ide....read more