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

本文是小编为大家收集整理的关于如果有的话,ERP的最佳默认交易隔离级别是什么?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

简短的背景:我们刚刚开始使用Hibernate迁移/重新实现ERP系统到Java,以使用该系统的50-100用户为目标.我们将MS SQL Server用作数据库服务器,这足以适合此加载.

现在,旧系统根本不使用任何交易,并且在设置手动锁(使用标志)并释放它们时依赖关键零件(例如库存更改).这就像手动交易管理一样.但是有时数据不一致存在问题.在新系统中,我们想使用交易来消除这些问题.

现在的问题:考虑到大约85%的OLTP和15%的OLAP,默认默认交易隔离水平是什么?还是我应该始终决定使用哪个交易级别?

并提醒您四个交易隔离级别:读取,读取,读取,可重复读取,可序列化

推荐答案

99次100次,读取是正确的答案.这可以确保您只看到另一个会话所做的更改(因此,假设您正确设计了交易的结果).但这并没有强加可重复读取或可序列化的锁定开销(尤其是在非奥科克斯数据库中).

偶尔,您可能需要运行一份报告,您愿意为速度牺牲准确性并设置读取的隔离级别.这很少是一个好主意,但是锁定争夺问题有时是可以接受的解决方法.

可序列化且可重复的读取偶尔会在您有一个过程中需要在整个运行中查看一致的数据集时,无论当时其他交易正在做什么.例如,将一个月末端的对帐过程设置为可序列化,例如,如果有很多程序代码,则用户可能会在运行该过程时进行更改,并且需要该过程需要进行更改确保它始终看到和解开始时存在的数据.

其他推荐答案

不要忘记快照,它在可序列化的下方.

这取决于数据中数据准确的重要性.这确实是任务任务的事情.

其他推荐答案

这确实取决于您如何设计应用程序,简单的答案只是在read_commit上运行.

您可以提出一个论点,即如果您牢记系统设计系统,则可以将read_uncommits用作默认值,并且只有在需要时增加隔离级别.无论如何,您的绝大多数交易都将取得成功,因此阅读无资产的数据不会很重要.

隔离级别效果您的查询取决于目标数据库的方式.例如,与Oracle这样的数据库,例如Sybase和MSSQL等数据库必须锁定更多的资源.

.

本文地址:https://www.itbaoku.cn/post/597659.html

问题描述

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.

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?

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

推荐答案

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.

其他推荐答案

Don't forget about SNAPSHOT, which is right below SERIALIZABLE.

It depends on how important it is for the data to be accurate in the reports. It really is a task-by-task thing.

其他推荐答案

It really depends a lot on how you design your application, the easy answer is just run at READ_COMMITTED.

You can make an argument that if you design your system with it in mind that you could use READ_UNCOMMITTED as the default and only increase the isolation level when you need it. The vast majority of your transactions are going to succeed anyway so reading uncommitted data won't be a big deal.

The way isolation levels effect your queries depends on your target database. For instance databases like Sybase and MSSQL must lock more resources when you run READ_COMMITTED, than databases like Oracle.