What are the different types of database locks?

Database locks can be categorised into two main types: shared locks and exclusive locks.

Shared locks are used when a database operation needs to read a resource but not modify it. This type of lock allows multiple transactions to read (select) a resource but not change it. As long as a shared lock is held by a transaction, no other transaction can get an exclusive lock (i.e., write lock) on that resource. This ensures that while multiple transactions can read the resource simultaneously, none can modify it, thereby maintaining the consistency of the database.

Exclusive locks, on the other hand, are used when a database operation needs to both read and modify a resource. When a transaction holds an exclusive lock on a resource, no other transaction can get a shared lock or an exclusive lock on that resource. This means that the transaction with the exclusive lock has exclusive access to the resource for both reading and writing. This type of lock is necessary to ensure that when a resource is being modified, no other transaction can read or modify it, thereby preventing inconsistencies and conflicts.

In addition to these two main types, there are other types of locks used in more specific scenarios. Update locks are a special type of lock used when a transaction needs to read a resource with the intention of modifying it later. This type of lock prevents other transactions from acquiring an exclusive lock on the resource, thereby preventing deadlocks.

Intent locks are used to establish a locking hierarchy. They indicate that a transaction intends to acquire a lock on a lower level of the hierarchy. For example, a transaction may acquire an intent lock on a table to indicate that it will later acquire a lock on a row within that table.

Finally, schema locks are used when performing operations that involve changes to the database schema, such as adding or dropping tables. These locks prevent other transactions from accessing the database schema while it is being modified.