Add object and its relationships atomically in SQL Server database

Question

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

Velarde

Answer 1

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

Velarde

Answer 2

The following should actually perform what you are after assuming you are not reading with READ UNCOMMITTED or similar isolation levels

using(var ctx = new MyContext())
{
    var task = new Task{};
    ctx.Tasks.Add(task);
    ctx.Experiment.Add(new Experiment{ Task = task });
    ctx.SaveChanges();
}

If you are using READ UNCOMMITTED or similar in this case the task will show up before the Experiment is added, I don't believe there should ever be a state where the Experiment can exist before the task given the constraint you have described.

Sumatra

This produces errors because of the constraint that each task must refer an experiment. – Velarde

@Velarde have you got that constraint enforced by ef? If so this should work without issue. – Sumatra

You're right, the constraints does not hurt as it's in EF. However, it does not solve my issue. I assume that by default data is read with "Read commited" isolation level. – Velarde

BTW I edited my post to add link on unanswered similar question based on unit test. – Velarde

@sithers the default level is normally set at the server level. I dont think EF explicitly specifies an isolation level (unless you set it yourself). It might be worth taking a look at what your database is set to. Run DBCC useroptions in SSMS for your DB and it will tell you what you are running. I would also sugguest running this as the same user as you are using to connect to SQL from your app – Sumatra

Thanks for your help Luke. We found an option on server which solves the issue: "Is Read Commited Snapshot On" (still don't really understand why). Jeff Atwood has a post about it. codinghorror.com/blog/2008/08/deadlocked.html – Velarde