InnoDB Locking •Record Locks •Gap Locks •Next-Key Locks •Insert Intention Locks •AUTO-INC Locks •Predicate Locks for Spatial Indexes •A shared ( S ) lock permits the transaction that holds the lock to read a row. •An exclusive ( X ) lock permits the transaction that holds the lock to update or delete a row. •A request by  T2  for an  S  lock can be granted immediately. As a result, both  T1  and  T2  hold an  S  lock on r . •A request by  T2  for an  X  lock cannot be granted immediately. •Before a transaction can acquire a shared lock on a row in a table, it must first acquire an  IS  lock or stronger on the table. •Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an  IX  lock on the table. 3081 For example,  SELECT ... FOR SHARE  sets an  IS  lock, and  SELECT ... FOR UPDATE  sets an  IX lock. The intention locking protocol is as follows: •An intention shared lock ( IS ) indicates that a transaction intends to set a  shared  lock on individual rows in a table. •An intention exclusive lock ( IX ) indicates that a transaction intends to set an exclusive lock on individual rows in a table. InnoDB  supports  multiple granularity locking  which permits coexistence of row locks and table locks. For example, a statement such as  LOCK TABLES ... WRITE  takes an exclusive lock (an  X  lock) on the specified table. To make locking at multiple granularity levels practical,  InnoDB  uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks: Intention Locks If a transaction  T1  holds an exclusive ( X ) lock on row  r , a request from some distinct transaction  T2  for a lock of either type on  r  cannot be granted immediately. Instead, transaction  T2  has to wait for transaction T1  to release its lock on row  r . InnoDB  implements standard row-level locking where there are two types of locks, shared ( S ) locks and exclusive ( X ) locks. If transaction  T1  holds a shared ( S ) lock on row  r , then requests from some distinct transaction  T2  for a lock on row  r  are handled as follows: Shared and Exclusive Locks