What types of locks exist in SQL?

Types of locks in SQL:

• By level:

  • Table Locks: Lock the entire table. Simple to implement but can significantly reduce parallelism. Suitable for operations that modify the entire table (e.g., ALTER TABLE).
  • Row Locks: Lock individual rows. Provide high concurrency but require more resources to manage. Used by default in most DBMS for INSERT, UPDATE, DELETE.

• By access type:

  • Shared Lock (S Lock): Allows multiple transactions to read data simultaneously but prohibits writing by other transactions.
  • Exclusive Lock (X Lock): Allows only one transaction to access the data (for reading and writing) and prohibits access by others.
  • Intention Lock: Indicates to the DBMS that transactions intend to set a lock at a lower level (rows or tables). For example, IS (Intention Shared), IX (Intention Exclusive). Helps the DBMS quickly check lock compatibility.

• Other types:

  • Range Lock: Locks a range of rows in an index. Used to prevent phantom reads.
  • Metadata Lock: Locks the metadata of an object (table, view, etc.). Used during DDL operations.

Lock compatibility determines whether multiple transactions can hold certain types of locks on the same object simultaneously.

Lock Type	S	X	IS	IX
S	Yes	No	Yes	No
X	No	No	No	No
IS	Yes	No	Yes	Yes
IX	No	No	Yes	Yes