I'm working on a Bitcoin-clone block explorer (specifically Mincoin) which has a block timer of about 1 minute. For those unfamiliar with Bitcoin, basically a new data structure called a block is propagated to the network about every minute. A block itself holds a lot of information, but three things are the most important:
- The previous block before it
- The next block in the chain
- A data structure containing transactions on the network
So sometimes in Bitcoin there is a "reorganization" which means the network finds that some of the most recent blocks are not valid and it switches over to another miner's branch.
At the moment every 1 minute I get all of the new blocks from the network and I store them into my database. However when a reorganization happens I was previously unable to delete and re-do the block structure of my database.
Now that I've written that functionality in, I'm encountering a new problem. I've got a function that looks in the past 100 blocks and checks if the hash of the block is the same as what my Mincoin client reports. If there was a reorganization, then some blocks will be different. I find the first block that is different and I delete from the database the block before it (so that block can point to the correct new one that will be found), the block itself, and all records of all blocks and transactions that happen after it, then rebuild from there.
The problem is the process of finding and deleting each transaction within the deleted blocks takes more than one minute because of the large amount of data, even though the fields I'm searching on are indexed. So I'm worried that the 1 minute cron I've put on the program will cause a problem where it starts to write again from the database at the same time the process of deleting from the rebuilding function is happening.
I'm using PHP and PDO so I have a PDO transaction for the rebuilding function, but this just adds more confusion. If the PDO transaction starts deleting records, then during that process a new cron finds some new blocks and writes them to the database, then the PDO transaction ends, which data will be stored? Will it cause an error? How is the best way to handle this -- should I write some field into the database that controls the regular cron (simply put a switch 0 or 1 for on and off) and keep the switch disabled while the rebuilding function is doing its thing?
I'm not sure how to structure the flow of this program to ensure I am not causing my database to have inaccurate data in it.