什么是ERP的最佳默认事务隔离级别（如果有）？ [英] What is the best default transaction isolation level for an ERP, if any?

问题描述

简短背景：我们刚刚开始使用Hibernate开始将ERP系统迁移/重新实现为Java，目标是使用系统的50-100个用户的并发用户数。我们使用MS SQL Server作为数据库服务器，这对于这个负载是足够好的。

Short background: We are just starting to migrate/reimplement an ERP system to Java with Hibernate, targeting a concurrent user count of 50-100 users using the system. We use MS SQL Server as database server, which is good enough for this loads.

现在，旧系统根本不使用任何事务， （例如库存变化）设置手动锁定（使用标志）和释放它们。这就像手动事务管理。但有时数据不一致的问题。在新系统中，我们希望使用事务消除这些问题。

Now, the old system doesn't use any transactions at all and relies for critical parts (e.g. stock changes) on setting manual locks (using flags) and releasing them. That's something like manual transaction management. But there are sometimes problems with data inconsistency. In the new system we would like to use transactions to wipe out these problems.

现在问题：什么是好的/合理的默认事务隔离级别用于ERP系统，给定大约85％的OLTP和15％OLAP的使用率？

Now the question: What would be a good/reasonable default transaction isolation level to use for an ERP system, given a usage of about 85% OLTP and 15% OLAP? Or should I always decide on a per task basis, which transaction level to use?

并提醒四个事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ ，SERIALIZABLE

And as a reminder the four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

推荐答案

100次中有99次，读取已提交是正确的答案。这确保您只看到由其他会话提交的更改（因此，假设您正确设计了事务，结果是一致的）。但它不强加锁定开销（特别是在非Oracle数据库中）可重复的读取或可序列化强制。

99 times out of 100, read committed is the right answer. That ensures that you only see changes that have been committed by the other session (and, thus, results that are consistent, assuming you've designed your transactions correctly). But it doesn't impose the locking overhead (particularly in non-Oracle databases) that repeatable read or serializable impose.

很偶然，您可能想要运行报告您愿意牺牲速度的准确性并设置读取未提交的隔离级别。这很少是一个好主意，但偶尔也是锁定争用问题的合理可接受的解决方法。

Very occasionally, you may want to run a report where you are willing to sacrifice accuracy for speed and set a read uncommitted isolation level. That's rarely a good idea, but it is occasionally a reasonably acceptable workaround to lock contention issues.

当您有一个需要查看的进程时，偶尔会使用可序列化和可重复读在整个运行期间一致的一组数据，而不管当时正在做什么其他事务。将月末对帐过程设置为可序列化可能是合适的，例如，如果存在大量过程代码，用户将在过程运行时进行更改的可能性，以及需要过程需要请确保始终查看对帐开始时存在的数据。

Serializable and repeatable read are occasionally used when you have a process that needs to see a consistent set of data over the entire run regardless of what other transactions are doing at the time. It may be appropriate to set a month-end reconciliation process to serializable, for example, if there is a lot of procedureal code, a possibility that users are going to be making changes while the process is running and a requirement that the process needs to ensure that it is always seeing the data as it existed at the time the reconciliation started.

这篇关于什么是ERP的最佳默认事务隔离级别（如果有）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！