CODEWITHSUNDEEP
mysqlsqldatabaseperformanceinnodb
Basel
Basel

Reputation: 369

Clarifying the difference between row-level lock in InnoDB engine and table-level lock in MyISAM engine in MySQL database

Let us say that I have two users trying to reach a table in the database called "comments" in the following order:

  1. User1 is making and update for a record with id = 10

    UPDATE comments SET comment="Hello World" WHERE id=10

  2. User2 is making a select for all rows of the same table comments

    SELECT * FROM comments

I want to discuss the difference between the following cases:

  1. If the table's engine is MyISAM : the Update query will lock the whole table which will queue the select query until the update of the row is finished and then it will be executed which will stop any user from asking anything from this table until the update is finished.
  2. If the table's engine is InnoDB : the update query will lock the updated row.

I WANT TO KNOW HOW DOES THIS LOCK AFFECT THE SELECT QUERY???

I mean if the select ask the database for the whole records of the comments table and found one of them (id =10 ) is locked does the database queue the select query again until the updated is finished?

If yes then what is the difference between the two engines??

If No I want to say that I have the same situation above in my website and even I changed my tables engines from MyISAM to InnoDB but the problem of queuing any requests when there is an update or insert query still occurred.

Any explanation for this situation will be so helpful . thank you in advance

Upvotes: 14

Views: 5353

Answers (2)

Ritika Jain
Ritika Jain

Reputation: 64

In Innodb in your scenario the result will come for select query but with old data for row where id=10 if not updated.The result wont stop.

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10246

In InnoDB it depends on whether transaction is enabled or not. InnoDB has MVCC feature that means while thread 1 is updating, thread 2 can read without lock.

this is already answered here InnoDB's row locking the same as MVCC Non-Blocking Reads?

if transaction is disabled, same with MyISAM? I guess so but not sure.

Upvotes: 1

Related Questions