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: | |
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
[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)