阐明了InnoDB引擎中的行级锁与MySQL数据库中MyISAM引擎中的表级锁的区别

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

假设我有两个用户试图访问数据库中的一个名为“comments”的表，顺序如下:

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

User1正在用id = 10创建和更新一个记录。

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

更新注释设置注释="Hello World"， id=10。
User2 is making a select for all rows of the same table comments

User2正在为相同表注释的所有行进行选择

SELECT * FROM comments

SELECT * FROM评论

I want to discuss the difference between the following cases:

我想讨论以下案例的不同之处:

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.
如果表的MyISAM引擎:更新查询队列将锁定整个表的select查询,直到更新行完成,然后将这将阻止任何用户要求执行任何东西,从这个表,直到更新完成。
If the table's engine is InnoDB : the update query will lock the updated row.
如果表的引擎是InnoDB: update查询将锁定已更新的行。

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

我想知道这个锁是如何影响SELECT查询的?

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?

我的意思是，如果select查询comments表的整个记录并发现其中的一个(id =10)被锁定，那么数据库是否会再次对select查询进行排队，直到更新完成?

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.

如果没有，我想说我在我的网站上有同样的情况，甚至我将我的表引擎从MyISAM更改为InnoDB，但是当有更新或插入查询时，仍然存在排队请求的问题。

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

任何对这种情况的解释都是很有帮助的。提前谢谢你

2 个解决方案

#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.

在InnoDB中，这取决于事务是否启用。InnoDB有MVCC特性，这意味着当线程1更新时，线程2可以不加锁地读取。

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

在这里，InnoDB的行锁定与MVCC非阻塞读取相同吗?

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

如果事务被禁用，与MyISAM一样?我想是的，但不确定。

#2

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.

在您的场景中的Innodb中，结果将用于select查询，但如果没有更新id=10的行将使用旧数据。结果不会停止。

#1