SQL locking of table in an inner join

Question

Every day, I run a SQL statement to set an estimate of an aggregated value. This is a mysql server and the code looks like this:

UPDATE users
INNER JOIN (
  SELECT user_id, COUNT(*) AS action_count
  FROM action_log
  GROUP BY user_id
) AS action_log
ON users.id = action_log.user_id
SET users.action_count = action_log.action_count

As my database has grown, this is taking longer to run and it seems to be affecting other queries. As the code stands now, is there a lock held on my action_log table for the entirety of the update?

Since this is an estimate and doesn't need to be totally accurate, I'm considering splitting this up into multiple SQL statements. One that does the select to get the aggregated counts per users. And then do single updates for each user row.

I'm hoping that would help. Running EXPLAIN on this query didn't give me much info and I'm not sure if breaking things up in this fashion will actually help.

Answer 1

Yep, it's probably going to hold onto that lock. If you can separate out the SELECT your problem will go away, something like this:

DECLARE @THETABLE TABLE(
USERID INT,
TOTAL INT
)

INSERT INTO @THETABLE
SELECT user_id, COUNT(*) AS action_count
FROM action_log
GROUP BY user_id

UPDATE users
INNER JOIN (
@THETABLE
) AS action_log
ON users.id = action_log.user_id
SET users.action_count = action_log.action_count

You could also dump the results into a #TEMP_TABLE but I like using a table variable. Another option is to play with locking hints like NOLOCK or READPAST or ROWLOCK but I wouldn't suggest it, that can get you into all kinds of other trouble.