14

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

Basel
  • 359
  • 3
  • 16
  • In your example the select has to wait on the update to finish for both engines. It would be different if the select query did not include the record with ID 10 (which is locked by the update), in this case the MyISAM select has to wait, but InnoDB doesn't. "This feature in InnoDB is known as concurrency" - http://stackoverflow.com/a/28070969/934739 – Gerard Roche Sep 24 '16 at 14:11

2 Answers2

1

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.

Community
  • 1
  • 1
Jason Heo
  • 9,956
  • 2
  • 36
  • 64
  • All my tables are changed to InnoDB , Is it possible that the table is InnoDB but the transaction is disabled? If yes How can I find out if it is disabled or not and how to enable it – Basel Nov 24 '13 at 11:41
  • @Basel I mean if autocommit off then current connection' transaction is disabled. to use transaction, "SET autocommit = 0; START TRANSACTION; UPDATE .... ; COMMIT;" – Jason Heo Nov 24 '13 at 11:47
0

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.

Ritika Jain
  • 64
  • 4