I am not using a transaction and my storage engine is InnoDB. I want to update the entire table with:
UPDATE someTable set someColumn = CONCAT('somePrefix', SUBSTR(2, someColumn))
I am told doing this will lock the whole table until the query completes which is about 200 seconds, which is not good.
So i want to try iterating:
UPDATE someTable set someColumn = CONCAT('somePrefix', SUBSTR(2, someColumn)) WHERE id BETWEEN 1,50000
Will this lock the first 50,000 rows? There is a primary key on id This only takes 4 seconds which is a more manageable lock time, but it would be good to know if it's locking the whole table, just the 50,000 rows, or just one row at a time while it updates.
First of all, you don't have the right syntax for BETWEEN. You want:
... WHERE id BETWEEN 1 AND 50000
InnoDB will lock all examined rows during an UPDATE. If you use an indexed column (the primary key qualifies as indexed), it will only lock the rows matched by the condition.
You can get an estimate for the number of examined rows by using EXPLAIN before your query and look at the rows column (note this is only the optimizer's rough estimate, not a precise count of rows examined).
If you are in a REPEATABLE-READ transaction, it will also lock any gaps in that range. I'm guessing it's not unlikely that this will be a problem for you, but it will prevent anyone from inserting new rows into those gaps. If you need to avoid this, you can set your transaction isolation to READ-COMMITTED.
Be sure to COMMIT your transaction promptly (or use autocommit). Locks may be held after the UPDATE is finished, until your next COMMIT.