WARNING:  Use the code supplied here like beta software.  Testing is still ongoing as of January 2011.

What is SubSonic?

  • SubSonic is a free Data Access Layer generator that you can use in .Net programming.  It's a favorite of developers because it is clean, simple and lightweight when compared to other DAL solutions out there, and there are many.  
Why have I modified Subsonic to use ADO Transactions instead of  System.Transactions.TransactionScope which Subsonic provides out of the box?
  • I have had problems getting System.Transactions.TransactionScope to work reliably without having the Distributed Transaction Coordinator service running.
  • Many of my clients believe ADO Transactions perform better than System.Transactions.TransactionScope.
What is Optimistic Locking / Optimistic Concurrency
  • (quoth Wikipedia) optimistic concurrency control (OCC) is a concurrency control method that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back,
User Beware
  • Please treat this like beta software.  I mean Microsoft beta, not Google beta.  I have not thoroughly tested this technique.  At all.
  • I have assumed the database is Microsoft SQL Server in this code.  It should be very easy to adapt to other databases, but the specific code included here will definitely not work on anything but Microsoft SQL Server.
  • If you find a problem with this solution, please use the comments form below to send me an email.  If you have a Google account, you can also log in using the link at the bottom of the page, and post your questions/comments here.   I appreciate any feedback.
  • If you have skipped all of the text above and jumped right to the substance, I salute you because that means you already have a working knowledge of SubSonic and Quakers.  But I hope you'll scroll up a bit and tip your hat to the gentleman Quaker above.  He would do the same for you.
  • These instructions assume you already have an understanding of how to set up SubSonic.  If not, you'[ll need to start here http://subsonicproject.com/docs/Using_ActiveRecord.
  • Download the zip file found here: http://sites.google.com/site/subsonicoptimistic/downloads
  • The download contains all the files necessary in order to include my modified SubSonic 3.0 ActiveRecord in an application.
  • The ActiveRecord.tt code generation template will look for a field called "RowVer" of type "Timestamp" on your tables.  If it finds it, it will enforce optimistic locking/concurrency.  If this field is not found no change is made from the original behavior.
  • If you'd like to see what's going on, open up ActiveRecord.tt and search for "RowVer".  It should be pretty obvious what to change if you'd like to use a different field name for your timestamp, or a different data type.
What do I have to do to get Optmistic Locking working?
  • Include a field called "RowVer" of type "Timestamp" on any tables that need to have optimistic locking enforced.  Make the field non-nullable.
How do I wrap statements in a transaction?

The code I've added needs to know whether it's being used on a web application or a WinForms application.  By default it will assume it's a web application.  If the application is not a web application, you'll need to add a value in your app.config like this one:

    <add  key="AppType" value="Forms"/>

Once this is done, wrapping any statements that need to be transactional with SubSonic's "SharedDbConnectionScope" should make them transactional:

                using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope())
                    LockingTest rec = LockingTest.Find(lt => lt.FirstName == "Bruce").ToList()[0];

                    rec.FirstName = "BruceX";


                    // Re-pull from the db, so we get the new timestamp
                    rec = LockingTest.Find(lt => lt.FirstName == "BruceX").ToList()[0];

                    rec.FirstName = "BruceY";


            // Commit the transaction.  Any errors above that cause this
            //  line of code not to be hit will result in a rollback of both
            //  updates

Question and Comments