I think Tooster's answer covers most of the important points, but I do want to elaborate on a couple of details.
Maintaining Flow
This builds on Tooster's references to "context" that can be lost between play sessions. When I quit a game, I might intend to come back to it in a minute or an hour. But in practice, sometimes it will be days or weeks in between. During that time, my memory of where I was and what I was doing, and even my skills in the game will atrophy.
In Metroidvania and Zelda games like Reverent Lapwing describes, sections of the map will often be designed around a theme. Introducing a new element (like a new enemy, obstacle, player ability, or strategy), then developing it over the course of several challenges/rooms/waves, twisting it by juxtaposing it against previously-introduced elements, and finally culminating at a boss fight or major test that lets the player show off their mastery of the new skill.
When played through in one session, this creates an enjoyable progression - your understanding and skill in dealing with the new ingredient grows, parallel to the escalation of the challenge. Designed well, this facilitates the player in finding a "flow state" that can be very engaging.
Dropping cold into the middle of this flow, however, can be quite painful. I've forgotten what trick I needed to navigate this area, or I've lost the particular timing that I'd practiced in the previous room due to the time spent away from the game. Instead of a feeling of flow, I find I'm bashing my head against the wall just trying to get back up to speed.
In a scenario like this, starting the player back at the checkpoint can be a mercy - giving them a chance to practice the early challenges again and pick back up the thread of what they were doing. This is even more true if the reason they ended the last session was because they got stuck - a chance to practice the early lesson again is often all they need to unstick themselves, but they won't get that if we spawn them back at the part that frustrated them into quitting.
Meanwhile, the impact on a player who did learn and retain the skill is (if the checkpoint frequency is well-designed) relatively mild. They can breeze through the early parts of the challenge with their already-developed skills, maybe finding it at worst dull for a couple minutes until they get back to the novel part. If you have great 3Cs, then even fighting your way back to where you were should still be pleasurable - if it's not, then you have deeper problems that skipping the replay only partly masks.
Preventing Soft-Lock
Reverent Lapwing touches on this in the question: if you spawn the player at a checkpoint, you can guarantee the game is playable and winnable from every possible spawn point. Generally speaking, we cannot make this guarantee for every possible game state that can arise at runtime and get snapshotted by a background auto-save or save-on-exit feature. In a game of even modest size, there are enough possible interactions between mechanics that it basically reduces to the Halting Problem.
I recently worked on a AAA open world game, and the variety of navigational tools and explosive mechanics open to players meant there were plenty of opportunities to get hurled out of the ordinarily-navigable area and get stuck somewhere. If we spawned you back at the exact position where you quit, then these would become walkthrough breaks. Even though they were exceedingly rare, even a one in a million chance means it happens to tens of players if you sell tens of millions of copies. So instead, we always snapped the player to the closest "known good" spawn point, of which we had a reasonable number that we could exhaustively verify.
The chaos that can be introduced by an overly-faithful save system can be even worse during development of the game. Let's say I'm doing a test of "chapter 2", and I'm halfway through at the end of the day, so I save and log off, recording my exact position. The next time I get a chance to continue my test, I'm working with a slightly more updated build (which is good - fewer bugs, right?), not realizing that yesterday the level designer checked in a change that raised the floor 1 centimeter. Now my save file - which has recorded the exact position where I should be spawned - places me under the floor and I'm stuck or fall to my death.
These kinds of changes are happening continuously during development, and having to create new save files for each test scenario or redo the test from scratch anytime something changes can seriously slow down development.
(Yes, we could fix this particular example by having the load system scan for a valid place to stand near the saved position, but that fixes only the one case, not the general problem, and adds complexity - something we'll get to shortly)
This can happen in non-buggy ways too, like being on the wrong side of a damaging obstacle with 1 health left, or in a boss fight with your ammo exhausted. We can say "oh, that's the player's own fault" and yeah maybe it is, but it's not necessary for the game to rub your nose in your mistake and force you to struggle in vain or kill yourself when it could have spawned you in a state it knows is winnable instead. Especially in cases like Tooster's Portal example, where the fact that they're in a dead end might not be obvious to the player.
As Reverent Lapwing points out, a workaround for soft-lock issues is to offer a "return to last checkpoint" option as a menu item or in-game ability of some kind, and I do think those are valuable inclusions. However, from observing playtests, I can attest that even when these options are present, players frequently miss them or forget about them, because they're not part of their routine play. So ensuring the player always starts a fresh game session in a winnable state can still improve the player experience and reduce negative reviews or customer service calls from frustrated players.
Complexity
A checkpoint save system can be dirt simple. Upon entering a trigger zone, you save the character's current progression state (like XP/items/etc), and the ID of the checkpoint they're at, and that's all the information that you need to reconstruct this game state reliably.
The checkpoint location will generally be designed such that it has no other complicated state in its immediate vicinity - no enemy spawners, etc - so everything else can just be spawned "from scratch" as the player approaches, and there will be no visible or game-altering discrepancies. (This also gives the resuming player a space to re-orient themselves with what's happening in the game before they're immediately tested on their recall)
A save that records you "in the moment" needs to contend with a lot more. Just about every feature you add to the game, the save system needs to be made aware of it in order to put that feature back into the right state on load.
Even simple things like moving platforms can be a can of worms. If you can save on a moving platform, then the position/phase of the moving platform (and other platforms you might need to jump to from there / hazards you might need to avoid) also need to be saved and synchronized with your load, so you don't spawn in mid air or in an unwinnable configuration. If you want to avoid this complexity by disallowing saves on moving platforms, then you need to save a bookmark anytime the player jumps or is knocked around just in case they might land on a moving platform and quit before returning to solid ground. Both ways have a cost. Multiply this by every other thing your player can do or have done to them. Alternate forms/movement modes, status effects, multi-stage enemy waves or boss battles, and especially any kind of exotic gameplay for a narrative beat or level design set piece.
These types of complexity can creep in unexpectedly. Say a level designer makes a room where you need to hit a series of switches in a particular order to open and shut doors, giving you access to other switches and other doors. You've probably played dozens of segments like this. The level designer doesn't know this needs special save handling - they don't know the guts of the save system, and other times they've used one switch-door pair at a time there were no issues. And the programmers working on the save system usually aren't playing every part of the map or attending every review, so they don't spot it either.
But a player who quits in the middle of the puzzle might come back to an unwinnable state - spawned somewhere in the middle, but with a door still in the initial state, so they can't reach the next switch. Worse, this kind of bug can go undetected, because any tester playing the puzzle through in one pass finds it works perfectly - it only shows up if the player quits at just the wrong moment.
This may seem like a contrived example, but at least one nasty issue like this has cropped up in every AAA game with an autosave system I've worked on, often being discovered months later, and always at the most inconvenient time to overhaul either the offending content or the underlying save system. We've had it arise from things as unassuming as loot containers or sidequest completion. The bigger the project, the more susceptible it is, because you have more cooks in the kitchen and it becomes infeasible for the save system programmer(s) to stay abreast of every change being made that might impact game saves.
Conceptually "snapshot the game on exit" sounds simple, but in practice it carries a heavier development cost.
Priorities
You may object to the preceding sections, "but that's all solvable!" and yes, it is, given unlimited time to fix the problems or wisdom to avoid them all in the first place (and all the time needed to implement that wise robust solution).
Unfortunately, we work with finite time and finite wisdom. In practice, the more complexity there is in the save system, the more time and brain cycles your team will be spending on that complexity instead of other parts of your game.
Sadly, games don't get rave reviews about the quality of their save system. You could have the greatest feat of engineering under the hood, and players will only ever notice it when something goes wrong.
So sometimes, it makes more sense to make a simple, predictable save system that does one thing reliably (say, a simple contract with the player "when you load, you'll spawn at the last checkpoint you touched, with full health and ammo"), and spend the dev time and energy freed in this way on parts of the experience that get players more excited - like the 3Cs and combat mechanics, adding new bosses or new items and abilities, etc. (all of which become cheaper to create when you don't have to contend with every save contingency).
This is especially true if you're making a game that you expect will almost always be played in sessions that are one or more "checkpoint span" long. Respawning a player at the exact moment they left off might save an individual player some seconds or minutes of retracing their steps, but this will often have less impact on their appreciation of the game than say an extra hour of new gameplay.
Wrapping Up
None of this is to say "Auto-save bad. Always use checkpoints." - both systems have their merits, but they also have drawbacks. If you were making a mobile game meant to be played in very short snippets that could be interrupted at any time and had very simple state considerations, then a background auto-save that puts you exactly where you left off might be a no-brainer.
For a designer, the challenge is to be aware of the trade-offs of each decision, and find the one that's right for the game they're making and the team/production practices making it. And then to design the feature and the features it intersects with in such a way as to capitalize on its strengths and minimize the frustration or risks introduced by its weaknesses.
