Transaction with Snapshot isolation gives inconsistent result
<p>Environment: SQL Server 2012</p>

<p>Backend: C# + EntityFramework</p>

<p>We use Change Tracking to obtain changes from multiple tables. All the operations are done inside one transaction with Snapshot isolation level. All queries inside that transaction are read-only.</p>

<pre><code>using (var context = new BackendContext())
                {
                    using (var transaction = context.Database.BeginTransaction(IsolationLevel.Snapshot))
                    {
                        try
                        {
                            int count1= context.Reservation.Count();

                            //Then several read only operations to get changes from Chnage Tracking
                            ...

                            int count2= context.Reservation.Count();

                            if(count1 != count2)
                            {
                                throw new Exception("Inconsistent state");
                            }

                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
</code></pre>

<p>So the first weird thing is that if we count number of rows in the table before processing of changes and after then sometimes we get different results. According to the MSDN: SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. But here inside one transaction with snapshot we get two different results.</p>

<p>Another major issue happens along with the one i just described above.</p>

<p>Inside that transaction we obtain changes from multiple tables. </p>

<p>Let's say we have Table1, Table2 and Junction table where we have one to many relationship so T1 row might have multiple T2 relations.</p>

<p>Sometimes it happens that we don't get changes from Table1 but we do have changes from Table2 with entries linked to Table1.
Again, everything happens inside one transaction with Snapshot isolation.</p>

<p>I hope i was clear enough.</p>