> For the complete documentation index, see [llms.txt](https://avinandanbanerjee99.gitbook.io/system-design-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://avinandanbanerjee99.gitbook.io/system-design-notes/locking-in-databases.md).

# Locking in Databases

Broadly there are two types of concurrency control

### Optimistic Locking

* This is not actually a real lock obtained upon the database
* The strategy is, do a read - and remember what is the state of the row
  * This state may be timestamp, version number, or even the content
* When you do a write and go to commit the transaction, check that the remembered state is unchanged
* If the state is changed, abort the transaction, the user should retry
* This is implemented at application level rather than database level, and is preferable when collisions are infrequent in nature as it has lesser overhead
* DynamoDB uses this model

### 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, thereby releasing the record lock. Only then can another user make changes based on the previous user's changes.

An exclusive lock prevents any other locker from obtaining any sort of a lock on the object. This provides isolation by ensuring that no other locker can observe or modify an exclusively locked object until the locker is done writing to that object.

*Non-exclusive locks* are granted for read-only access. For this reason, non-exclusive locks are also sometimes called *read locks*. Since multiple lockers can simultaneously hold read locks on the same object, read locks are also sometimes called *shared locks*.

There are different levels of locks that can be acquired:-

1. A single row
2. A single page
3. A table as a whole
4. An entire database