So, my question is, what wallet and account solution mining pools use? What software cryptsy, btc-e and bitstamp use?
Data integrity: Lets say I do not use bitcoind accounts. I have N addresses in Wallet and my own SQL database with addr-user (n:1) mapping. I really need user to have many addresses. So, when I display addr A to some user, I have to check for transactions to the addr A in some point in time and update my SQL - cron could be my friend in this case.
That would be the worst possible solution. bitcoind supports block and tx callback notifications. At an application level you don't need to be concerned with the "balance at each address" (an abstraction which doesn't even exist at the protocol level) instead you only care about the balance per user. When a tx comes in, determine which user it belongs to.
But, I have to scan all the addresses in my SQL with current balance to find out user balance! I have to use "deep" scan - I can not assume each address has static balance - the wallet sends btc from addresses it decides are optimal. So, I have to use for example blockchain.info API where I can pass many addresses with minconf and it returns balance from corresponding TXs which have minconf confirmations. I am sure bitcoind's rpc functions do not support many addresses as input and not sure if it could retrieve balance at minconf from one addr either.
It is pointless to cache user balances in SQL, because it may be changed at every user withdrawal. No way to say "send ONLY from THIS address".
Once again who cares what the current balance of an individual address is, in a pool fund situation. Joe has deposited 5 BTC, you owe Joe 5 BTC, Joe's "balance" is 5 BTC.
It is pointless to cache user balances in SQL, because it may be changed at every user withdrawal. No way to say "send ONLY from THIS address".
Of course it changes on ever withdraw (and every transfer and ever deposit).
If a user has the following tx stored in the database
Deposit 10 BTC to deposit address x, tx id y
Withdraw 2 BTC to address x, tx id y
Transfer 1 BTC to user z
What is the user's current balance?
If those records were in a table couldn't you get the balance at any moment in time with a SELECT?
It is difficult (at least for me) to code two separate systems which must be synchronized.
Then the issue isn't coding but architecture. Forget Bitcoin a second. Imagine you ran a USD based service. Customers can deposit funds, customers can withdraw funds, customers can transfer funds to other customers accounts. Are you telling me there is no possible way you could keep track of user balances without doing it all inside the banking system? If the answer is no then replace bank/USD with bitcoind/BTC and that is your solution. If the answer is yes, then please don't develop or launch a service without a lot more research.
Even if bitcoind did everything you wanted it to do it doesn't scale trying to use it as a real time balance data feed. Use it as it was intended, as a interface to the bitcoin network. You can then scale out if needed (multiple instances).