Database Locking

There are two main types of locks in a Relational Database Management System (RDBMS):

Shared (Read) Locks:

1. Shared locks are used when a transaction wants to read data from a resource (rows in a table).
2. Multiple transactions can hold shared locks on the same resource simultaneously, allowing for concurrent reading.
3. Shared locks do not prevent other transactions from acquiring their own shared locks.
4. Any write operations are required to wait until the read lock is released.

Example: SELECT * ... FOR SHARE

django version:
objects.filter() or similar methods, the resulting queryset will acquire shared locks by default

with transaction.atomic():
  model_obs_to_update=
  model.objects.filter(some_condition)
  #extra logic here

Exclusive Locks:

1. Exclusive locks are used when a transaction wants to modify data in a resource. They prevent any other transaction from acquiring any lock (shared or exclusive) on the same resource.
2. Exclusive locks ensure that only one transaction can modify a resource at a time, maintaining data consistency. Once lock is acquired other transactions updating/reading same records will be blocked and can resume once lock is released

Example: SELECT * ... FOR UPDATE

django version:

model_ids = [1, 2, 3, 4]
with transaction.atomic():
  model_obs_to_update=
  model.objects.select_for_update().filter(id__in=model_ids)
  #extra logic here

lock will be released when transaction is either committed or rolled back.

In addition to these lock types, some databases support special locking behaviors:

NoWait:

1. When a transaction uses the NOWAIT option, it tries to acquire a lock. If the lock cannot be acquired immediately because the resource is already locked by another transaction, the transaction returns an error immediately rather than waiting.

Example: SELECT * ... FOR UPDATE NOWAIT

django version:

model_ids = [1, 2, 3, 4]
with transaction.atomic():
  model_obs_to_update=
  model.objects.select_for_update(nowait=True).filter(id__in=model_ids)
  #extra logic here

Skip Locked:

1. The SKIP LOCKED option is used to skip over rows that are already locked by another transaction when using an FOR UPDATE or FOR SHARE clause. This allows a transaction to work on the next available row without waiting for the locked row to be released.

Example: SELECT * ... FOR UPDATE SKIP LOCKED

These lock types and behaviors are essential for managing concurrency and ensuring data integrity in database systems. Proper use of locks helps to prevent conflicts between multiple transactions attempting to access and modify the same data simultaneously.