Bug #24500 InnoDB please implement transactional NOWAIT locks
Submitted: 22 Nov 2006 11:55 Modified: 13 Nov 2009 4:56
Reporter: Ingo Strüwing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[22 Nov 2006 11:55] Ingo Strüwing
Description:
We are implementing transactional locking:

  LOCK TABLE[S] table [[AS] alias] lock [, table [[AS] alias] lock ...]

    lock= READ | WRITE | LOW_PRIORITY WRITE | READ LOCAL |
          IN transactional_lock_mode MODE [NOWAIT]

    transactional_lock_mode= SHARE | EXCLUSIVE

On this LOCK TABLE statement we will call handler::lock_table(). Until InnoDB reimplements this method, we will supply the following in ha_innodb.h:

        int lock_table(THD *thd, int lock_type, bool transactional,
                       bool nowait __attribute__((unused)))
        {
          /*
            Preliminarily call the pre-existing internal method for
            transactional locking and ignore non-transactional locks.
          */
          if (transactional || thd->variables.innodb_table_locks)
            return transactional_table_lock(thd, lock_type);
          return 0;
        }

The feature request is that InnoDB should take the nowait option into account.

Another thing is that the above is now also called on non-transactional LOCK TABLE before handler::external_lock() is called. For transactional locks, handler::external_lock() will not be called.

For non-transactional locks the return code does not matter. It is ignored.

How to repeat:
Hehe. The bug database forced me to enter "instructions on how the bug you are reporting can be repeated". Well, since this is a feature request, I don't see a bug here. The only way to repeat what I'm asking for is to wait for the transactional locking in the server and then try to request a NOWAIT lock. On a lock conflict you'll experience a wait.
[22 Nov 2006 13:51] Heikki Tuuri
Ingo, feature request taken. Several people have requested the NOWAIT option also to row locking.

It is intentional that in the current 5.1.12, if AUTOCOMMIT=0, InnoDB in LOCK TABLES first takes the transactional table lock on the specified table. In this way, the ordinary LOCK TABLES can be used somewhat like a transactional table lock.

ha_innodb.cc in 5.1:

ha_innobase::external lock():

"
                /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK
                TABLES if AUTOCOMMIT=1. It does not make much sense to acquire
                an InnoDB table lock if it is released immediately at the end
                of LOCK TABLES, and InnoDB's table locks in that case cause
                VERY easily deadlocks.

                We do not set InnoDB table locks if user has not explicitly
                requested a table lock. Note that thd->in_lock_tables
                can  be TRUE on some cases e.g. at the start of a stored
                procedure call (SQLCOM_CALL). */

                if (prebuilt->select_lock_type != LOCK_NONE) {

                        if (thd->in_lock_tables &&
                                thd->lex->sql_command == SQLCOM_LOCK_TABLES &&
                                thd->variables.innodb_table_locks &&
                                (thd->options & OPTION_NOT_AUTOCOMMIT)) {

                                ulint   error = row_lock_table_for_mysql(
                                        prebuilt, NULL, 0);

                                if (error != DB_SUCCESS) {
                                        error = convert_error_code_to_mysql(
                                                (int) error, user_thd);
                                        DBUG_RETURN((int) error);
                                }
                        }

                        trx->mysql_n_tables_locked++;
                }

                DBUG_RETURN(0);
"
...

This function is not used in current 5.1.12:

"
/**********************************************************************
With this function MySQL request a transactional lock to a table when
user issued query LOCK TABLES..WHERE ENGINE = InnoDB. */

int
ha_innobase::transactional_table_lock(
/*==================================*/
                                /* out: error code */
        THD*    thd,            /* in: handle to the user thread */
        int     lock_type)      /* in: lock type */
{
        row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
        trx_t*          trx;

        DBUG_ENTER("ha_innobase::transactional_table_lock");
        DBUG_PRINT("enter",("lock_type: %d", lock_type));

        /* We do not know if MySQL can call this function before calling
        external_lock(). To be safe, update the thd of the current table
        handle. */

        update_thd(thd);

        if (prebuilt->table->ibd_file_missing && !current_thd->tablespace_op) {
                ut_print_timestamp(stderr);
                fprintf(stderr, "  InnoDB error:\n"
"MySQL is trying to use a table handle but the .ibd file for\n"
"table %s does not exist.\n"
"Have you deleted the .ibd file from the database directory under\n"
"the MySQL datadir?"
"See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html\n"
"how you can resolve the problem.\n",
                                prebuilt->table->name);
                DBUG_RETURN(HA_ERR_CRASHED);
        }

        trx = prebuilt->trx;

        prebuilt->sql_stat_start = TRUE;
        prebuilt->hint_need_to_fetch_extra_cols = 0;

        prebuilt->read_just_key = 0;
        prebuilt->keep_other_fields_on_keyread = FALSE;

        if (lock_type == F_WRLCK) {
                prebuilt->select_lock_type = LOCK_X;
                prebuilt->stored_select_lock_type = LOCK_X;
        } else if (lock_type == F_RDLCK) {
                prebuilt->select_lock_type = LOCK_S;
                prebuilt->stored_select_lock_type = LOCK_S;
"
...
[5 Dec 2006 16:19] Ingo Strüwing
Hi.

During design review we changed the interface handler::lock_table():

  /*
    Lock table.

    SYNOPSIS
      handler::lock_table()
        thd                     Thread handle
        lock_type               F_RDLCK or F_WRLCK
        lock_timeout            -1 default timeout
                                0  no wait
                                >0 wait timeout in milliseconds.
        lock_transactional      If a transactional table lock is requested

    NOTE
      lock_timeout >0 is not used by MySQL currently.

    DESCRIPTION
      If 'lock_transactional' is true, this is a hint that a
      non-transactional lock was taken on the table by MySQL.

    RETURN
      HA_ERR_WRONG_COMMAND      Storage engine does not support lock_table()
      HA_ERR_UNSUPPORTED        Storage engine does not support
                                non-default timeout like NOWAIT or WAIT X
      HA_ERR_WOULD_BLOCK        Table is already locked and NOWAIT specified
      HA_ERR_LOCK_WAIT_TIMEOUT  Lock request timed out
  */
  virtual int lock_table(THD  *thd               __attribute__((unused)),
                         int  lock_type          __attribute__((unused)),
                         int  lock_timeout       __attribute__((unused)),
                         bool lock_transactional __attribute__((unused)))
  {
    return HA_ERR_WRONG_COMMAND;
  }

Just for my internal testing I preliminarily added to ha_innodb.h:

        int lock_table(THD *thd, int lock_type, int lock_timeout,
                       bool lock_transactional)
        {
          /*
            Preliminarily call the pre-existing internal method for
            transactional locking and ignore non-transactional locks.
          */
          if (!lock_timeout)
          {
            /* Preliminarily show both possible errors for NOWAIT. */
            if (lock_type == F_WRLCK)
              return HA_ERR_UNSUPPORTED;
            else
              return HA_ERR_WOULD_BLOCK;
          }
          if (lock_transactional || thd->variables.innodb_table_locks)
            return transactional_table_lock(thd, lock_type);
          return 0; /* Ignore transactional locks here. */
        }
[30 Sep 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)