The following example demonstrates NOWAIT  and SKIP LOCKED . Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using the NOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read with SKIP LOCKED  returns the requested rows except for the row that is locked by Session 1. 

# Session 1: 

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB; mysql> INSERT INTO t (i) VALUES(1),(2),(3); mysql> START TRANSACTION; mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE; +---+ | i | +---+ | 2 | +---+ 

# Session 2: 

mysql> START TRANSACTION; mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT; ERROR 3572 (HY000): Do not wait for lock. 

# Session 3: 

mysql> START TRANSACTION; mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED; +---+ | i | +---+ | 1 | | 3 | +---+ 

## 15.7.3 Locks Set by Different SQL Statements in InnoDB 

A locking read, an UPDATE , or a DELETE  generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE  conditions in the statement that would exclude the row. InnoDB  does not remember the exact WHERE  condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap”  immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. For more information, see Section 15.7.1, “InnoDB Locking” . The transaction isolation level can also affect which locks are set; see Section 15.7.2.1, “Transaction Isolation Levels” . 

If a secondary index is used in a search and the index record locks to be set are exclusive, InnoDB  also retrieves the corresponding clustered index records and sets locks on them. 

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not scan more rows than necessary. 

InnoDB  sets specific types of locks as follows. 

• SELECT ... FROM  is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE . For SERIALIZABLE  level, the search 

