WL#8919: InnoDB: Implement NOWAIT and SKIP LOCKED

Affects: Server-8.0   —   Status: Complete

When we do locking reads, either SELECT ... LOCK IN SHARE MODE or SELECT ... FOR 
UPDATE, if a row has been locked by another transaction, we will wait until the 
transaction ends(see http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-
reads.html). What if we just want to know whether a row is locked, or just skip 
the locked without waiting?

Let's take seats in the cinema for example: there is table named seats with two 
column(no, state), state(0: free, 1: sold).
1. I like seat 10 because its good view, just want to reserve the seat without 
2. I need one free seat, so just reserve a free seat without waiting.

This worklog will solve the above issues.
#1: SELECT * FROM seats WHERE no = 10 and state = 0 FOR UPDATE NO_WAIT;
    if the query returns error(e.g. ER_NO_WAIT_LOCK), the seat is reserved by 
    if the query returns empty set, the seat is sold already.
#2: SELECT * FROM seats WHERE no = 10 AND state = 0 LIMIT 
    if the query returns empty set, all seats are sold or reserved.

The exact SQL syntax refers to WL#3597:Implement NOWAIT and SKIP LOCKED.

Note: Skipping locked rows provides an inconsistent view of the data, so this is 
not suitable for general purpose work, but can be used to avoid lock contention 
with multiple consumers accessing a queue-like table.
F-1: Query on a table with NO_WAIT must return directly, if it can't lock a row 
immediately, without waiting for the row lock to be released.

F-2: Query on a table with SKIP LOCKED must skip a row, if it can't lock the row 
immediately, without waiting for the row lock to be released.

F-3: NO_WAIT/SKIP LOCKED shall apply only to the row level lock(s).

Non-Functional requirements:
NF-1: Implicit requirements: must work on all  platforms, do not break 
replication, backup, partitioning, FK, or any  other exiting features.
1. NO_WAIT/SKIP LOCKED process steps:
  a. Set select mode in handler interfaces:SELECT_ORDINARY, SELECT_SKIP_LOCKED, 
  b. When acquiring a lock holding by another transaction, return error 
DB_SKIP_LOCKED or DB_NO_WAIT before adding to wait queue.
  c. In row mvcc search, if DB_SKIP_LOCKED, go to next record; if DB_NO_WAIT, 
stop search and return the error.

2. For spatial index, we finally goes to record lock. Don't need to handle 
predicated lock because we always lock successfully in SELECT. 
   In rtree search we always buffer all matched records in a page, and 
lock them, no matter how may records we need(such as LIMIT 2). Suppose there are 
10 matched records on a page, and we only need 2 records in select-1, but we 
don't see the remaining 8 records on the page in select-2 with SKIP LOCKED.

3. Ignore NO_WAIT/SKIP_LOCKED for high priority transaction.
1. Define select mode in storage/innobase/include/lock0types.h
enum select_mode {
        SELECT_ORDINARY = 0,    /* default behaviour */
        SELECT_SKIP_LOCKED,     /* skip the row if row is locked */
        SELECT_NO_WAIT          /* return immediately if row is locked */

2. Set select mode in storage/innobase/handler/ha_innodb.cc
        /* Set select mode for SKIP LOCKED / NO_WAIT */
        switch (lock_type) {
        case TL_WRITE_SKIP_LOCKED:
                m_prebuilt->select_mode = SELECT_SKIP_LOCKED;
        case TL_READ_SHARED_NO_WAIT:
        case TL_WRITE_NO_WAIT:
                m_prebuilt->select_mode = SELECT_NO_WAIT;
                m_prebuilt->select_mode = SELECT_ORDINARY;

3. Return corresponding errors when locking rows in 
              if (wait_for != NULL) {
                        switch (sel_mode) {
                        case SELECT_SKIP_LOCKED:
                                err = DB_SKIP_LOCKED;
                        case SELECT_NO_WAIT:
                                err = DB_LOCK_NOWAIT;

Note: we don't try to jump the wait queue for high priority transactions.

4. Handle DB_SKIP_LOCKED in storage/innobase/row/row0sel.cc
                        case DB_SKIP_LOCKED:
                                goto next_rec;

  a. Rollback the latest statement in row_mysql_handle_errors()
  b. Return ER_LOCK_NOWAIT to mysql in convert_error_code_to_mysql()

6. Main lock functions with select mode: