Does update lock the row or the entire table?

Question

Basic question: When I update a row in a database table, does it lock the current row or the entire table?

I am using MySQL. When I update column which is not unique.

Is the locking different from one database engine to another? I have read this article but I am still confused.

Depends. How much of a table are you updating? One row or more? How large is each row? Are you updating rows by primary key or by querying other columns? Is the estimated row count accurate? Are you using any hints? What about the escalation policy on the table? Delayed write? What is your isolation level? Any cascade foreign keys? Does the lock actually block anyone? — Jonathan Fite, Commented Jul 18, 2020 at 23:01
(Jonathan's many questions are important, but most are not relevant to the rather narrow Question at hand.) — Rick James, Commented Jul 19, 2020 at 17:11

Rick James · Accepted Answer · 2020-07-19 17:19:53Z

The links go into gory details, but this question seems to need a simple yes/no answer.

For ENGINE=MyISAM or MEMORY, the only lock is a table lock.

For ENGINE=InnoDB:

Think of it this way -- It locks every row it had to look at.

No index on the column -- It had to check every row, so all rows are locked. That effectively locks the entire table.
UNIQUE index on the column -- Only one row need be touched, hence, locked.
In between... A non-unique INDEX on the column -- It must lock all the rows with that value. (Possibly the 'next' row will get locked, too.)

PS: the PRIMARY KEY is a UNIQUE index in MySQL.

Some other vendors have different index definitions, and some do have "table locks". Some "escalate" a bunch of row locks to a "table lock".

Bottom line:

Add an INDEX, either UNIQUE or not, as appropriate.
If you need to look at the row before potentially UPDATEing it, use a transaction and SELECT ... FOR UPDATE.

1 Answer 1