What is optimistic locking and how is it typically implemented in SQL?

Question

SQL Fundamentals — Hard

What is optimistic locking and how is it typically implemented in SQL?

Accepted Answer

Optimistic locking in SQL assumes conflicts are rare, so it reads without locks and checks a version column at update time to detect concurrent modification. If a change is detected, it rolls back the transaction.