We ran across a fun gotcha today while performance testing some code. The issue was originally discovered when we noticed a 300ms query taking 20 seconds. After about an hour of debugging we found the issue to be with aliases in table locks.

Here’s what our original SQL looked like:

LOCK TABLES files_versiondownloadcount WRITE;
 
UPDATE files_version AS version, files_versiondownloadcount AS count
SET version.downloads = version.downloads + count.downloads, count.downloads = 0
WHERE version.id = count.version_id;
 
UNLOCK TABLES;

Our first idea was to add locking into the files_version table as well. Doing this caused MySQL to error due to syntax. So we then added the aliases to both tables. Voila, back to a 300ms query.

We run one more queryset almost identical to this, and found out that mislabeling the table alias in the lock (not using the same alias in the query) caused the same performance impact we had seen originally, with the 20s query.

Here is the SQL with the updated LOCK statement:

LOCK TABLES files_versiondownloadcount as count WRITE,
files_version as version WRITE;
 
UPDATE files_version AS version, files_versiondownloadcount AS count
SET version.downloads = version.downloads + count.downloads, count.downloads = 0
WHERE version.id = count.version_id;
 
UNLOCK TABLES;