• Record Locks
  • A record lock is a lock on an index record.
• Intention Locks
  • intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE).
  • 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.
• Gap lock
  • A gap might span a single index value, multiple index values, or even be empty.
  • Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.
  • If using a unique index to search and the index is not exist, It will still be a gap lock,but not a record lock
  • Innodb do not prevent different transactions from taking gap locks on the same gap.
  • a gap X-lock and a gap S-lock has the same effect: only stop other transactions from inserting to the gap.
  • Gap lock can be disable by change the transaction isolation level to READ COMMITTED.
• Next-Key Locks
  • A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
  • A next-key lock is an index-record lock plus a gap lock on the gap preceding the index record.
  • It's use for prevent phantom read.