在SQL Server数据库中以原子方式添加对象及其关系 [英] Add object and its relationships atomically in SQL Server database

问题描述

假设我想在我的SQL Server数据库中插入一个新的实验，使用实体框架4.0：

• 实验有1 .. * 任务中


• 实验和任务派生自 EntityObject / li>
  
• 此外，还有一个数据库约束，每个任务必须只有一个父实验链接到它

插入必须是原子。我的意思是原子的是数据库上的读者永远无法读取不完全写入数据库的实验，例如实验没有任务。

我迄今尝试的所有解决方案都有一些问题，不完整的实验可以读取，即使这只持续了几秒钟;即实验终于快速地填入其任务，但不是以原子方式填充。

更具体地说，

• 我的reader.exe读入 while（true）循环所有的实验和转储没有任务的实验。


• 并行我的 writer.exe 写一个1000个实验，一个一个，全部都有一个任务，并将它们保存到数据库。

我找不到写我的 ReadAllExperiments 和 WriteOneExperiment 函数，以便我从未读过不完整的实验。

我应该怎么做？

PS：

我是数据库的新手;我尝试使用可序列化隔离级别的交易，使用UPDLOCK等读取手动SQL请求，但没有成功解决这个问题，所以我被卡住了。

我认为是一个基本和容易的需求可能会揭示出不正确的问题？

问题是在这里进行单元测试：
实体框架代码首先：SaveChanges不是原子的

解决方案

解决我们的问题。

1. 数据库选项Is Read Commited Snapshot On= True（默认为false）


2. 数据库选项允许快照隔离= True +使用快照隔离级别完成读取。我们以前尝试过使用快照隔离读取，但不知道此db选项。我仍然不明白为什么我们在阅读禁用隔离级别时不会收到错误？

有关 http://www.codinghorror.com/blog/2008/08/deadlocked.html
或

MSDN： http://msdn.microsoft.com/en-us/library/ms173763.aspx （搜索READ_COMMITTED_SNAPSHOT）

http://msdn.microsoft.com/en-us/ library / ms179599％28v = sql.105％29.aspx

Suppose I want to insert a new Experiment in my SQL Server database, using Entity framework 4.0:

• Experiment has 1..* Tasks in it
• Both Experiment and Task derive from EntityObject
• Also, there is a database constraint that each Task must have exactly one "parent" Experiment linked to it

Insertion must be atomic. What I mean by atomic is that a reader on database must never be able to read an Experiment which is not fully written to database, for instance an Experiment with no Task.

All solutions I tried so far have the issue that some incomplete experiments can be read even though this lasts only a few seconds; i.e. the experiment finally gets populated with its Task quickly but not atomically.

More specifically,

• my reader.exe reads in while(true) loop all experiments and dumps experiments with no tasks.
• In parallel my writer.exe write ~1000 experiments, one by one, all with one task, and save them to database.

I cannot find a way to write my ReadAllExperiments and WriteOneExperiment functions so that I never read incomplete experiment.

How I am supposed to do that?

PS:

I'm a newbie to databases; I tried transactions with serializable isolation level on write, manual SQL requests for reading with UPDLOCK, etc. but did not succeed in solving this problem, so I'm stuck.

What I thought to be quite a basic and easy need might reveal to be ill-posed problem?

Issue is unit tested here: Entity Framework Code First: SaveChanges is not atomic

解决方案

2 solutions apparently solve our issues.

1. The database option "Is Read Commited Snapshot On"=True (By default, it's false)
2. The database option "Allow Snapshot isolation"=True + read done using snapshot isolation level. We tried the read using snapshot isolation before, but did not know about this db option. I still do not understand why we don't get an error when reading with disabled isolation level?

More information on http://www.codinghorror.com/blog/2008/08/deadlocked.html or on

MSDN: http://msdn.microsoft.com/en-us/library/ms173763.aspx (search for READ_COMMITTED_SNAPSHOT)

http://msdn.microsoft.com/en-us/library/ms179599%28v=sql.105%29.aspx

这篇关于在SQL Server数据库中以原子方式添加对象及其关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！