Transacations with Database.NET

Find working examples on GetHub

DbContext

To understand transactions, I must first explain contexts. A context is essentially a single connection to the database file. Each context contains a cache of entities that have been retrieved or written to the database using it. The context keeps track of changes to entities and manages transactions.


using (var shopContext = new ShopContext(stream))
{
    var pillow = shopContext.Items.CreateProxy();
    pillow.Name = "Pillow";
    pillow.Price = 45;
    shopContext.Items.Add(pillow);  //Start of new transaction         
} //Changes were not saved because the context closed without SaveChanges being called

using (var shopContext = new ShopContext(stream))
{
    var pillow = shopContext.Items.CreateProxy();
    pillow.Name = "Pillow";
    pillow.Price = 45;
    shopContext.Items.Add(pillow);  //Start of new transaction         

    shopContext.SaveChanges(); //Commits Transaction and locks released
}

Changes in one context can block reading and writing in another context until either the first context is closed, or the changes made are written to the database with the SaveChanges method. These types of locks only occur if one context has locked a resource for writing and another context is trying to access that same resource. Once the first context has closed or written the data the second context will continue.

Since a context maintains a cache of entities it can be more efficient to keep the context open for long periods of time or even use the same context for an entire project. This has the benefit of entities being updated without having to reload them and entities no needing to be recreated from the database. Usually, however, it is recommended that contexts be open and closed as needed so that unused resources can be release.

Entities created or retrieved by one context are not the same entities that are created or retrieved by another context. For instance, if you have two contexts open at the same time and the both retrieved the Pillow entity from the Items table; A changed to the Pillow entity in the first context will not be reflected in the Pillow entity of the second context. However, if the first context makes a change and then save that change. All other contexts that retrieve the Pillow entity from that point on will have those changes.

Transactions

Transactions are automatically managed by Database.NET. Whenever you make a change to an entity or an entity is added to a context a transaction is created. When the SaveChanges() method is called the transaction is committed. If the SaveChanges() method is not called the changes are lost.


using (var shopContext = new ShopContext(stream))
{
    var pillow = shopContext.Items.CreateProxy();
    pillow.Name = "Pillow";
    pillow.Price = 45;
    shopContext.Items.Add(pillow);  //Start of new transaction      
        
    var blanket = shopContext.Items.CreateProxy();
    blanket.Name = "Blanket";
    blanket.Price = (decimal)20.5;
    shopContext.Items.Add(blanket); //Still in same transaction

    shopContext.SaveChanges(); //Commits Transaction and locks released
}

When a transaction locks are put in place to keep other threads from making updates to the same resources. Depending upon the changes a lock may only be put on a portion of a table, or it may lock several tables. For this reason, it is important to limit the time between changes and the call to SaveChanges();

Many databases have very complex systems in place to limit locking and prevent race conditions. These are great when a single database is shared between multiple applications or connections. Since Database.Net is not meant to be shared the overhead of creating such a complex locking system has been limited.