I think you're right. An UPDATE would fail, but not an INSERT.
Skyrim came out 14 years ago.
I believe both SERIALIZABLE and REPEATABLE READ isolation levels would raise an error for this example.
And I looked this up: Postgres won't retry the transaction for you either. Though ultimately I think this a good default behavior.
Sqlite does not have a SELECT ... FOR UPDATE feature. The alternative is BEGIN IMMEDIATE.
There is a subtle scenario with read-modify-write transactions in MVCC where SQLite lacks some grace (in my opinion).
In MVCC, transactions work with a point-in-time (read "between atomic transactions") consistent "read snapshot" of the database.
Consider this example:
- Transaction A begins and reads from table
foo. - Transaction B begins and updates table
foo. - Both transactions commit.
There is no conflict here because these transactions are isolated from each other via the snapshot mechanism. Transaction A's read snapshot is immutable and will not see any writes from transaction B, even if they are happening concurrently.
Now what happens in this example (from the OP):
- Transaction A begins and reads from table
foo. - Transaction B begins and updates table
foo. - Transaction B commits.
- Transaction A tries to update
foo.
This is a true conflict because both transactions are trying to write to foo, and transaction A's writes might be based on what it just read. There is no consistent way for A to proceed, because B already wrote to foo, invalidating A's read snapshot.
So SQLite handles this by returning an error to A, effectively requiring A to restart the transaction.
There are other ways this could be handled though. The DB could optimistically retry the transaction for you. There is even a special BEGIN IMMEDIATE; statement that it could use to proactively take a write lock on foo so that the transaction doesn't get starved by other writers. But SQLite puts all of the responsibility on users to handle this.
I'm not an expert, so there could be a very good reason that SQLite works this way, but it feels a bit annoying as a user.
I don't actually know off the top of my head how PostgresQL handles this particular scenario.
Spent way too long trying to see how oxford comma was relevant.
- Saying goodbye to your friend before realizing you both parked next to each other, so you follow not too closely behind so they don't notice.
I just own the mistake and turn around. No one gives a fuck in public.
It's funnier when you are leaving a social event and you need to change directions in front of the people you were just with.
If they get sued they will claim it was a proper pit maneuver 🙄
Probably won't solve all of your problems, but I like to at least change git's default pager to delta.
Your average ICE officer has the mind of a 3rd grade bully with a racist dad.
If it's that prevalent, is it really improper at this point?