Despite transaction fails, Identity column gets incremented

Topics: Troubleshooting, Writing modules
Sep 29, 2011 at 11:28 AM

Hi all,

yesterday I experienced the following:

An exception was thrown in a controller, in the middle of a long series of database-handling method calls. As far as I know Orchard handles all page requests as transactions: indeed, none of the changes made to the database was committed. Almost none, as it seems that the Id column of the records next time started not from the last value, but from the last value + number of rows successfully inserted before the exception. Could be this some flaw with transaction handling?

Sep 29, 2011 at 5:31 PM

It would be very surprising but it could. Do you think you could build a reliable repro of this?

Sep 29, 2011 at 6:45 PM

I have the code that caused that at hand, so I will extract the necessary parts and try it again.

Sep 29, 2011 at 7:15 PM

OK, this definitely happens. Repro:


            var c1 = _contentManager.New<MyPart>("MyType");
            c1.Label = "lbl1";
            var c2 = _contentManager.New<MyPart>("MyType");
            c2.Label = "lbl2";
            var c3 = _contentManager.New<MyPart>("MyType");
            c3.Label = "lbl3";
            throw new ApplicationException("yay");
            var c4 = _contentManager.New<MyPart>("MyType");
            c4.Label = "lbl4";
            var c5 = _contentManager.New<MyPart>("MyType");
            c5.Label = "lbl5";

Note the exception between the third and fourth content creation.

Result: no item is inserted into the database. Last record in MyPart's table have the id 203. However if I run the above code again with removing the exception, the result is as following: the five new records are inserted, but the first (marked with "lbl1" has the Id 207. It seems that the Ids 204, 205 and 206 where allocated when creating the first three content parts (and records) before the exception was thrown.


Sep 29, 2011 at 7:24 PM

Can you please put that into a bug? Thanks for your help.

Sep 29, 2011 at 7:28 PM

I have done it here.

Sep 29, 2011 at 7:32 PM


Oct 4, 2011 at 3:10 PM

I suppose you're aware that, in sql server, identity column increment is lost when rollbacking a transaction.

So, from an sql point of view, it's expected to have gaps between increments in an identity column.

BOL Reference :

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Oct 5, 2011 at 4:02 PM

Thanks, I wasn't aware, however this is not a failure with SQL Server: here an application exception is thrown and the Orchard (NHibernate) transaction should be rolled back. I don't know how this is implemented (I suppose data shouldn't even get to the SQL Server), so indeed your idea can be the solution.

Oct 5, 2011 at 4:10 PM
Edited Oct 5, 2011 at 4:10 PM

No, it's not possible : NHibernate rely on sql transaction : when orchard/nhibernate starts a transaction, it still sends sql commands to the database. The database is simply aware that a transaction is active (it receive a "begin transaction" when the transaction scope was opened), and wait for a commit/rollback to actually commit/rollback the changes. But the identity value is still lost.

The only solution (adopted by most developers which have to implement a real increment without gaps) is to create another strategy to create unique ids. It's often based on a procstoc combined with a db table (like sequences in oracle).

Oct 5, 2011 at 4:14 PM

Then there's no problem at all. Thanks for clearing that up.