Does update lock the row or the entire table? 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.
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 &quot;table locks&quot;.  Some &quot;escalate&quot; a bunch of row locks to a &quot;table lock&quot;.
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.