Mysql – Does MySQL lock full table in case of update query on specific rows

innodbMySQL

I just wanted to know if the MySQL 5.5 with InnoDB will lock the whole table instead of some specific rows for SELECT query while UPDATE query is running on that specific table?

I can see that the SELECT queries stop responding while I was running the below query. All of the associated services were down in that particular period.

UPDATE table SET column='sample' where column is null;

Around 3.6 million(Total: 4 Million) rows were going to updated with this query.

Index on the column which is part of where clause makes any effect on this?
Is there any possible way to minimise the execution time? I am ok with the query changes as long as the output remains the same.

Best Answer

Locking 3.6M rows, especially when it is 90% of the table, has multiple burdens on the system. It may "feel like" the entire table is locked.

This sounds like a 1-time fix; perhaps you should simply wait for it to finish -- which could take many minutes.

If you need a better, but more complex, solution, do the UPDATE in chunks of 100-1000 rows. More details: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Related Solutions

Mysql – UPDATE waits for lock too long after SELECT in MySQL (Innodb)

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc.

How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running?

My points are: (a) The benchmark is stressing the limits, not looking for reality; (b) Oracle has made great strides recently in handling more connections.

When you go beyond the effective connection limit, latency of queries will suffer terribly. So, don't benchmark beyond that. Furthermore, throttle the users so that not "too many" get to MySQL 'simultaneously'. In older versions, it was so bad that (a) throughput would go down as you add more clients, and (of course) (b) latency would go through the roof. Now, throughput plateaus while latency climbs.

For a single item, be sure to wrap Rolando's SQL in a transaction:

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
COMMIT;

For handling more than one id at the same time, you should sort the ids to help avoid deadlocks. Then do them in a single transaction:

BEGIN;
SELECT * FROM pics WHERE id IN ($id_list) FOR UPDATE;
UPDATE pics SET seen=1 WHERE id IN ($id_list) LIMIT 1;
COMMIT;

You can simplify the code more: Do the

UPDATE...
Check rows_affected; exit if 0
SELECT ...

Since rows_affected is local to the 'session', you can discover whether the UPDATE grabbed the row. Note that there is no need for transactions (as far as this code snippet goes), and autocommit=1 would suffice.

Let me point out another issue with the design: Indexing a flag (seen) has two problems (a) The optimizer is unlikely to use the index, due to low cardinality; and (b) the update has to remove a row from that index and add a new row elsewhere; this is costly.

The logic you described does not seem to need INDEX(seen); does something else need it? If not, DROP that INDEX; that may solve the problem.

MySQL Optimization – UPDATE Query Stuck on Sending Data State

PROBLEM

In the EXPLAIN plan, select_type is SIMPLE.

That's a full index scan VisitDiffTimes and range scan on CustomerVisits.

SUGGESTION #1

You need to reorder the columns in the PRIMARY KEY of VisitDiffTimes

ALTER TABLE VisitDiffTimes DROP PRIMARY KEY;
ALTER TABLE VisitDiffTimes ADD PRIMARY KEY (`OrderID`, `CustomerID`, `MerchantID`);

This will eliminate the full scan on VisitDiffTimes

SUGGESTION #2 (Optional)

Create a compound index on those two columns for CustomerVisits

ALTER TABLE CustomerVisits
    DROP INDEX OrderID,
    ADD INDEX OrderID_CustomerID_ndx (OrderID,CustomerID)
;

The JOIN may end up cleaner

SUGGESTION #3 (Optional)

Set the join_buffer_size to 16M

Add this to my.cnf

[mysqld]
join_buffer_size = 16M

You don't have to restart. Just login as root@localhost and run

mysql> SET GLOBAL join_buffer_size = 16 * 1024 * 1024;

GIVE IT A TRY !!!

SUGGESTION #4 (Optional)

Another Index with columns (CustomerID,OrderID)

ALTER TABLE CustomerVisits
    DROP INDEX CustomerID,
    ADD INDEX CustomerID_OrderID_ndx (CustomerID,OrderID)
;