Too Long; Didn't Read
The locking mechanism is used to manage access to resources shared by user databases, tables, pages, and rows to guarantee the consistency of the transmitted data. There are two types of locking in databases, Optimistic Locking, and Pessimistic locking.
Optimistic Locking.
Optimistic locking is a technique for SQL database applications that do not hold row locks between selecting and updating or deleting a row. If the row does change, the update or delete will fail, and the application logic handles such failures by, for example, retrying the whole process.⁽¹⁾
Pessimistic Locking.
The pessimistic locking model prevents simultaneous updates to records. As soon as one user starts to update a record, a lock is placed on it. Other users who attempt to update this record are informed that another user has an update in progress. The other users must wait until the first user has finished committing their changes, releasing the record lock. Only then can another user make changes based on the previous user's changes.⁽²⁾