Atomically maintaining a counter using Sub-sonic ActiveRecord

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_开发者_JAVA技巧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.

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).

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.