Long answer short- It depends on the editorial policy of the specific journal or the current editor.
In my field, 4 or more rounds of revisions is not completely unheard of. Despite people's frustration with such policy of unlimited number of revisions (Imagine being that person who got a rejection after 4 major revisions and the manuscript being under review for 3 years? --- this is not uncommon in the social sciences), some journals do retain such policy. The top 2 journals in sociology are notorious for this.
I currently serve on an editorial board of a reputable journal in the field (one of the top 5), where the current editor has changed the journal policy to not extend second R&Rs. In this journal, you only get once chance to revise, and the result of the revise can either be an outright accept, conditional accept (which will come back with very minor change suggestions-- for example, cite an overlooked source, change the title of the paper, rewrite the conclusion, etc), or reject. I think this is a positive step forward, especially since many editors in the field tend to be very nitpicky about minor/aesthetic issues.