Suppose I want to insert a new Experiment
in my SQL Server database, using Entity framework 4.0:
Experiment
has 1..* Tasks
in itExperiment
and Task
derive from EntityObject
Task
must have exactly one "parent" Experiment
linked to itInsertion 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,
while(true)
loop all experiments and dumps experiments with no tasks.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.
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