使用 Sub-sonic ActiveRecord 以原子方式维护计数器
我试图找出正确的方法来自动增加一个表中的计数器，并使用该增加的值作为另一个表中记录的伪仅显示 ID。
我有一个公司表和一个工作表。我希望每个公司都有自己的一组 job_numbers。我确实有一个自动递增的 job_id，但这些数字在所有公司之间共享。即：每个公司的职位数量通常应该无间隙地增加。
即：
- 公司（company_id，next_job_number）
- 工作（company_id，job_id，job_number）
目前我正在这样做（作为部分工作类的方法）：
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();
}
}
它似乎有效，但我不确定这里是否有陷阱？只是感觉不对，但我不知道还能怎么做。
任何建议表示赞赏。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，
您应该将 TransactionScope 与 SharedDbConnectionScope 结合使用，否则您的事务将无法按预期工作。
其次，
我将使用另一种方法，使用单个语句，而不需要将 job_id 与公司一起保存）
用 job_number = 0 保存记录
更新记录像这样的东西
更新作业集 JOB_NUMBER =
（从 Company_id = 12345 的作业中选择 MAX(job_number)+1）
WHERE job_id = " + this.job_id;
（你只需要将这个查询转换为subsonic语法，我不使用subsonic3）
这应该保证每个公司的作业编号是唯一的（如果您将保存和更新命令包装在事务中并使用表锁）。
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)
Save the record with job_number = 0
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).
在大容量多用户系统上，使用动态代码获取当前最高值可能存在风险（潜在的重复值）。
另一种方法是创建一个包含两列的新表；一个是字符 PK，另一个是存储相关最后值的整数。每次您需要新值时，请增加相关公司记录中的值，并将该值用作您的订单号。角色 PK 将包含以下内容：
我有一个要调用的存储过程，该过程会在公司第一次收到订单时自动启动新记录，或者为后续订单递增该记录，并返回新的订单号值。
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:
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.