I think the general public (and by that I mean including software engineers too) overestimate the likelihood of a huge screw-up leading to being fired like they do in the movies, if the screw-up is neither (1) malicious/intentional in nature, nor (2) demonstrates that you're grossly incompetent for the job.
Most huge screw-ups happen to well-intentioned, knowledgeable software engineers, who simply made an honest mistake.
The correct way to handle it, on the company/management's perspective, is not to fire the person who made the mistake, but to allow them to correct it (perhaps with help from others). And that is indeed what happens in most cases. There are certainly poorly managed companies who would fire someone in these scenarios, but they should be less common than otherwise.
I'm not going to name any names: in the late 00s/early 10s I worked in one of the highest-profile, high-growth tech startups of its era, and I've personally made a blunder that corrupted literally millions of user records in the database. This incident was known internally as one of the most disastrous technical things that happened in the company's history, among a few others. The nature of the product was one of very quickly updating data, and updates were critically important (e.g. is affected by user spends) and hence restoring from DB backups of even the night before was unfeasible. There was irreparable damage where a whole team of us had to spend the next few weeks painstakingly hand-fixing data for users, and coming up with algorithms/code to fix these things as users use the product as they go. As you expect in this anecdote, I did not get fired, I was part of the team that worked tirelessly following this incident to fix user data, and I continued to have a good, growing career in my remaining time in this company (the next few years).