Atomically maintaining a counter using Sub-sonic ActiveRecord
- by cantabilesoftware
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.
