LOCK TABLE command that is similar in behaviour to the same command in Oracle,
DB2, and other databases.
That means:
1. LOCK TABLE does _not_ commit a transaction
2. after LOCK TABLE one can access tables not mentioned in the LOCK TABLE statement
3. One can issue LOCK TABLE many times, adding more tables to the locked set,
LOCK TABLE does not unlock tables that were locked previously
4. tables are unlocked at the end of transaction
Note added by Trudy Pelzer, 2006-10-31
During today's Dev-MT meeting, a decision was made that
this feature will not be done for 5.1. Instead, it will
be coded and pushed to the Alcatel tree, for their use,
and then later to 5.2, for all users.
-- end note
The syntax is:
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
Meanings: EXCLUSIVE technically does not mean WRITE since users could
certainly ask for exclusive access when they're only reading, but the
usual association might be "SHARE is for READ, EXCLUSIVE is for WRITE".
"NOWAIT" means "return an error instead of waiting"; default behaviour
is to wait.
Statements in the current (pre-5.2) syntax, with "READ" or "WRITE" or
"LOW_PRIORITY WRITE" or "READ LOCAL", will not require any changes.
Statements in the new syntax, with "IN SHARE MODE" or "IN EXCLUSIVE
MODE" or "IN SHARE MODE NOWAIT" or "IN EXCLUSIVE MODE NOWAIT", are what
we mean when we say "transactional LOCK TABLE" statements. It is legal
to combine, for example:
LOCK TABLES t1 READ, t2 IN EXCLUSIVE MODE;
But when a standard READ/WRITE table lock is present, all transactional
lock requests are converted to standard READ/WRITE table locks (explained
in detail below).
Internally, LOCK ... IN { SHARE | EXCLUSIVE } MODE will not set any
locks on the upper level, but will call handler::lock_table(lock_level)
only. handler::lock_table(lock_level) will return an error, if not
supported.
NOWAIT clause is passed to the handler::lock_table() method too.
This means we're saying lock_table(lock_level, wait_or_nowait).
The last argument is an integer, 0 means "no wait", -1 means "wait":
Guilhem proposes, in dev-private thread
Re: design questions Re: WL#3594 - transactional LOCK TABLE Testing,
that both NOWAIT and WAIT [X] should eventually be supported, so
the handler call should allow for timeout support. This will then be
implemented by wait_or_nowait > 0.
Possible errors due to a return from handler::lock_table:
- "Storage engine %s does not support NOWAIT"
- "A table is already locked and NOWAIT was specified"
- "Storage engine %s does not support SHARE or EXCLUSIVE"
(this will be common, we only expect some transactional
storage engines such as InnoDB or NDB to support the new
syntax)
- "Incompatible lock mode: One of the tables that you are
trying to lock with SHARE or EXCLUSIVE has already been
locked with READ or WRITE" (MySQL won't allow both kinds in a session
at the same time)
- Incompatible state due to a previous statement, see below
When handler::lock_table rejects due to non-support / incompatible mode:
- if sql_mode is STRICT_TRANS_TABLES or STRICT_ALL_TABLES,
it's a real error, no tables are locked.
- otherwise, it's a warning and MySQL converts the
SHARED/EXCLUSIVE lock to READ/WRITE lock as appropriate.
SHARED to READ, EXCLUSIVE to WRITE.
Here is an attempt to describe "incompatible lock mode" a different way:
The old lock modes
("READ" | "WRITE" | "LOW_PRIORITY WRITE" | "READ LOCAL")
are not compatible with the new lock modes
("IN SHARE MODE" | "IN EXCLUSIVE MODE").
If old and new lock modes appear in the same statement for the same table,
or for different tables,
then the old lock mode is assumed and a warning appears, or (strict mode)
an error appears.
For example:
LOCK TABLES t1 IN EXCLUSIVE MODE, t2 READ
causes
Warning: Incompatible lock mode ... EXCLUSIVE MODE is interpreted as WRITE
or
Error: Incompatible lock mode ... EXCLUSIVE and READ
Old LOCK rules apply as usual - for example trying to LOCK a MyISAM
table IN SHARE MODE, being converted to LOCK myisam_table READ, will
behave exactly as the latter, for example, one won't be able to access
not locked tables, old READ/WRITE locks will be automatically released.
A notable exception is the behavior inside a transaction - LOCK READ
implicitly commits any existing transaction, LOCK IN SHARE MODE never
does it, when converted to LOCK READ it'll abort with an error if there
is a running transaction.
In autocommit mode locks in SHARE/EXCLUSIVE mode will be ignored
(optimized away). A transactional lock is meaningless if the
transaction ends as soon as the LOCK statement ends.
But the LOCK statement does still cause a wait if incompatible table
locks exist. So it's definitely not a "no-op".
UNLOCK TABLE works exactly as before, no changes.
Although Oracle allows locking of partitions and subpartitions,
MySQL has no plans for anything similar.
Additionally - as a cleanup - LOCK TABLE ... READ/WRITE shall
call handler::lock_table(), it makes table locks explicit in the
storage engine. It allows to get rid of hacks that InnoDB does now
(thd->in_lock_tables && thd->lex->sql_command == SQLCOM_LOCK_TABLES
in external_lock()).
[Peter suggested a few hypothetical scenarios:
If LOCK TABLE doesn't start a transaction, there might be earlier
statements in the same transaction, causing new possibilities.
If LOCK TABLE t1 IN EXCLUSIVE MODE:
If earlier statement was LOCK TABLE t1 IN SHARE MODE:
/* it's up to the storage engine */
If earlier statement was LOCK TABLE t1 IN EXCLUSIVE MODE:
No action, or warning.
(LOCK TABLE t1 IN {SHARE|EXCLUSIVE} MODE will not automatically
remove a previous lock.)
If earlier statement was LOCK TABLE READ:
Continue, it's not an error
If earlier statement was INSERT/UPDATE/DELETE:
/* There's already a row-level lock, it may be cancelled,
it's up to the storage engine. */
If earlier statement was FLUSH TABLES WITH READ LOCK:
/* the lock is granted */
If earlier statement was SELECT ... LOCK IN SHARE MODE:
/* There are already row-level locks, they may be cancelled,
it's up to the storage engine. */
Also there might be later statements in the same transaction.
If ROLLBACK TO SAVEPOINT:
If earlier statement was LOCK TABLE t1 IN EXCLUSIVE MODE:
/* No error */
If INSERT DELAYED:
If earlier statement was LOCK TABLE t1 IN EXCLUSIVE MODE:
/* No change */
If DROP TABLE:
If earlier statement was LOCK TABLE t1 IN EXCLUSIVE MODE:
Remove the lock. (One just tries to live with the bug here.)
]
==========================================
Current behaviour:
LOCK TABLE automatically removes a previous lock.
LOCK TABLE automatically commits a transaction.
UNLOCK TABLE automatically commits a transaction
START TRANSACTION automatically removes a previous lock
This is retained for READ/WRITE locks. SHAE/EXCLUSIVE locks behave
slightly different as explained above.
==========================================
Compatibility:
On Jan 6, 2005, Peter G wrote:
>MS-SQL and Firebird don't have an explicit LOCK TABLE statement.
>
>* Syntax for the others is (read down under each column):
>
>DB2 Oracle PostgreSQL Sybase ASE
>---------- ---------- ---------- ----------
>LOCK LOCK LOCK LOCK
>TABLE TABLE [TABLE] TABLE
>name name name name
> [,name...] [,name...]
>IN IN IN IN
>{SHARE {SHARE {SHARE {SHARE
>|EXCLUSIVE} |EXCLUSIVE} |EXCLUSIVE} |EXCLUSIVE}
>MODE MODE MODE MODE
> [ NOWAIT ] [ WAIT [n]
> | NOWAIT ]
>
>Example: (DB2) LOCK TABLE x IN SHARE MODE
>
>But Oracle and/or PostgreSQL allow additional modes:
>ACCESS EXCLUSIVE, ACCESS SHARE, EXCLUSIVE, ROW EXCLUSIVE,
>ROW SHARE, SHARE, SHARE ROW EXCLUSIVE, SHARE UPDATE,
>SHARE UPDATE EXCLUSIVE.
>
>* Privileges vary. Generally you must have SELECT privilege
>on the table(s), but for DB2 it's enough to have SYSADM privilege,
>for Sybase ASA you need more privileges if you say EXCLUSIVE.
>
>* UNLOCK TABLE is supported by nobody. The unlocking happens
>at transaction end.
EXCLUSIVE and NOWAIT shall not be reserved words.
Related bugreports:
BUG#3275 INSERT INTO table1 AS alias1
BUG#5665 problem with lock table sql command
BUG#6150 Incremental LOCKs or stacked LOCKs
BUG#9514 Provide a way to select a lock status of a row or rows
BUG#24500 InnoDB please implement transactional NOWAIT locks
BUG#46663 Table locked in EXCLUSIVE MODE is still accessible from other connections
BUG#46664 NOWAIT in SHARE mode causes immediate timeout although the table is
not locked
BUG#46678 Transactional locks do not protect a table from concurrent DROP or RENAME
References
----------
Oracle "LOCK"
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_9015.htm#sthref6976
DB2 "LOCK":
http://itsuite.it.brighton.ac.uk/suite/docs/db2sqlref/frame3.htm#sqls0643
Sybase "LOCK":
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/55452#X
PostgreSQL "LOCK":
http://www.postgresql.org/docs/8.1/static/sql-lock.html
most of the details - see above
on the innodb side we'll try to use ha_innodb::transactional_table_lock,
which was created exactly for this purpose.
We just need to rename it to ::lock_table and use as above.
Add the following new functions:
/*
Try to get transactional table locks for the tables in the list.
SYNOPSIS
try_transactional_lock()
thd Thread handle
table_list List of tables to lock
DESCRIPTION
This is called if transactional table locks are requested for all
tables in table_list and no non-transactional locks pre-exist.
RETURN
0 OK. All tables are transactional locked.
1 Error: must fall back to non-transactional locks.
-1 Error: no recovery possible.
*/
int try_transactional_lock(THD *thd, TABLE_LIST *table_list);
/*
Check if transactional locks are converted to non-transactional.
SYNOPSIS
check_transactional_lock()
thd Thread handle
table_list List of tables to lock
DESCRIPTION
Report warnings or errors if transactional locks need to be
converted to non-transactional. In strict mode the conversion is
prohibited. Otherwise we warn about it.
RETURN
0 OK. Proceed with non-transactional locks.
-1 Error: Lock conversion is prohibited.
*/
int check_transactional_lock(THD *thd, TABLE_LIST *table_list);
/*
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 NOWAIT
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, int lock_type,
int lock_timeout, bool lock_transactional);
Extend the LOCK TABLE statement execution by calling
try_transactional_lock() and check_transactional_lock().
Extend the TABLE_LIST structure:
/* For transactional locking. */
int lock_timeout; /* NOWAIT or WAIT [X] */
bool lock_transactional; /* If transactional lock requested. */
Add a parser helper type:
struct st_table_lock_info
{
thr_lock_type lock_type;
int lock_timeout;
bool lock_transactional;
};
Extend the LEX structure:
bool lock_transactional; /* For LOCK TABLE ... IN ... MODE */
Add lex keywords EXCLUSIVE and NOWAIT.
Add parser branches for the new syntax. Keep track of the resulting lock
method (LEX::lock_transactional). Initialize from !THD::locked_tables and
set to FALSE if any of the requested locks is non-transactional. Store
the requested lock method in each TABLE_LIST for later reference. We
need to warn or abort if the lock methods needs to be changed.
Add a handler error return code:
#define HA_ERR_WOULD_BLOCK <nr> /* Operation would block without NOWAIT */
Add error messages:
ER_WARN_AUTO_CONVERT_LOCK Converted to non-transactional lock
ER_NO_AUTO_CONVERT_LOCK_STRICT Cannot convert to non-transactional lock
in strict mode
ER_NO_AUTO_CONVERT_LOCK_TRANSACTION Cannot convert to non-transactional lock
in an active transaction
ER_WOULD_BLOCK Operation would block
You must be logged in to tag this worklog