PostgreSQL + Rails 并发澄清
我正在构建一个后台作业来更新 Web 应用程序的用户统计信息。该作业目前需要 55-60 秒，我担心如果用户在该作业运行的同时尝试加载他的统计页面会发生什么。
根据我所读到的有关 PostgreSQL 和并发性的内容，如果两个客户端尝试访问同一行（一个更新，一个读取），并且我没有显式启动任何事务，第一个客户端只需等待第二个客户端结束。
因此，如果我理解正确的话，我可能遭受的唯一性能影响是用户尝试在更新行的同时加载其统计页面的可能性极小。除非我明确配置 Postgres 来执行此操作，否则整个统计表不会在 55-60 秒的工作期间被锁定，对吧？
这是正确的解释吗？还有其他我遗漏的因素吗？
（我提到 Rails 部分只是为了防止它与上述场景有任何关系）
（另外：PostgreSQL 版本是 9.0.4）
I'm building a background job that's updating users' statistics for a web application. The job currently takes 55-60 seconds, and I'm concerned about what would happen if a user were to try to load his stats page at the same time that job is running.
From what I've read about PostgreSQL and concurrency, if two clients attempt to access the same row (one updating and one reading), and I'm not explicitly starting any transactions, the first one just has to wait for the second one to finish.
So if I'm understanding that correctly, the only performance hit I'm likely to incur is on the infinitesimally small chance that a user tries to load his stats page at the same moment that the row is being updated. It's not like the whole stats table is locked up during the 55-60 second job unless I were to explicitly configure Postgres to do that, right?
Is that a correct interpretation? Are there other factors I'm missing?
(I mention the Rails part just in case it has any bearing on the above scenario)
(Also: the PostgreSQL version is 9.0.4)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这取决于事务隔离级别。如果我有你的情况 - 你正在谈论脏读以避免延迟。是的，如果您使用默认隔离级别，脏读是不可能的。仅当读取器尝试获取正在更新的同一行时，它才会等待写入器。
隔离规范
It depends on transaction isolation level. If I've got your case - you are talking about Dirty Read avoiding delay. And YES, Dirty Read is impossible if you are using default isolation level. Reader will wait for the writer only when it will try to get the same row that is being updated.
specs on ISOLATION