| 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 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)
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.