MySQL - Update table rows without locking the rows

Question

I have requirement where we need to update the row without holding the lock for the while updating.

Here is the details of the requirements, we will be running a batch processing on a table every 5 mins update blogs set is_visible=1 where some conditions this query as to run on millions of records so we don't want to block all the rows for write during updates.

I totally understand the implications of not having write locks which is fine for us because is_visible column will be updated only by this batch process no other thread wil update this column. On the other hand there will be lot of updates to other columns of the same table which we don't want to block

Mona

Answer 1

First of all, if you default on the InnoDB storage engine of MySQL, then there is no way you can update data without row locks except setting the transaction isolation level down to READ UNCOMMITTED by running

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

However, I don't think the database behavior is what you expect since the dirty read is allowed in this case. READ UNCOMMITTED is rarely useful in practice.

To complement the answer from @Tim, it is indeed a good idea to have a unique index on the column used in the where clause. However, please note as well that there is no absolute guarantee that the optimizer will eventually choose such execution plan using the index created. It may work or not work, depending on the case.

For your case, what you could do is to split the long transaction into multiple short transactions. Instead of updating millions of rows in one shot, scanning only thousands of rows each time would be better. The X locks are released when each short transaction commits or rollbacks, giving the concurrent updates the opportunity to go ahead.

By the way, I assume that your batch has lower priority than the other online processes, thus it could be scheduled out of peak hours to further minimize the impact.

P.S. The IX lock is not on the record itself, but attached to the higher-granularity table object. And even with REPEATABLE READ transaction isolation level, there is no gap lock when the query uses a unique index.

Lakieshalakin

This don't block rows that doesn't got updated but still block all rows that got updated, until the end of the query, and this may cause error "The total number of locks exceeds the lock table size" on larger tables. You can fix that by increasing param innodb_buffer_pool_size – Pavlov

Answer 2

This is because UPDATE wants to keep your table in a consistent table, so it doesn't allow to leave the update partially done. This is why if you interrupt the command before finishing, all updated done up to that moment are reverted back with a costly operation.

To fix: Take the WHERE part of your UPDATE and save in a temporary table PK of rows to be updated with CREATE TABLE temp AS SELECT id FROM blogs WHERE conditions;. Prepend SET ISOLATION LEVEL READ UNCOMMITTED; to not get blocked, if this is acceptable in your context. Then UPDATE rows one by one with a SQL FOR LOOP + CURSOR. In this way even if loop is interrupted before finishing, you won't lose updates already done up to that moment and you can restart from the row where you've left to. Depending of the amount of data to be updated, you can even choose to just iterate the whole table, with the FOR LOOP as above, or HANDLER https://dev.mysql.com/doc/refman/5.7/en/handler.html, so skipping the temp table part and checking row by row if needs to be updated.

As an alternative, you may do smaller updates with a small limit clause: update blogs set is_visible=1 where some conditions limit 10 maybe in a loop to automate.

Pavlov

Answer 3

Best practice is to always acquire a specific lock when there is a chance that an update could happen concurrently with other transactions. If your storage engine be MyISAM, then MySQL will lock the entire table during an update, and there isn't much you can do about that. If the storage engine be InnoDB, then it is possible that MySQL would only put an exclusive IX lock on the records targeted by the update, but there are caveats to this being the case. The first thing you would do to try to achieve this would be a SELECT ... FOR UPDATE:

SELECT * FROM blogs WHERE <some conditions> FOR UPDATE;

In order to ensure that InnoDB only locks the records being updated, there needs to be a unique index on the column which appears in the WHERE clause. In the case of your query, assuming id were the column involved, it would have to be a primary key, or else you would need to create a unique index:

CREATE UNIQUE INDEX idx ON blogs (id);

Even with such an index, InnoDB may still apply gap locks on the records in between index values, to ensure that the REPEATABLE READ contract is enforced.

So, you may add an index on the column(s) involved in your WHERE clause to optimize the update on InnoDB.

Yale

I am not sure if you got my question right? I don't want row locks to happen during update so using ` select ... For update` will lock the tables. My question is is there a way we can update a row without row lock? – Mona

That's only true in the case of MyISAM. On InnoDB, FOR UPDATE would not lock the entire table assuming you have the correct unique indices setup. – Yale