续集(Ruby)，如何以安全的方式递增和使用数据库计数器? [英] Sequel (Ruby), how to increment and use a DB counter in a safe way?

问题描述

我发现了4种正确"的方法:

I found 4 "proper" ways to do this:

1. 在针对ActiveRecord用户的备忘单中，代替了ActiveRecord的和increment_counter应该是album.values[:column] -= 1 # or += 1 for increment和album.update(:counter_name=>Sequel.+(:counter_name, 1))
2. 在 SO解决方案中，建议使用update_sql以获得相同的效果s[:query_volume].update_sql(:queries => Sequel.expr(3) + :queries)
3. 在随机线程中，我找到了这个dataset.update_sql(:exp => 'exp + 10'.lit)
4. 在用于更新的Sequels API文档中我找到了此解决方案http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-update

1. In the cheat sheet for ActiveRecord users substitutes for ActiveRecord's increment and increment_counter are supposed to be album.values[:column] -= 1 # or += 1 for increment and album.update(:counter_name=>Sequel.+(:counter_name, 1))
2. In a SO solution update_sql is suggested for the same effect s[:query_volume].update_sql(:queries => Sequel.expr(3) + :queries)
3. In a random thread I found this one dataset.update_sql(:exp => 'exp + 10'.lit)
4. In the Sequels API docs for update I found this solution http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-update

但是，没有任何一种解决方案能够真正安全地更新值并以安全，原子的方式返回结果.

yet none of the solutions actually update the value and return the result in a safe, atomic way.

基于afaik的基于添加值然后保存"的解决方案在多处理环境中应确定性地失败，并导致诸如以下的错误:

Solutions based on "adding a value and then saving" should, afaik, fail nondeterministically in multiprocessing environments resulting with errors such as:

1. 相册的计数器为0
2. 线程A和线程B都获取album
3. 线程A和线程B都增加了hash/model/etc中的值
4. 线程A和线程B都将计数器更新为相同的值
5. 结果是:A和B都将计数器设置为1并使用计数器值1

1. album's counter is 0
2. thread A and thread B both fetch album
3. thread A and thread B both increment the value in the hash/model/etc
4. thread A and thread B both update the counter to same value
5. as a result: A and B both set the counter to 1 and work with counter value 1

另一方面，

Sequel.expr和Sequel.+实际上没有返回值，但是Sequel::SQL::NumericExpression和(afaik)您无法进行另一次数据库往返来获取它，这意味着可以发生:

Sequel.expr and Sequel.+ on the other hand don't actually return a value, but a Sequel::SQL::NumericExpression and (afaik) you have no way of getting it out short of doing another DB roundtrip, which means this can happen:

1. 相册的计数器为0
2. 线程A和B都增加值，值增加2
3. 线程A和B都从数据库中获取行
4. 结果是:A和B都将计数器设置为2并使用计数器值2

那么，除了编写自定义锁定代码外，解决方案是什么?如果没有，则只需编写自定义锁定代码即可:)最好的方法是什么?

So, short of writing custom locking code, what's the solution? If there's none, short of writing custom locking code :) what's the best way to do it?

我通常对回答说我想要太多生活的回答感到不满意，正如1个回答所表明的:)

I'm generally not happy with answers saying that I want too much of life, as 1 answer suggests :)

相册只是文档中的一个示例.

The albums are just an example from the docs.

例如，假设您在一个电子商务POS上有一个交易计数器，该计数器可以同时在不同的主机上接受2个交易，并且需要向银行发送24小时内唯一的整数计数器(称为systan)给银行，使用相同的systan发送2个trx，并且发送1个trx会被拒绝，或者更糟的是，会警告计数中的差异(因为它们提示缺少事务")，因此无法使用数据库的ID值.

Imagine for example that you have a transaction counter on an e-commerce POS which can accept 2 transactions at the same time on different hosts and to the bank you need to send them with an integer counter unique in 24h (called systan), send 2 trx with same systan and 1 will be declined, or worse, gaps in the counts are alerted (because they hint at "missing transactions") so it's not possible to use the DB's ID value.

一个不那么严重的示例，但与我的用例更相关，在后台工作程序中同时触发了多个文件导出，每个文件目标都有自己的计数器.计数器中的空白会发出警报，工作人员位于不同的主机上(因此互斥锁无用).而且我有一种感觉，无论如何我将很快解决更严重的问题.

A less severe example, but more related to my use case, several file exports get triggered simultaneously in a background worker, every file destination has its own counter. Gaps in the counters are alerted, workers are on different hosts (so mutexes are not useful). And I have a feeling I'll soon be solving the more severe problem anyway.

DB序列也不好，因为这意味着要在每个终端加法之后执行DDL，我们在这里说的是1000.即使在我的用例不太严格的情况下，Web门户网站操作上的DDL仍然是PITA，并且甚至可能无法使用，具体取决于下面的缓存方案(由于实现了ActiveRecord和Sequel-在我的情况下，我同时使用了两者-可能要求服务器重新启动只是为了注册商家.

The DB sequences are no good either because it would mean doing DDL on addition of every terminal, and we're talking 1000s here. Even in my less sever use case DDLing on web portal actions is still a PITA, and might even not work depending on the cacheing scheme below (due to implementation of ActiveRecord and Sequel - and in my case I use both - might require server restart just to register a merchant).

Redis可以做到这一点，但是当您坐在符合ACID的数据库上时，仅仅为计数器添加另一个基础结构组件似乎很疯狂.

Redis can do this, but it seems insane to add another infrastructure component just for counters when you're sitting on an ACID-compliant database.

推荐答案

如果使用的是PostgreSQL，则可以使用UPDATE RETURNING:DB[:table].returning(:counter).update(:counter => Sequel.expr(1) + :counter)

If you are using PostgreSQL, you can use UPDATE RETURNING: DB[:table].returning(:counter).update(:counter => Sequel.expr(1) + :counter)

但是，由于不支持UPDATE RETURNING或类似的操作，因此无法在返回增加的值的同时原子地进行增量.

However, without support for UPDATE RETURNING or something similar, there is no way to atomically increment at the same time as return the incremented value.

这篇关于续集(Ruby)，如何以安全的方式递增和使用数据库计数器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！