CODEWITHSUNDEEP
mysqlinnodb
Wenhao Ji
Wenhao Ji

Reputation: 5161

Whether UPDATE statement locks the rows in the table separately or entirely when using InnoDb

Say, we have a table called person like below

CREATE TABLE person (
    id INT,
    name VARCHAR(30),
    point INT
);

I want to update the entire table changing the point of a person according to other's like

UPDATE person SET point=(
    SELECT point FROM person WHERE some-condition
);

or, simply just increasing by one, like

UPDATE person SET point=point+1;

When executing the scripts above, which rows will be locked and will other statements wait until the update statement finishes or can be executed between two update operations?

Upvotes: 0

Views: 76

Answers (1)

O. Jones
O. Jones

Reputation: 108839

Neither of your update statements has a where clause. (Your first one has a select with a where clause; it's possible you want that where clause to be part of the update, but I am not sure about that.)

That means they'll update all the rows in your person table. Transaction semantics provided by InnoDB says that each row will be locked until the entire update is completed. That is, other updates will be blocked. If you attempt other updates in an order different from the one in this query, you're risking a deadlock.

Other client connection select-queries will see the previous state of the table ... the state at the instant before your update statement began ... until your update statement completes. In many cases InnoDB can do that without delaying its response to the other connections' queries. But sometimes it must delay its response. The biggest delay may come at the end of your update query while InnoDB is committing its results.

Keep this in mind: in order to implement transaction semantics, InnoDB sacrifices the predictability of query performance.

I strongly suggest you avoid doing updates without where clauses where it makes sense to do that. It doesn't in your second (give every person another point) query.

Upvotes: 1

Related Questions