SQL server version column updates more than it should

Question

We're using NHibernate to update an account table with balance for a user. One test case that stymies us is this:

var s = NHibernateHelper.OpenSession();
var q = s.CreateQuery("Update Account set Balance=? where UserId=? and Version=?");
var acc = s.Load(1);
ITransaction tx = null;
for (int j = 0; j < NUM_UPDATES; j++)
{
    int rowcount = 0;
    var tx = s.BeginTransaction();
    do
    {
        s.Refresh(acc);
        q.SetDouble(0, acc.Balance + _amount).SetInt32(1, 1).SetBinary(2, acc.Version);
        rowcount = q.ExecuteUpdate();
    } while (rowcount <= 0);
    tx.Commit();
}

this code is being executed both in a single thread and in multiple threads concurrently (hence the versioning). it executes correctly, but - the version number is incremented by a number which is greater than the actual update count (NUM_UPDATES) by a some value.

e.g. if we make 16 updates, the version number is incremented by 16 or 17. if we make 1000 updates, the version number is incremented by 1004.

If 16 threads run this same code (on a 4-core machine) with 1000 updates each, then we get a lot of retries obviously, and the version is incremented by 16064 (1004 * 16).

Any idea what's causing this? Is there a cause for alarm?

Answer 1

If the version column is of type timestamp or rowversion, then it is updated using a global counter. That global counter is incremented whenever any row in any table containing a rowversion column is inserted or updated. So it is not a cause for concern. It merely reflects other activity within the database.

Answer 2

Read here more about rowvwersion (timestamp) in SQL server. Things to know:

• Has nothing to do with time; other than the bigger number was generated later.
• The counter is database-global.
• It is auto-changed, you do not change it.
• Can be used in queries to detect data change, during sync, etc..

Answer 3

Since you're building your own queries, you can atomically increment a column in an UPDATE:

UPDATE Account SET Balance = Balance + @amount WHERE UserId = @uid

That will also save the overhead of creating and committing a separate transaction.