0

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.

  • 1
    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 Jul 18 at 23:01
  • (Jonathan's many questions are important, but most are not relevant to the rather narrow Question at hand.) – Rick James Jul 19 at 17:11
2

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.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.