Tim Freeborn



Why locking database tables is a bad idea

When working with databases, in order to produce a consistent data backup it has become common practice to lock the tables.

This causes problems when the database is busy and needs to be writable at all times for an application to function. If you find yourself in this situation there are a few common ways to work around the issue. I have highlighted my top three below:

Replication to a second instance

MySQL and almost all of its derivatives support replication natively and this can be used a means of working around the issue of locks. This allows you to have a second copy of the data that you can lock and not impact the running of the application. This method can work with any of the data base structures that are supported in the database.

MySQL dump – single-transaction

If your databases are using only INNO DB tables it is possible to take a database dump using the –single-transaction flags which will run without locking tables. If your database is not running entirely on  INNO DB tables you can use tools like mysqlhotcopy which still locks tables but only the ones it is actively reading which reduces the impact overall on the application that is using them.

LVM snapshots

If the file system that is hosting the database is using LVM then you can use the 10% of space that is left in the VG by default to take an active snapshot. By taking a snapshot of the volume you will create a space in which all new changes are written and stored so you can take a MySQL dump of the data with locks that will not impact the live application.

Once the dump of the database is complete you can consolidate the snapshot back into the live disk. This will bring all the changes from the period into the live copy. This means that the only time in which the DB is locked will be while you lock it to flush all the data from memory to the DB. This normally takes seconds unless the DB has a very large memory foot print.

As a point of interest, of the three options outlined this is the approach we are currently looking at implementing as a part of our backup system to get round the issue of locks.