The Acts As List gem has been around for a pretty long time. DHH originally wrote it in 2007. That makes the plugin (now a gem) over 7 years old. The fact that I am still using this in production shows just how useful the gem was. Recently (yes, recently) my team and I decided to upgrade a very old rails 2.3 codebase. In doing this move, I started removing plugins from the old plugin directory as they are no longer supported in rails > 4.0. Long story short, we replaced the acts_as_list plugin with the acts_as_list gem. You would think that such a simple gem would work the same from rails 2.3 to rails 3.2. That’s what I thought too…
Both the old acts_as_list plugin and the new acts_as_list gem make use of the update_all.
However, as you can see, they make use of slightly different scoping mechanisms. Well it turns out that if you use the newer gem code that uses a where statement to scope the query you can end up with a particularly nasty query that will lock up your database and generally ruin your day. You’ll likely only notice this on a large table. The table I saw issues with had a little over 2 million records.
The problem arrises when your scoping column is optional. Allow me to illustrate with a concrete example.
In this example, If I always create a song with an associated album then all is well in the world. The update_all query is executed but it is scoped to the album_id so at most maybe 100 rows would have to be updated. Updating a 100 rows in MySQL is pretty fast and unlikely to cause any real issues in your application.
Everything breaks down when you decide that you want to let users upload songs to your app and then at some other point in the future associate those songs with an album. What happens when you upload a song without an album is that the album_id is nil. When the album_id is nil the acts_as_list gem will still attempt to update the position for the provided scope. What this means is that it will issue an update_all query that will include any song that doesn’t have an album_id. This could be 90% or more of your database table. Trying to update 1,800,000 records everytime someone uploads a song will result in crippling database performance and a terrible user experience.
So now that we know what is wrong with act_as_list. What can we do about it? One thing you can do is to change some of you models and domain logic. If you were to introduce a join model in between the the songs and albums model then you would ensure that you would never have an association that had NULL (nil) values and so you would always be calling update_all on a properly scoped model.
Secondly, you could ignore the acts_as_list plugin and move the song ordering onto a simple serialized Set on the album model. So changing your Album model to look something like this. song_order_ids would be a set of unique song_ids where the order mattered. You could then use the song_order_ids with a custom sort_by routine that would order your songs.
Of course doing things this way would require you to manually manage the song_order_ids and make sure it was updated appropriately. Not really that difficult, and probably the best option if you only have one model in your app that needs to be ordered.
Another option is to replace act_as_list with ranked-model. Ranked-model is a more modern version of acts_as_list that goes out of it’s way to avoid hitting the database when it doesn’t need too. This sounds like my kind of plugin. I think I’m going to replace my acts_as_list dependent models with this gem. After completing the migration I’ll write another post outlining any difficulties or interesting data migration steps necessary. Additionally I’ll monitor the database chatter to see if we are getting a reduction as a percentage of site traffic.
I actually ended up using the serialized column approach. After looking at the ranked-model project I decided that it wasn’t being actively maintained to my standards so I went for the simpler solution until I find a better project or make my own abstraction. One issue with my solution is that you can’t use the normal ActiveRecord scoping mechanisms which means pagination won’t work well. So generally speaking this solution would not be appropriate if you expected a really long list. Fortunately for my application I can’t imagine an album with more than 100 or so songs so this should work just fine.