只读数据库访问的事务? [英] 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:

如果你需要一个交易你只看数据?答案是你没有.开始一个事务执行只读操作增加了开销处理线程并可能导致共享数据库上的读锁(取决于您使用的数据库类型以及设置的隔离级别到).

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 文档中引用了以下相反的观点 Non-transactional data access and the auto-commit模式

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?

据我所知，只有在隔离级别高于已提交读"时才会有所作为.这是正确的吗?

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 使语句具有原子性(有效地自动提交)，但使用诸如开始工作"之类的语句启动显式事务，在下一次提交或回滚之前取消自动提交(IBM Informix 动态服务器就是这样 - 当数据库不是 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.

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