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