Syncing events on Database - Dealing with blockchain reorganization

Question

My Dapp relies on live events synchronization on a centralized database. There is always a server side script polling the blockchain for new events, parsing then and do the corresponding operations on the database. Blockchain reorganization is a game breaking. As a common practice, I know it is suggested to wait for 12 confirmation blocks before syncing.

However, I'm testing on ropsten and I'm get reorganizations spanning 200 blocks!!! After a week of researches I'm stuck. Does it happen only on testnet? Is there a more realible testnet where I can trust 12 confirmations? How can I deal with this and realize live sync on the database?

Answer 1

If you want a reorganization free testnet, try Kovan. Ropsten is proof of work, and anyone who can point enough hashpower to it can rewrite the chain for a considerable number of blocks. Kovan is proof of authority, which ensures there are no reorganizations.

As for dealing with them on the mainnet, the general approach is:

On each new block, check the prevhash against the hash you have for the N-1 block. If the hash is different, get the hash for the prevhash's parent block, and compare that against n-1 blocks. Repeat this until you find a common ancestor between the blocks you've seen, and the blocks you have just received. Then drop all events upto that common ancestor, and rebuild the index from there using the longer chain of blocks in play at the moment. If two chains are of equal length, wait until a new block comes in and makes one longer.