Take the 2-minute tour ×

I'm in a situation where an entire column in a table (used for user tokens) needs to be wiped, i.e., all user tokens are reset simultaneously. There are two ways of going about it: reset each user's token individually with a separate UPDATE query; or make one big query that affects all rows.

The advantage of one big query is that it will obviously be much faster, but I'm worried about the implications of a large UPDATE query when the database is big. Will requests that occur during the query be affected?

share|improve this question
How large is "large"? –  maček Nov 22 '12 at 3:19
batch it.......say 10,000 rows at a time.... –  Mitch Wheat Nov 22 '12 at 3:21

2 Answers 2

up vote 1 down vote accepted

Afraid it's not that simple. Even if you enable dirty reads, running one big update has a lot of drawbacks:

  • long running transaction that updates one column will effectively block other insert, update and delete transactions.
  • long running transaction causes enourmous load on disk because server is having to write to a log file everything that is taking place so that you can roll back that huge transaction.
  • if a transaction fails, you would have to rerun it entirely, it is not restartable.

So if simultaneous requirement can be interpreted "in one batch that may take a while to run", I would opt for batching it. A good research write up on performance of DELETEs in MySql is here: http://mysql.rjweb.org/doc.php/deletebig, and I think most of the findings are applicable to UPDATE.

The trick will be finding the optimal "batch size".

Added benefits of batching is that you can make this process resilient to failures and restart-friendly.

share|improve this answer

The answer depends on the transaction and isolation level you've established.

You can set isolation to allow "dirty reads", "phantom reads", or force serialization of reads and writes.

However you do that UPDATE, you'll want it to be a single unit of work.

I'd recommend minimizing network latency and updating all the user tokens in one network roundtrip. This means either writing a single query or batching many into one request.

share|improve this answer

Your Answer


