Describe the difference between optimistic and pessimistic locking.

This question assesses your technical knowledge of database management and concurrency control mechanisms. Interviewers want to understand your foundational understanding of how systems handle simultaneous data access, as well as your ability to explain complex concepts clearly. A well-rounded response can indicate not only your technical proficiency but also your ability to communicate technical topics to others.

Optimistic locking assumes that multiple transactions can complete without affecting one another, allowing changes to be made without immediate checks for conflicts. If a conflict occurs, the transaction is rolled back, and the user is notified. In contrast, pessimistic locking takes a cautious approach, locking records at the start of a transaction to prevent other transactions from making changes until the first transaction is complete. You could say, 'For instance, in a collaborative editing tool, optimistic locking allows multiple users to edit without locking until saving, while pessimistic locking would lock the document right away to avoid conflicts.'