MySQL Bugs: #32947: Misleading LOCK TABLES description in 6.0 Nutshell section

Bug #32947	Misleading LOCK TABLES description in 6.0 Nutshell section
Submitted:	3 Dec 2007 23:47	Modified:	5 Dec 2007 8:33
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	qc

Description:
From "What's new in MySQL 6.0", http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html:

You can now also issue LOCK TABLE many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously; tables are not unlocked until the transaction is committed.

This is not backwards compatible and is likely to be a serious problem for a lot of older code.  I know I've written code that assumes LOCK TABLES releases any existing locks, so I bet lots of other people have too.

I would prefer a new (optional) syntax instead to cause this behavior.

FYI this change isn't reflected on the page about LOCK TABLES syntax yet: http://dev.mysql.com/doc/refman/6.0/en/lock-tables.html

How to repeat:
FR

Suggested fix:
Change the syntax to something like 

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
    [WITHOUT UNLOCKING]

Where the optional WITHOUT UNLOCKING causes locks to accumulate.

Thank you for a reasonable feature and documentation request.

LOCK TABLE remains backward compatible.
We've implemented an extension to LOCK TABLE, and that paragraph from the What's New applies only to that extension.

The new syntax is

  LOCK TABLES ... IN SHARE MODE
  LOCK TABLES ... IN EXCLUSIVE MODE

it acquires transactional table locks, with the conventional transactional behavior (a lock stay until transaction ends, locks can be taken incrementally, etc).

This is not documented yet in the LOCK TABLE page of the manual.

See
http://mysql.bkbits.net:8080/mysql-6.0/mysql-test/include/locktrans.inc?PAGE=anno&REV=4702...
for examples.

I agree that nutshell page is somewhat misleading, though.

The section in question now reads:

  The syntax for the LOCK TABLES statement
  has been extended to support transactional table locks
  which do not commit transactions automatically. Following 
  LOCK TABLES ... IN SHARE MODE or
  LOCK TABLES ... IN EXCLUSIVE MODE, you
  can access tables not mentioned in the LOCK
  TABLES statement. You can now also issue these
  extended LOCK TABLES statements many
  times in succession, adding additional tables to the
  locked set, and without unlocking any tables that were
  locked previously. When using LOCK
  TABLES with IN SHARE MODE or
  IN EXCLUSIVE MODE tables are not
  unlocked until the transaction is committed.
  
  The behavior of LOCK TABLES when not
  using IN SHARE MODE or
  IN EXCLUSIVE MODE remains unchanged.

The LOCK TABLES Syntax section of the Manual will be updated with details of the new syntax soon. The basics are shown here:

  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

The new syntax is already supported in MySQL 6.0.3.

Forgot to change status when fixing; see previous comment.