mysql支付记录表_mysql-如何避免在记录付款和运行余额的rail...

更新：这是第一个版本,有关实际可行的方法,请参见下文：

如果在使用pessimistic locking计算最后一笔余额时锁定了最后一笔付款,则可以摆脱竞争条件.为此,您始终需要使用事务处理包装创建付款.

class Payments < ActiveRecord::Base

before_create :calculate_running_balance

private

def calculate_running_balance

last_payment = Payment.lock.last

self.running_balance = last_payment.running_balance + amount

end

end

# then, creating a payment must always be done in transaction

Payment.transaction do

Payment.create!(amount: 100)

end

获取最后一次付款的第一个查询还将在记录的交易持续时间内锁定记录(并延迟进一步查询),直到记录完成交易为止,即直到交易被完全提交并创建新记录为止.

如果同时有另一个查询尝试读取锁定的最后一笔付款,则必须等到第一笔交易完成.因此,如果您在创建付款时在sidekiq中使用交易,则应该是安全的.

有关更多信息,请参见上面链接的指南.

更新：并不是那么容易,这种方法会导致死锁

经过大量测试后,问题似乎更加复杂了.如果仅锁定“最后一个”付款记录(Rails转换为SELECT * FROM Payments ORDER BY ID DESC LIMIT 1),那么我们可能会陷入僵局.

在这里,我介绍了导致死锁的测试,下面进一步讨论了实际的工作方法.

在下面的所有测试中,我正在使用MySQL中的简单InnoDB表.我创建了最简单的付款表,只在第一行中添加了金额列,并在Rails中添加了随附的模型,如下所示：

# sql console

create table payments(id integer primary key auto_increment, amount integer) engine=InnoDB;

insert into payments(amount) values (100);

# app/models/payments.rb

class Payment < ActiveRecord::Base

end

现在,让我们打开两个Rails控制台,在第一个控制台会话中使用最后一个记录锁定和新行插入来启动长时间运行的事务,并在第二个控制台会话中使用另一个最后一行锁定来启动：

# rails console 1

>> Payment.transaction { p = Payment.lock.last; sleep(10); Payment.create!(amount: (p.amount + 1)); }

D, [2016-03-11T21:26:36.049822 #5313] DEBUG -- : (0.2ms) BEGIN

D, [2016-03-11T21:26:36.051103 #5313] DEBUG -- : Payment Load (0.4ms) SELECT `payments`.* FROM `payments` ORDER BY `payments`.`id` DESC LIMIT 1 FOR UPDATE

D, [2016-03-11T21:26:46.053693 #5313] DEBUG -- : SQL (1.0ms) INSERT INTO `payments` (`amount`) VALUES (101)

D, [2016-03-11T21:26:46.054275 #5313] DEBUG -- : (0.1ms) ROLLBACK

ActiveRecord::StatementInvalid: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: INSERT INTO `payments` (`amount`) VALUES (101)

# meanwhile in rails console 2

>> Payment.transaction { p = Payment.lock.last; }

D, [2016-03-11T21:26:37.483526 #8083] DEBUG -- : (0.1ms) BEGIN

D, [2016-03-11T21:26:46.053303 #8083] DEBUG -- : Payment Load (8569.0ms) SELECT `payments`.* FROM `payments` ORDER BY `payments`.`id` DESC LIMIT 1 FOR UPDATE

D, [2016-03-11T21:26:46.053887 #8083] DEBUG -- : (0.1ms) COMMIT

=> #

第一笔交易最终陷入僵局.一种解决方案是从此答案的开头使用代码,但是当出现死锁时重试整个事务.

重试死锁的事务的可能解决方案：(未测试)

利用@ M.G.Palmer在this SO answer中重试锁定错误的方法的优势：

retry_lock_error do

Payment.transaction

Payment.create!(amount: 100)

end

end

发生死锁时,将重试该事务,即找到并使用了最新的最后一条记录.

测试工作解决方案

我came across的另一种方法是锁定表的所有记录.这可以通过锁定COUNT(*)子句来完成,并且似乎可以始终如一地工作：

# rails console 1

>> Payment.transaction { Payment.lock.count; p = Payment.last; sleep(10); Payment.create!(amount: (p.amount + 1));}

D, [2016-03-11T23:36:14.989114 #5313] DEBUG -- : (0.3ms) BEGIN

D, [2016-03-11T23:36:14.990391 #5313] DEBUG -- : (0.4ms) SELECT COUNT(*) FROM `payments` FOR UPDATE

D, [2016-03-11T23:36:14.991500 #5313] DEBUG -- : Payment Load (0.3ms) SELECT `payments`.* FROM `payments` ORDER BY `payments`.`id` DESC LIMIT 1

D, [2016-03-11T23:36:24.993285 #5313] DEBUG -- : SQL (0.6ms) INSERT INTO `payments` (`amount`) VALUES (101)

D, [2016-03-11T23:36:24.996483 #5313] DEBUG -- : (2.8ms) COMMIT

=> #

# meanwhile in rails console 2

>> Payment.transaction { Payment.lock.count; p = Payment.last; Payment.create!(amount: (p.amount + 1));}

D, [2016-03-11T23:36:16.271053 #8083] DEBUG -- : (0.1ms) BEGIN

D, [2016-03-11T23:36:24.993933 #8083] DEBUG -- : (8722.4ms) SELECT COUNT(*) FROM `payments` FOR UPDATE

D, [2016-03-11T23:36:24.994802 #8083] DEBUG -- : Payment Load (0.2ms) SELECT `payments`.* FROM `payments` ORDER BY `payments`.`id` DESC LIMIT 1

D, [2016-03-11T23:36:24.995712 #8083] DEBUG -- : SQL (0.2ms) INSERT INTO `payments` (`amount`) VALUES (102)

D, [2016-03-11T23:36:25.000668 #8083] DEBUG -- : (4.3ms) COMMIT

=> #

通过查看时间戳,您可以看到第二个事务等待第一个事务完成,并且第二个插入已经“知道”第一个事务.

因此,我提出的最终解决方案如下：

class Payments < ActiveRecord::Base

before_create :calculate_running_balance

private

def calculate_running_balance

Payment.lock.count # lock all rows by pessimistic locking

last_payment = Payment.last # now we can freely select the last record

self.running_balance = last_payment.running_balance + amount

end

end

# then, creating a payment must always be done in transaction

Payment.transaction do

Payment.create!(amount: 100)

end