Do two rounds of major revision lead to a better chance of getting the paper accepted?
I think of requests for revision as falling into three basic categories, sorted in order of importance:
- New technical results (e.g., additional experiments, more theorems)
- Large-scale text improvement (e.g., reorganizing text, dealing with pervasive language issues)
- Localized text improvement (e.g., improving explanations, adding references)
Of these, only a request for new results should be of concern as a possible road to rejection: for the other two, if you want the paper published, if one cares to one can typically always put in sufficient work to address reviewer comments. It may be hard, unpleasant, and unrewarding (depending on the particulars), but this is an area where any request for revision almost always has a clear path to publication, and of a stronger paper than you started with.
Requests for new technical results, on the other hand, might or might not be something that you cannot reasonable address. If you can address them, then it's still a clear road to publication of a stronger paper. If you can't, however, then it's a question of scope and may be worth discussing with the handling editor to see whether they are a sine qua non for publication.
In general, however, my feeling about requests for revision, even major revision, is that they're generally good news. Once a paper hits at least a major revision, it's highly likely to be eventually accepted if the authors just keep answering the requests for improvement, and will likely be the stronger for it. There are exceptions (I just had one rejected by a journal for the rather unusual reason that "they only allow one revision,"), but in my experience these most frequently reflect an author who has chosen to fight the reviewers rather than to improve the paper.
I can see two reasons why a reasonable referee might request a second major revision after you've addressed all the issues raised in the first report. (1) Your revision to address the original issues might have introduced new problems that now need to be addressed. (2) Some problems in the original manuscript could not be detected until after other issues had been resolved.
As for your second question, about the chance of getting the paper accepted after the second round of revision, I cannot make any guess without a lot more information about the journal, the paper, the nature of the revisions, and perhaps the editor's mood.
I review many journal and conference papers frequently. In my opinion, first round decides whether the quality and technicality of the paper is considerable or not. If yes, then reviewer provides his/her minor and major comments. In second round of review, first reviewer checks whether all his/her comments have been addressed or not. Is the paper now seems reason to accept? If not, the reviewer ask for next review, but minor (may be with mandatory changes).
I have also observed that some reviewers keep on doing this and ask you every time to revise the paper with new comments. I don't think it is a good practice. The reviewer must give his/her major concerns in first round of review. If he/she is not satisfy with the quality of the paper, justify the reason and the reject the paper.
In my understanding your paper can be accepted (high probability), but you still need to revise the paper considering reviewer's comments.