Database Transactions & EntityFramework

A.Database-Transactions.cs

    public class Program
    {
        private static TestingContext Context { get; set; }

        public static void Main(string[] args)
        {
            /*
             * A database transaction is a unit of work that represents a change in the database and is treated in a 
             * coherent and reliable way independent of other transactions. 
             * 
             * Transactions should be used when you have to perform a set of changes to the database and would like to 
             * guarantee that all the changes will either succeed or fail as a single unit of work.
             * 
             * A database transaction, by definition, must be atomic, consistent, isolated and durable (ACID).
             * 
             * -Atomic: An atomic transaction is an indivisible and irreducible series of database operations such that 
             * either all occur, or nothing occurs.
             * 
             * -Consistent: Any data written to the database must be valid according to all defined rules, including 
             * constraints, triggers, etc.
             * 
             * -Isolated: In database systems, isolation determines how transaction integrity is visible to other systems. 
             * For example, when a user is creating a Purchase Order and has created the header, but not the Purchase 
             * Order lines, is the header available for other systems to see?
             * 
             * -Durable: In database systems, durability is the ACID property which guarantees that transactions that 
             * have committed will survive permanently. For example, if a flight booking reports that a seat has 
             * successfully been booked, then the seat will remain booked even if the system crashes.
             *
             *
             * Isolation levels: 
             * 
             * A lower isolation level increases the ability of many users to access the same data at the same time, 
             * but increases the number of concurrency effects (such as dirty reads or lost updates) users might 
             * encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users may 
             * encounter, but requires more system resources and increases the chances that one transaction will block 
             * another.
             *  
             * Most DBMSs offer a number of transaction isolation levels, which control the degree of locking that 
             * occurs when selecting data. For many database applications, the majority of database transactions can 
             * be constructed to avoid requiring high isolation levels (e.g. SERIALIZABLE level), thus reducing the 
             * locking overhead for the system. The programmer must carefully analyze database access code to ensure 
             * that any relaxation of isolation does not cause software bugs that are difficult to find. Conversely, 
             * if higher isolation levels are used, the possibility of deadlock is increased, which also requires 
             * careful analysis and programming techniques to avoid.
             * 
             * To understand the isolation levels, let's refer to the following example:
             * 
             * BEGIN TRANSACTION;
             * SELECT * FROM T;
             * WAITFOR DELAY '00:01:00'
             * SELECT * FROM T;
             * COMMIT;
             * 
             * -Read Uncommitted: This is the lowest isolation level. In this level, dirty reads are allowed, so one 
             * transaction may see not-yet-committed changes made by other transactions.
             * 
             * -Read Committed: An isolation level that guarantees that any data read is committed at the moment it is 
             * being read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It 
             * makes no promise whatsoever that if the transaction re-issues the read, it will find the same data; data 
             * is free to change after it is read, therefore, the second SELECT statement might return different data
             * then the first statement.
             * 
             * -Repeatable Reads: In this isolation level, the database keeps read and write locks (acquired on 
             * selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads 
             * can occur. Therefore, the second SELECT is guaranteed to read the rows that were read at the first SELECT 
             * statement unchanged. New rows may be added by a concurrent transaction in that one minute (phantom rows), 
             * but the existing rows cannot be deleted or changed.
             * 
             * -Serializable: The highest isolation level. Serializability requires read and write locks (acquired on 
             * selected data) to be released at the end of the transaction. Also range-locks must be acquired when a 
             * SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon. Therefore, 
             * the second SELECT is guaranteed to read exactly the same rows as the first. No row can change, nor 
             * get deleted, and new rows could not be inserted by a concurrent transaction. 
             * 
             * 
             * Note that the default isolation level for SQL Server is Read Committed.
             * 
             * -------------------------------------------------------------------------------------------------------
             * 
             * Snapshot isolation:
             * 
             * Snapshot isolation is a guarantee that all reads made in a transaction will see a consistent snapshot 
             * of the database (in practice it reads the last committed values that existed at the time it started), 
             * and the transaction itself will successfully commit only if no updates it has made conflict with any 
             * concurrent updates made since that snapshot.
             */

            using (var transaction = Context.GetTransaction())
            {
                var reservation1 = Context.Reservations.FirstOrDefault(x => x.ReservationId == 4389951);
                //At this point, if the transaction's isolation level is Repeatable Read or above, then no other
                //concurrent transactions will be able to modify this row.

                if (reservation1 != null)
                    reservation1.ReservationKey = $"{Guid.NewGuid()}";

                var reservation2 = Context.Reservations.FirstOrDefault(x => x.ReservationId == 4389955);

                if (reservation2 != null)
                    reservation2.ReservationKey = $"{Guid.NewGuid()}";

                Context.SaveChanges();
                //At this point, all other concurrent transactions configured with an isolation level higher than
                //Read Uncommitted won't be able to read the two rows above

                try
                {
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback(); //If any change fails, the whole transaction will rollback
                }
            }

            /*
             * Concurrency has the ability to introduce deadlocks, deadlocks are most commonly associated with database
             * transactions.
             * 
             * Transaction 1:
             * 
             * UPDATE Table1 --Step1: Table1 locked till the transaction commits
             * UPDATE Table2 --Step3: Table2 is locked, therefore the transaction will block
             * COMMIT
             * 
             * 
             * Transaction 2:
             * UPDATE Table2 --Step2: Table2 locked till the transaction commits
             * UPDATE Table1 --Step4: Table1 is locked, threfore the transaction will block (deadlock)
             * COMMIT
             * 
             * It is advisable to not overuse transactions and in case needed then it is better to keep all read 
             * statements outside the scope of the transaction whenever applicable and the transaction should be
             * as efficient as possible.
             */
        }

        static Program()
        {
            Context = new TestingContext();
        }
    }

B.TestingContext.cs

    public class TestingContext : DbContext
    {
        public TestingContext() : base("Testing")
        {
            
        }

        public DbContextTransaction GetTransaction()
        {
            return Database.BeginTransaction();
        }

        public DbContextTransaction GetSerializableTransaction()
        {
            return Database.BeginTransaction(IsolationLevel.Serializable);
        }

        public DbContextTransaction GetRepeatableReadTransaction()
        {
            return Database.BeginTransaction(IsolationLevel.RepeatableRead);
        }

        public DbContextTransaction GetReadUncommittedTransaction()
        {
            return Database.BeginTransaction(IsolationLevel.ReadUncommitted);
        }

        public IDbSet<Reservation> Reservations { get; set; }
    }

C.Reservation.cs

    public class Reservation
    {
        public long ReservationId { get; set; }
        public string ReservationKey { get; set; }
    }