MySQL与会计应用程序的交易 [英] MySQL transaction with accounting application

问题描述

我有一个如下表:

transaction_id
user_id
other_user_id
trans_type
amount

此表用于维护财务类型应用的帐户交易.

This table is used to maintain the account transactions for a finance type app.

它具有双重记帐功能，因此从用户A到用户B的转移将在表中插入两行，如下所示.

Its double entry accounting so a transfer from User A to B would insert two rows into the table looking like.

1, A, B, Sent, -100
1, B, A, Received, 100

任何帐户上的余额都是通过对该帐户的交易进行汇总得出的.

The balance on any account is calculated by summing up the transactions for that account.

例如:

select sum(amount) from transactions where user_id=A

阻止资金转移的最佳方法是什么?我当前的代码如下:

What is the best way to lock down transferring of funds? My current code looks like:

Start Transaction
Debit the sender's account
check the balance of the sender's account
if new balance is negative then the sender didn't have enough money and rollback
if the balance is positive then credit the receiver and commit

这似乎不能完全按照预期的方式工作.我在网上看到很多有关交易的示例，这些交易基本上说:开始，借方发送方，贷方接收方，提交.但是，检查两者之间余额的最佳方法是什么?

This seems not to be working exactly as expected. I see a lot of examples online about transactions that say basically: start, debit sender, credit receiver, commit. But what is the best way to check the sender's balance in between?

我不应该通过交易进行交易.假设用户有3K的余额，并且3K完全同时发生了两笔交易，而这两项交易仅在一次应有的情况下才通过.

I have transactions getting through that shouldn't. Say a user has a balance of 3K and two transactions come in at exactly the same time for 3K, both of these are getting through when only one should.

谢谢

推荐答案

您正在使用InnoDB表还是MyISAM表? MySQL不支持MyISAM表上的事务(但是，如果您尝试使用它们，它不会给您带来错误).另外，请确保正确设置事务隔离级别，该级别应为SERIALIZABLE，这不是MySQL的默认设置.

Are you using InnoDB tables or MyISAM tables? MySQL doesn't support transactions on MyISAM tables (but it won't give you an error if you try to use them). Also, make sure your transaction isolation level is set appropriately, it should be SERIALIZABLE which is not the default for MySQL.

此文章有一个很好的示例，它解释了使用与您非常相似的示例了解不同隔离级别的影响.

This article has a good example that explains the impact of the different isolation levels using an example very similar to yours.

这篇关于MySQL与会计应用程序的交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！