问题描述
使用myisam引擎运行查询时，因为它不是事务性的，所以长查询(据我所知)不会影响其他查询的数据.
When running queries while using myisam engine, because its not transactional, long queries (as far as I understand) don't affect the data from other queries.
在InnoDB中，它警告的一件事是避免长查询.当InnoDB快照时，是否可以快照所有内容?
In InnoDB, one of the things it warns is to avoid long queries. When InnoDB snapshots, is it snap shotting everything?
我问这个原因是:出于某种原因说一个查询要花比正常时间更长的时间并最终回滚.同时，其他200个用户已将行更新或插入到数据库中.当长查询回滚时，是否还会删除其他用户所做的更新/插入?还是涉及其他用户的行是安全的，除非他们与被回滚的行交叉?
The reason I am asking this is: say a query for whatever reason takes a longer time than normal and eventually rolls back. Meanwhile, 200 other users have updated or inserted rows into the database. When the long query rolls back, does it also remove the updates/inserts that were made by the other users? or are the rows that involved the other users safe, unless they crossed over with the one that gets rolled back?
推荐答案
您的查询是否影响并发性或与查询类型无关.具有许多读取查询不会影响MyISAM或InnoDB中的并发性(除了性能问题).
Whether your queries affect concurrency or not have to do with the types of queries. Having many read queries won't affect concurrency in MyISAM or InnoDB (besides performance issues).
插入(使用InnoDB到索引的末尾，或者使用MyISAM插入表的末尾)也不会影响并发性.
Inserts (to the end of an index with InnoDB, or the end of a table with MyISAM) also don't impact concurrency.
但是，一旦有了更新查询，行就会在InnoDB中被锁定，而对于MyISAM，整个表都将被写锁定.当您尝试更新具有写锁的记录(或表)时，必须等到锁释放后才能继续.在MyISAM中，更新是在读取之前进行的，因此您必须等待直到更新被处理为止.
However, as soon as you have an update query, rows get locked in InnoDB, and with MyISAM, it's the entire table that gets write locked. When you try to update a record (or table) that has a write lock, you must wait until the lock is released before you can proceed. In MyISAM, updates are served before reads, so you have to wait until the updates are processed.
MyISAM的性能更高，因为表锁比记录锁快(尽管记录锁快).但是，当您开始进行大量更新时，通常首选InnoDB，因为不同的用户通常不太可能争用相同的记录.因此，借助InnoDB，由于记录级别锁定(而不是表锁定)，许多用户可以并行工作而不会互相影响太多.
MyISAM can be more performant because table locks are faster than record locks (though record locks are fast). However, when you start making a significant number of updates, InnoDB is generally preferred because different users are generally not likely to contend for the same records. So, with InnoDB, many users can work in parallel without affecting each other too much, thanks to the record level locking (rather than table locks).
更不用说通过InnoDB获得的完全ACID合规性，强制执行外键约束以及聚集索引的速度.
Not to mention the benefit of full ACID compliance that you get with InnoDB, enforcement of foreign key constraints, and the speed of clustered indexes.
快照(日志条目)保留足够长的时间来完成当前事务，如果快照被回滚或提交，则会被丢弃.事务运行的时间越长，发生其他更新的可能性就越大，这将增加回滚所需的日志条目的数量.
Snapshots (log entries) are kept long enough to complete the current transaction and are discarded if they are rolled back or committed. The longer a transaction runs, the more likely it is that other updates will occur, which grows the number of log entries required to roll back.
由于锁定，不会有交叉"现象.当同一条记录存在写争用时，一个用户必须等待，直到另一位用户提交或回滚为止.
There will be no "cross-over" due to locking. When there is write contention for the same records, one user must wait until the other commits or rolls back.
您可以阅读有关 InnoDB事务模型和锁定.
这篇关于MySQL/InnoDB和长期运行的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！