database 只读数据库访问的事务？

Transactions for read-only DB access?

提问by jbandi

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:

引自 DeveloperWorks 文章交易策略：模型和策略概述：

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

作为相反的观点，以下引用来自 Hibernate 文档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.

我们的建议是不要在应用程序中使用自动提交模式，并且仅在有明显的性能优势或未来代码更改的可能性很小时才应用只读事务。无论您是读取数据还是写入数据，始终倾向于使用常规 ACID 事务来对数据访问操作进行分组。

There is also a similar debate on the EclipseLink mailing list here.

也有EclipseLink的邮件列表上一个类似的争论在这里。

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?

有哪些经验和建议？

采纳答案by Jonathan Leffler

Steven Devijver provided some good reasons for starting transactions even if the operations are only going read the database:

即使操作只是读取数据库，Steven Devijver 也为启动事务提供了一些很好的理由：

• Set timeouts or lock modes
• Set isolation level

• 设置超时或锁定模式
• 设置隔离级别

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

如果当前没有正在进行的事务，标准 SQL 要求即使是查询也必须启动一个新事务。有些 DBMS 不会发生这种情况——例如，那些具有自动提交模式的数据库管理系统（语句启动事务并在语句完成后立即提交）。默认情况下，其他 DBMS 将语句设为原子（有效地自动提交），但使用诸如“开始工作”之类的语句启动显式事务，取消自动提交直到下一次提交或回滚（IBM Informix 动态服务器就是这样 - 当数据库不是 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.

我不确定永远不要回滚的建议。它对只读事务没有任何影响，并且在一定程度上惹恼了您的 DBA，那么最好避免 ROLLBACK。但是，如果您的程序在没有执行 COMMIT 的情况下退出，那么 DBMS 应该对您未完成的事务执行 ROLLBACK - 当然如果它修改了数据库，并且（为简单起见）即使您只选择了数据。

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.

总体而言，如果要更改一系列操作的默认行为，请使用事务，即使该事务是只读的。如果您对默认行为感到满意，那么使用事务并不重要。如果您的代码要在 DBMS 之间移植，最好假设您需要一个事务。

回答by Schwern

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.

首先，这听起来像是过早的优化。正如史蒂文指出的那样，大多数健全的数据库无论如何都会让你进入一个事务，他们真正做的就是在每个语句之后调用 commit 。因此，从这个角度来看，自动提交的性能可能会降低，因为每个语句都必须启动一个新事务。或者可能不是。只有基准测试才能说明问题，我敢打赌它不会对您的应用程序产生任何影响。

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.

这里有一篇很好的文章总结了这一切。细节是特定于 Oracle 的，但概念是通用的。

回答by Steven Devijver

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.

DBA 可能正在监视回滚活动，在这种情况下，任何默认的回滚行为都会惹恼他们。

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.

因此，无论您是否启动交易，都会使用交易。如果您不需要它们，请不要启动它们，但永远不要对只读操作进行回滚。