MySQL Locks

Table Lock

Description: Locks the entire table, typically used for bulk data operations. Table locks prevent other sessions from performing any read or write operations on the table.

Example: LOCK TABLES t1 WRITE;

Row Lock

Description: Locks specific rows within a table, suitable for transactional processing. Row locks allow different transactions to lock different rows in the same table simultaneously.

Example: RECORD LOCKS space id 2969 page no 4 n bits 72 index "PRIMARY" of table "test"."t1" trx id 123456 lock_mode X

Gap Lock

Description: Locks the gaps between index records to prevent new records from being inserted within the range. Gap locks are primarily used to prevent phantom reads.

Example: RECORD LOCKS space id 2969 page no 4 n bits 72 index "PRIMARY" of table "test"."t1" trx id 123456 lock_mode X locks gap before rec

Next-Key Lock

Description: Combines row locks and gap locks to lock a row and the adjacent gap, preventing phantom reads.

Example: RECORD LOCKS space id 2969 page no 4 n bits 72 index "PRIMARY" of table "test"."t1" trx id 123456 lock_mode X locks rec but not gap

Intention Lock

Description: A table-level lock indicating an intention to set locks on certain rows. Intention locks resolve compatibility issues between table locks and row locks.

Example: TABLE LOCK table 'test.t1' trx id 123456 lock_mode IX

Shared Lock

Description: Allows multiple transactions to read the data simultaneously but not write. Shared locks are suitable for read-only operations.

Example: RECORD LOCKS space id 2969 page no 4 n bits 72 index "PRIMARY" of table "test"."t1" trx id 123456 lock_mode S

Exclusive Lock

Description: Allows a single transaction to read and write data, preventing access by other transactions. Exclusive locks are suitable for update operations.

Example: RECORD LOCKS space id 2969 page no 4 n bits 72 index "PRIMARY" of table "test"."t1" trx id 123456 lock_mode X