InnoDB Locking Table-level lock type compatibility is summarized in the following matrix. X IX S IS X Conflict Conflict Conflict Conflict IX Conflict Compatible Conflict Compatible S Conflict Conflict Compatible Compatible IS Conflict Compatible Compatible Compatible A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs. Intention locks do not block anything except full table requests (for example,  LOCK TABLES ... WRITE ). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table. Transaction data for an intention lock appears similar to the following in  SHOW ENGINE INNODB STATUS and InnoDB monitor output: TABLE LOCK table ‘test‘.‘t‘ trx id 10080 lock mode IX Record Locks A record lock is a lock on an index record. For example,  SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;  prevents any other transaction from inserting, updating, or deleting rows where the value of  t.c1 is  10 . Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB  creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes” . Transaction data for a record lock appears similar to the following in  SHOW ENGINE INNODB STATUS  and InnoDB monitor output: RECORD LOCKS space id 58 page no 3 n bits 72 index ‘PRIMARY‘ of table ‘test‘.‘t‘ trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 4; hex 8000000a; asc ;;  1: len 6; hex 00000000274f; asc ’O;;  2: len 7; hex b60000019d0110; asc ;; Gap Locks A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example,  SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of  15  into column  t.c1 , whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. A gap might span a single index value, multiple index values, or even be empty. Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others. Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column 3082