只读数据库访问的事务？
关于使用事务从数据库读取数据似乎存在非常不同的观点。
引自 DeveloperWorks 文章 事务策略：模型和策略概述：
如果 你只是读取数据？ 答案 是你不这样做。 开始一个 事务执行只读 操作增加了开销 处理线程并可能导致共享 数据库上的读锁（取决于 您正在使用什么类型的数据库 以及设置的隔离级别 到）。
作为相反的观点，Hibernate 文档中引用了以下内容 非事务性数据访问和自动提交模式
我们的建议是不要使用 应用程序中的自动提交模式，以及 仅应用只读事务 当有明显表现时 好处或未来代码更改时 可能性很小。 总是更喜欢 定期进行 ACID 事务分组 您的数据访问操作， 无论你是否阅读或 写入数据。
此处。
那么真相在哪里呢？ 阅读交易是否是最佳实践？ 如果两者都是可行的解决方案，那么使用事务的标准是什么？
据我所知，只有隔离级别高于“已提交读”时才会产生影响。 它是否正确？
有哪些经验和建议？
There seem to be very different opinions about using transactions for reading from a database.
Quote from the DeveloperWorks article Transaction strategies: Models and strategies overview:
Why would you need a transaction if
you are only reading data? The answer
is that you don't. Starting a
transaction to perform a read-only
operation adds to the overhead of the
processing thread and can cause shared
read locks on the database (depending
on what type of database you are using
and what the isolation level is set
to).
As a contrary opinion there is the following quote from Hibernate documentation Non-transactional data access and the auto-commit mode
Our recommendation is to not use the
autocommit mode in an application, and
to apply read-only transactions only
when there is an obvious performance
benefit or when future code changes
are highly unlikely. Always prefer
regular ACID transactions to group
your data-access operations,
regardless of whether you read or
write data.
There is also a similar debate on the EclipseLink mailing list here.
So where lies the truth? Are transactions for reading best-practice or not? If both are viable solutions, what are the criteria for using transactions?
As far as I can see it only make a difference if the isolation level is higher than 'read committed'. Is this correct?
What are the experiences and recommendations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Steven Devijver 提供了一些启动事务的充分理由，即使操作只是读取数据库：
标准 SQL 要求，如果当前没有正在进行的事务，则查询也必须启动新事务。 有些 DBMS 不会发生这种情况 - 例如，那些具有自动提交模式的 DBMS（语句启动一个事务并在语句完成后立即提交它）。 其他 DBMS 默认情况下使语句原子化（有效地自动提交），但使用诸如“BEGIN WORK”之类的语句启动显式事务，取消自动提交，直到下一次 COMMIT 或 ROLLBACK（IBM Informix Dynamic Server 就是这样一个 - 当数据库不是 MODE 时）美国标准协会）。
我不确定永远不要回滚的建议。 它对只读事务没有影响，如果它让 DBA 烦恼，那么最好避免 ROLLBACK。 但是，如果您的程序在没有执行 COMMIT 的情况下退出，则 DBMS 应该对您不完整的事务执行 ROLLBACK - 当然，如果它修改了数据库，并且（为了简单起见）即使您只选择了数据。
总体而言，如果要更改一系列操作的默认行为，请使用事务，即使该事务是只读的。 如果您对默认行为感到满意，那么使用事务并不重要。 如果您的代码要在 DBMS 之间移植，最好假设您需要事务。
Steven Devijver provided some good reasons for starting transactions even if the operations are only going read the database:
Standard SQL requires that even a query must start a new transaction if there is no transaction currently in progress. There are DBMS where that is not what happens - those with an autocommit mode, for example (the statement starts a transaction and commits it immediately the statement completes). Other DBMS make statements atomic (effectively autocommit) by default, but start an explicit transaction with a statement such as 'BEGIN WORK', cancelling autocommit until the next COMMIT or ROLLBACK (IBM Informix Dynamic Server is one such - when the database is not MODE ANSI).
I'm not sure about the advice never to rollback. It makes no difference to the read-only transaction, and to the extent it annoys your DBAs, then it is better to avoid ROLLBACK. But if your program exits without doing a COMMIT, the DBMS should do a ROLLBACK on your incomplete transaction - certainly if it modified the database, and (for simplicity) even if you only selected data.
Overall, if you want to change the default behaviour of a series of operations, use a transaction, even if the transaction is read-only. If you are satisfied with the default behaviour, then it is not crucial to use a transaction. If your code is to be portable between DBMS, it is best to assume that you will need a transaction.
首先，这听起来像是一个不成熟的优化。 正如史蒂文指出的那样，大多数理智的数据库无论如何都会让你进入一个事务，而它们真正做的就是在每个语句之后调用提交。 因此从这个角度来看，自动提交的性能可能会较低，因为每个语句都必须启动一个新事务。 或者可能不是。 只有基准测试才能说明问题，我敢打赌它不会对您的应用程序产生丝毫影响。
您想要始终使用事务的原因之一是保护的一致性。 如果您仅在“需要”时才开始手动声明交易，那么您将在关键时刻忘记。 或者，所谓的只读操作集突然不再是只读的，要么是因为后来的程序员没有意识到它应该是，要么是因为您的代码调用了一个具有隐藏写入的函数。 例如，我将命令行数据库客户端配置为不自动提交。 这意味着我可以粗略地执行删除查询并仍然回滚。
正如所指出的，存在隔离级别。 这允许您进行多次读取，而不必担心其他进程是否在它们之间写入了您的数据，从而使您的读取有效地原子化。 这将为您节省许多小时调试竞争条件的时间。
最后，您通常可以将事务设置为只读。 这会检查您的假设，如果尝试写入，则会出错。
这是一篇很好的文章，总结了一切。 详细信息是 Oracle 特定的，但概念是通用的。
First off, this sounds like a premature optimization. As Steven pointed out, most sane databases are going to put you into a transaction anyway, and all they're really doing is calling commit after each statement. So from that perspective, autocommit might be less performant since each statement has to start a new transaction. Or maybe not. Only benchmarking will tell and I bet it doesn't make one lick of difference to your application.
One reason why you want to always use a transaction is consistency of protection. If you start fiddling with manually declaring a transaction only when you "need" then then you're going to forget at a critical time. Or that supposedly read-only set of operations suddenly isn't, either because a later programmer didn't realize it was supposed to be or because your code calls a function which has a hidden write. For example, I configure my command line database clients not to autocommit. This means I can fat finger a delete query and still rollback.
There's the isolation level, as pointed out. This allows you to do several reads without worrying if some other process has written to your data in between them making your reads effectively atomic. This will save you from many an hour debugging a race condition.
And, finally, you can often set a transaction to be read-only. This checks your assumption and will error out if something tries to write.
Here's a nice article summing it all up. The details are Oracle specific, but the concepts are generic.
如果要为查询设置除默认超时之外的特定超时，或者如果要更改隔离级别，则只读操作需要事务。
此外，每个数据库（不知道异常）都会在内部为每个查询启动一个事务。 当不需要回滚时，通常认为不进行回滚事务。
DBA 可能正在监视回滚活动，在这种情况下任何默认的回滚行为都会惹恼他们。
因此，无论您是否启动事务，事务都会被使用。 如果您不需要它们，请不要启动它们，但切勿回滚只读操作。
Transaction are required for read-only operations if you want to set a specific timeout for queries other than the default timeout, or if you want to change the isolation level.
Also, every database - don't know about exceptions - will internally start a transaction for each query. It's general considered not done to rollback transactions when that rollback is not required.
DBA's may be monitoring rollback activity, and any default rollback behavior will annoy them in that case.
So, transactions are used anyway whether you start them or not. If you don't need them don't start them, but never do a rollback on read-only operations.