4 ms·

> You can use an ACID compliant database and run into this problem, it's a classical race condition. No it's not. It's just sloppy code and/or a misunderstandi

by sehrope 13y ago

> You can use an ACID compliant database and run into this problem, it's a classical race condition. No it's not. It's just sloppy code and/or a misunderstanding of database transactions and isolation levels. If you use an ACID compliant database like PostgreSQL it's pretty straightforward to write proper code that would not have this issue. The main concept to understand is that you need to lock all the rows that you'll be touching before you use them. In most case this method works fine with the default read-committed isolation level. Code that queries your database in multiple round trips and then compares the values for validity on the app side will have this problem. Anything you read could have been modified by the time you "act on" the information you read. Heck even stored procedures running on the DB itself will have this problem if run in the default read-committed isolation level. The high level solution is to use SELECT ... FOR UPDATE and lock everything that you'll be touching before you update anything. > The solution is to implement double bookkeeping and bank reconciliation, but it's tedious and complex to implement. This is a business requirement for any accounting/financial software but it's not a technical one. Having an audit trail, double entry accounting, and recon are business functions. You'd be crazy to design a system that didn't include them but it's not strictly required (again from a technical sense, not a business one!).

RyanZAG 13y ago
The problem appears to have been more straight forward than any of that. The actual transaction system was a different system from the order entry/webs system. It ran orders in serial after a delay using some kind of message passing. Obviously you can't have a SQL transaction that spans multiple applications, so SQL transactions wouldn't really work in this case. It was just a straight forward mistake: orders could be placed before previous orders were finished executing. Transactions wouldn't actually work with bitcoin in this case anyway. eg, transaction 1 checks balance, deducts amount, does BTC transfer, and then commits. Transaction 2 does the same, but the commit would fail as transaction 1 has already modified it. Transaction 2 can't actually roll back though - the bitcoin transaction cannot be rolled back.
- knome 13y ago
  You wouldn't do the bitcoin transfer until the transaction authorizing it completed successfully. And you don't just use a single transaction that deducts the amount. You'd use one transaction to indicate you were beginning a transfer, putting that amount in a hold state, attempt the bitcoin transfer, then a transaction to indicate the bitcoin transfer was successful or not. If there is an error in the bitcoin transfer or after, and the second transaction is never run, the funds are still in hold whether transferred or not and can be manually corrected by checking the system logs.
  - unclebucknasty 13y ago
    I might do something similar in the main. Still, this doesn't inherently solve the problem. That is, you would still need to be aware of transaction isolation levels and ensure that you're locking rows properly as you conduct each transaction.
- arethuza 13y ago
  It's been a while, but I'm pretty sure I have worked on systems where the message queue endpoint and a database could both enlist in a distributed transaction.
- yxhuvud 13y ago
  The second part should be possible to solve using transfer accounts where the btc is stored temporarily while working out the transaction details. Everything would then be reversible until the transaction is finalized.
  - JoeAltmaier 13y ago
    So, a btc escrow service? Sounds like an app!
- sehrope 13y ago
  > The problem appears to have been more straight forward than any of that. The actual transaction system was a different system from the order entry/webs system. It ran orders in serial after a delay using some kind of message passing. I'm thinking that they had both issues. If the system let you submit multiple withdraw requests (totalling more than your balance) then they definitely weren't checking at request submission before queuing them for later processing. > Obviously you can't have a SQL transaction that spans multiple applications, so SQL transactions wouldn't really work in this case. It was just a straight forward mistake: orders could be placed before previous orders were finished executing. XA transactions allow you to have transactions across multiple services. The most common use case is a database and a message queue. It's nowhere near as simple as dealing with a single transactional resource but it's not that uncommon in the finance world. I've used them quite a bit and they're really convenient. Getting it right from a dev-ops perspective is a bit pretty tricky though as you need to really understand how the transaction manager itself works and make sure it actually runs. > Transactions wouldn't actually work with bitcoin in this case anyway. eg, transaction 1 checks balance, deducts amount, does BTC transfer, and then commits. Transaction 2 does the same, but the commit would fail as transaction 1 has already modified it. Transaction 2 can't actually roll back though - the bitcoin transaction cannot be rolled back. Yes you can't have XA transactions with bitcoin as it doesn't include any concept of rollback. What you can do though is guarantee that a transaction is only run once and re-submit it if it failed to send. If you can uniquely identify a bitcoin transaction[1] then you can guarantee that you don't send it out more than once. Just add in a significant delay (couple hours? a day?) before any retries to ensure the transaction has been merged into the block chain. [1]: And not by just using the transaction hash because we all know what can happen with that: https://en.bitcoin.it/wiki/Transaction_Malleability https://en.bitcoin.it/wiki/Transaction_Malleability
lnanek2 13y ago
Actually, I've worked on very large SOA systems that did share transactions and rollbacks across services. There are lots of standards for it, like WS-AtomicTransaction. So it's easily possible to do what the parent is asking, even when multiple services are involved.
- bunderbunder 13y ago
  FWIW, the A in ACID and the A in AtomicTransaction both stand for the same thing.

by sehrope 13y ago

RyanZAG 13y ago

The problem appears to have been more straight forward than any of that. The actual transaction system was a different system from the order entry/webs system. It ran orders in serial after a delay using some kind of message passing. Obviously you can't have a SQL transaction that spans multiple applications, so SQL transactions wouldn't really work in this case. It was just a straight forward mistake: orders could be placed before previous orders were finished executing. Transactions wouldn't actually work with bitcoin in this case anyway. eg, transaction 1 checks balance, deducts amount, does BTC transfer, and then commits. Transaction 2 does the same, but the commit would fail as transaction 1 has already modified it. Transaction 2 can't actually roll back though - the bitcoin transaction cannot be rolled back.

knome 13y ago
You wouldn't do the bitcoin transfer until the transaction authorizing it completed successfully. And you don't just use a single transaction that deducts the amount. You'd use one transaction to indicate you were beginning a transfer, putting that amount in a hold state, attempt the bitcoin transfer, then a transaction to indicate the bitcoin transfer was successful or not. If there is an error in the bitcoin transfer or after, and the second transaction is never run, the funds are still in hold whether transferred or not and can be manually corrected by checking the system logs.
- unclebucknasty 13y ago
  I might do something similar in the main. Still, this doesn't inherently solve the problem. That is, you would still need to be aware of transaction isolation levels and ensure that you're locking rows properly as you conduct each transaction.
arethuza 13y ago
It's been a while, but I'm pretty sure I have worked on systems where the message queue endpoint and a database could both enlist in a distributed transaction.
yxhuvud 13y ago
The second part should be possible to solve using transfer accounts where the btc is stored temporarily while working out the transaction details. Everything would then be reversible until the transaction is finalized.
- JoeAltmaier 13y ago
  So, a btc escrow service? Sounds like an app!
sehrope 13y ago
> The problem appears to have been more straight forward than any of that. The actual transaction system was a different system from the order entry/webs system. It ran orders in serial after a delay using some kind of message passing. I'm thinking that they had both issues. If the system let you submit multiple withdraw requests (totalling more than your balance) then they definitely weren't checking at request submission before queuing them for later processing. > Obviously you can't have a SQL transaction that spans multiple applications, so SQL transactions wouldn't really work in this case. It was just a straight forward mistake: orders could be placed before previous orders were finished executing. XA transactions allow you to have transactions across multiple services. The most common use case is a database and a message queue. It's nowhere near as simple as dealing with a single transactional resource but it's not that uncommon in the finance world. I've used them quite a bit and they're really convenient. Getting it right from a dev-ops perspective is a bit pretty tricky though as you need to really understand how the transaction manager itself works and make sure it actually runs. > Transactions wouldn't actually work with bitcoin in this case anyway. eg, transaction 1 checks balance, deducts amount, does BTC transfer, and then commits. Transaction 2 does the same, but the commit would fail as transaction 1 has already modified it. Transaction 2 can't actually roll back though - the bitcoin transaction cannot be rolled back. Yes you can't have XA transactions with bitcoin as it doesn't include any concept of rollback. What you can do though is guarantee that a transaction is only run once and re-submit it if it failed to send. If you can uniquely identify a bitcoin transaction[1] then you can guarantee that you don't send it out more than once. Just add in a significant delay (couple hours? a day?) before any retries to ensure the transaction has been merged into the block chain. [1]: And not by just using the transaction hash because we all know what can happen with that: https://en.bitcoin.it/wiki/Transaction_Malleability https://en.bitcoin.it/wiki/Transaction_Malleability

lnanek2 13y ago

Actually, I've worked on very large SOA systems that did share transactions and rollbacks across services. There are lots of standards for it, like WS-AtomicTransaction. So it's easily possible to do what the parent is asking, even when multiple services are involved.

bunderbunder 13y ago
FWIW, the A in ACID and the A in AtomicTransaction both stand for the same thing.