只读DB访问的事务？ [英] Transactions for read-only DB access?

问题描述

关于使用交易从数据库中读取，似乎存在非常不同的意见。

There seem to be very different opinions about using transactions for reading from a database.

从developerWorks文章交易策略：模型和策略概述：

Quote from the DeveloperWorks article Transaction strategies: Models and strategies overview:

为什么你需要一个事务，如果
你只是读取数据？答案
是你不。启动
事务以执行只读
操作会增加
处理线程的开销，并可能导致共享
读取数据库上的锁（取决于
on您使用的是什么类型的数据库
和隔离级别设置
到）。

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

相反的意见有以下引自Hibernate文档的非事务性数据访问和自动提交模式

As a contrary opinion there is the following quote from Hibernate documentation Non-transactional data access and the auto-commit mode

我们的建议是不要在应用程序中使用
自动提交模式，而应用
来应用只读事务只有
当有明显的性能
的好处或以后的代码更改
是不太可能的。总是喜欢
常规ACID事务将
的数据访问操作分组，
无论是否读取或
写入数据。

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.

对EclipseLink邮件列表也有类似的讨论这里。

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?

据我所看到的，如果隔离级别高于read committed '。这是正确的吗？

As far as I can see it only make a difference if the isolation level is higher than 'read committed'. Is this correct?

有哪些经验和建议？

推荐答案

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

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

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

标准SQL要求即使查询必须启动新事务没有交易正在进行中。有DBMS，这不是什么事情 - 那些具有自动提交模式，例如（语句启动一个事务，并立即提交语句完成）。默认情况下，其他DBMS make语句原子（有效地自动提交），但是使用诸如BEGIN WORK之类的语句启动显式事务，取消自动提交，直到下一个COMMIT或ROLLBACK（IBM Informix Dynamic Server是这样的 - 当数据库不是MODE ANSI）。

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

我不确定建议不要回滚。它对于只读事务没有什么区别，并且在它引起DBA的程度上，那么最好避免使用ROLLBACK。但是如果你的程序退出而没有做一个COMMIT，DBMS应该对你不完整的事务做一个ROLLBACK - 当然，如果它修改了数据库，（为了简单）即使你只选择数据。

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.

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

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.

这篇关于只读DB访问的事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！