Let us say that I have two users trying to reach a table in the database called "comments" in the following order:
User1 is making and update for a record with id = 10
UPDATE comments SET comment="Hello World" WHERE id=10
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:
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
A statement can lock the entire table. Table-level locking systems always lock entire tables. Row-level locking systems can lock entire tables if the WHERE clause of a statement cannot use an index. For example, UPDATES that cannot use an index lock the entire table.
InnoDB has row-level locking. MyISAM only has full table-level locking. InnoDB has what is called referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS). InnoDB supports transactions, which means you can commit and roll back.
InnoDB implements standard row-level locking where there are two types of locks, shared ( S ) locks and exclusive ( X ) locks. A shared ( S ) lock permits the transaction that holds the lock to read a row. An exclusive ( X ) lock permits the transaction that holds the lock to update or delete a row.
MyISAM is a non-transactional storage type, and any write option needs to be rolled back manually (if needed). InnoDB is a transaction storage type that automatically rollbacks the writes if they are not completed.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With