A Locking Problem

I have been aware for quite some time that, occasionally, plays of music made by AMP would not get recorded in the PLAYS table as they should. It’s difficult to know precisely why: when you’re developing the code and saving changes to the script as it’s playing something, it could well be that your editing has caused the ‘record in PLAYS’ bit of code to get skipped.

Or it could be a locking issue. Putting it at its simplest, databases cannot have one person modifying data whilst simultaneously allowing a second person to read that data. If I am in the process of withdrawing £5000 from my bank account just as a credit check is being performed, do we let the credit agency see the £5000 in the account? Or do we see it missing the £5000, even though I might be about to type ‘Cancel’ at the ATM? To resolve these data concurrency issues, all databases implement a form of ‘locking’: if I am going to withdraw money from my account, we lock the data until my withdrawal is complete, so that no-one can see it, either with or without the £5000, then when I’m finished at the ATM, we unlock and people can read the definite results.

The database which AMP uses has a fairly crude locking mechanism, whereby a ‘write of data’ blocks a ‘reader of data’… and (potentially) vice versa, in precisely this way. So if I’m simply querying the data in the PLAYS table, that would potentially prevent AMP from being able to record a new ‘play’ in the PLAYS table… and that might explain the occasionally ‘missing’ records I’ve noticed over time. (The timing of reads/writes would have to be quite precise to encounter the issue, but it’s a plausible explanation for most of my missing ‘PLAYS’, I think).

Fortunately, there’s a fix, though it’s a bit database-technical! Instead of writing a new play directly into the PLAYS table, you could get an application to write the new PLAY record into a special sort of log -a log which is never directly read or queried, so no-one’s going to be locking it: the write will therefore always take place, guaranteed. At a time the database thinks propitious, it will transfer the records from this log into the actual PLAYS table -and, because the database decided when to do this, it is also guaranteed not to block or fail because of someone querying the table. This mode of operation is called ‘Write Ahead Logging’ -or, more colloquially, ‘running your database in WAL mode’. It is not SQLite’s default mode of operation… and the AMP database therefore doesn’t run in WAL mode by default either.

So, the latest release of AMP comes with yet another run-time switch: ––setwalmode. It needs to be used together with the ––dbname parameter, otherwise we won’t know what database you mean to put into WAL mode (or, rather, we’ll assume it’s called ‘music’ if you insist on not mentioning a database name at all!). In my case, I run everything out of a database called “main”, so I need to type this:

amp --dbname=main --setwalmode

… and that will switch my AMP database into running in WAL mode. When AMP sees this new switch, it will check if the database is already in WAL mode (and warn you politely if so, before quitting without having done anything). If it isn’t, it will switch it into WAL mode -which is a one-time, set-and-forget process. No data is harmed in the conversion process, which takes mere seconds to complete. Once it has been converted, the AMP program quits and you can relaunch it more normally with whatever combination of commands you usually use to get it to play music. The difference won’t be noticeable to anyone, particularly, but it should mean that your database is protected against extraneous locking problems in the future.

It’s a very technical increment in functionality, in other words, but quite an important one. If you choose not to run your database in WAL mode (or to convert it to run in that mode), AMP will not complain or warn you about it. It’s just something I now encourage you to do to ensure there are no locking issues arising in the future.

I will mention in passing, too, a second new feature added in the current release of AMP: ––reportexport. You may have noticed the website has recently acquired a new page of text-based music listening listings. Did you stop to wonder where the data for that page comes from? Well, it comes out of AMP! The report of ‘plays’ which AMP has always allowed you to generate and view on-screen can now be exported to an external pipe-delimited text fie, thanks to the new ––reportexport parameter, which takes a path/filename value. So, for example:

amp --dbname=main --report --reportexport=/home/hjr/Desktop/myplays.txt

That command will return with a ‘report written successfully’ message after mere seconds, and the output (if opened in a text editor) might look something like this:

PLAYDAY|PLAYTIME|ARTIST|ALBUM|GENRE|DURATION
2021-01-09|16:48|Olivier Messiaen|Thème et variations (Loriod - 1977)|Chamber|00:12:17
2021-01-09|17:00|Gregorio Lambranzi|Dances from the School of Gregorio Lambranzi (Ball - 1980)|Orchestral|00:09:47
2021-01-09|18:13|Giacomo Antonio Perti|Gesù al Sepolcro (Vartolo - 1990)|Oratorio|01:12:29
2021-01-09|19:23|Johann Christoph Friedrich Bach|Keyboard Concerto in E flat major (Alexander-Max - 2007)|Concerto|00:18:58
2021-01-09|20:08|Gian Francesco Malipiero|Sinfonia dello Zodiaco (Almeida - 1994)|Symphonic|00:42:21

As mentioned, the various data elements are separated from each other by a pipe character (i.e., a vertical line, or |). This is, therefore, not technically a comma-separated values (CSV) file you might be more familiar with -but it can be imported into a spreadsheet just as easily, as they all give you the opportunity to specify what the delimiter is when opening such files. Anyway, once the PLAYS data is external to the database like this, the sky’s the limit on what you can do with it. In my case, I’ve created a cron job to copy the text file to my web server. My web server also has a cron job which periodically truncates a WordPress table and re-loads it from the plays text file. Thus, my PLAYS table is now present within my WordPress database -and it’s relatively easy (especially with the WP Data Access plugin) to get the contents of a WordPress table displayed as a web page.

So long as my AMP PLAYS table is accurate (which the new locking mechanism should mean is the case), this new report export functionality allows my website to display an accurate record of the music I listen to each day -something I’ve been after for a long time. And the icing on the cake is that I am no longer subject to Last.fm’s crazy ‘data matching’ algorithms, which in the past have turned “Aram Khachaturian” into Արամ Խաչատրյան, or “Edward Elgar” into “Sir Edward Elgar” (whilst mysteriously not turning “Benjamin Britten” into “Baron Britten of Aldeburgh”)! I finally have the music I play displayed in the manner I tagged it, not as someone else insanely thinks it should be displayed.

I don’t imagine there will be a lot of people making use of ––reportexport functionality; and ––setwalmode is strictly a use-once-and-forget affair anyway. But they are significant enhancements to functionality for some people, regardless.