Surely, if they lose Indyref2, then another referendum could not take place for 25 - 30 years (a 'generation'). So what do the SNP and Nicola do for the next 25 years? They can't beat the drum for independence - they may actually have to deal with sorting out the NHS, education etc. But do they have the ability to do so? If not, they will be toast.
There's no rulebook for this. If the will of the Scottish Parliament is for a third referendum, then there will be. The idea that a democratically elected parliament can only vote for independence every 25-30 years is fundamentally undemocratic.
I wouldn't completely underestimate Sturgeon but I think the most likely scenario is her resigning in 2024 or 2025, after no 'legal' independence referendum has occurred (and Starmer is possibly in power by then with the SNP having weak leverage in a hung parliament) and I'm also assuming the SNP will 'only' poll 40-45% maximum in the 2024 GE if they try to frame it as a plebiscite.
The Holyrood polling is also remarkably stable in terms of the unionist/nationalist blocs, the only changes being SNP voters switching to Greens on the list and the Tories losing votes to LDs and Labour.
The latest independence poll from TechneUK is also 54% NO 46% YES.
Sturgeon already made it clear that this is her last term. If there's no independence referendum, then the SNP will go into the next GE on a single issue. If the SNP then win 50+ seats and there's a hung parliament, Labour might well agree to a referendum in exchange for ending Tory rule. For the SNP, it's tactically much better to have the Tories in power, so Labour will need to come up with something in order to convince them to lend their support.
With Holyrood polling, one thing that should be pointed out is that Yes would be starting from a far stronger point this time round. 54/46 equates to a lead of around 160,000 votes for No, or around 80,000 that need to switch from No to Yes. It's not a huge number, especially when very few voters will switch from Yes to No.
Personally, if I were an English politician right now, I'd be offering Indyref2, but on condition that it is a 'once in a generation vote' enshrined in law and with a special clause that explicitly prevents a third referendum unless 2/3rds of the UK Parliament agrees. The SNP couldn't refuse, yet it could be a poisoned chalice.