1) Optimistic locking (update the version)

• Assume conflicts are rare.
• Detect conflicts only when updating.
• Version number or timestamp column

How it works

1. Read phase
   • Read the record
   • Read its version/timestamp
2. Work phase
   • Make changes in the application
3. Write phase (check & update)
   • Before writing back to the database, check if the version number or timestamp is still the same.
   • If unchanged: write succeeds and version increments
   • If changed: write fails → retry or handle conflict

Pros

• No long locks, better performance and scalability
• Good for read-heavy workloads
• Avoids deadlocks common with pessimistic locking

Cons

• Update failures are possible, must handle retries
• Not ideal for highly contended data (frequent conflicts)