Atomically maintaining a counter using Sub-sonic ActiveRecord

Question

I'm trying to figure out the correct way to atomically increment a counter in one table and use that incremented value as an pseudo display-only ID for a record in another.

What I have is a companies table and a jobs table. I want each company to have it's own set of job_numbers. I do have an auto increment job_id, but those numbers are shared across all companies. ie: the job numbers should generally increment without gaps for each company.

ie:

• companies(company_id, next_job_number)
• jobs(company_id, job_id, job_number)

Currently I'm doing this (as a method on the partial job class):

public void SaveJob()
{
    using (var scope = new System.Transactions.TransactionScope())
    {
        if (job_id == 0)
        {
            _db.Update<company>()
                .SetExpression("next_job_number").EqualTo("next_job_number+1")
                .Where<company>(x => x.company_id == company_id)
                .Execute();

            company c = _db.companies.SingleOrDefault(x => x.company_id == company_id);

            job_number = c.next_job_number;
        }

        // Save the job
        this.Save();

        scope.Complete();
    }
}

It seems to work, but I'm not sure if there are pitfalls here? It just feels wrong, but I'm not sure how else to do it.

Any advice appreciated.

Answer 1

First,

you should use the TransactionScope in conjunction with a SharedDbConnectionScope or your transaction won't work as expected.

Second,

I would use another approach with a single statement and without the need to save the job_id with the company)

1. Save the record with job_number = 0

2. Update the record with something like this

   UPDATE JOBS SET JOB_NUMBER = (SELECT MAX(job_number)+1 FROM JOBS WHERE company_id = 12345) WHERE job_id = " + this.job_id;

(You only need to convert this query to subsonic syntax, I don't use subsonic3) That should guarantee that the job number is unique for each company (if you wrap both the save and update command in a transaction and use a table lock).

Answer 2

Using dynamic code to get the current highest value can be risky (potential duplicate values) on a high volume multi user system.

Another approach could be to create a new table with two columns; one a character PK, and the other an integer to store the relevant last value. Each time you want a new value, increment the value in the record for the relevant company and use that value for your Order Number. The character PK would comprise something like:

"LAST_ORDER_NUMBER_COMPANY_" + company_id

I have a Stored Procedure to call which automatically either starts a new record the first time a company has an order, or increments it for subsequent orders, and returns the new order number value back.