I've encountered this dozens of times. It's not intuitive, but this implicitly locks the row from concurrent reads, where as SELECTing first won't:

  UPDATE accounts  
  SET balance = balance - 10  
  WHERE owner = 'alice' AND balance >= 10;

Another possible surprise, say two xacts do this at the same time:

  INSERT INTO foo(num) (
    SELECT 1 WHERE NOT EXISTS (
      SELECT * FROM foo WHERE num = 1
    )
  );

Without a UNIQUE on num, you get num=1 twice. Of course adding UNIQUE would prevent this, but what you might not expect is UNIQUE implicitly adds a lock too. So not only do you only get num=1 once, but also both xacts are guaranteed to succeed, which in some situations is an important distinction.

Schools teach that databases are ACID, but in most cases they aren't by default, and enabling full ACID comes with other caveats and also a large performance hit.

▲ mrits 2 days ago | parent [-]

One issue is there were a lot of database enhancements and known side effects introduced at a time where not only was SQL a full time job, it was often paid a lot more and was the most senior engineer on the team.

It has since become a tool of even front end engineers.

	▲	traderj0e 2 days ago \| parent [-]
		Yeah I keep running into situations where everyone else is a backend engineer but only has cursory knowledge of databases. Maybe they think the DB is just some modular add-on you can swap out, or they understand it's the foundation of all their backend code but don't really know how to do it right.