MySQL Bugs: #3299: LOCK TABLE not-expected behaviour

Bug #3299	LOCK TABLE not-expected behaviour
Submitted:	26 Mar 2004 6:20	Modified:	15 Oct 2004 12:12
Reporter:	marcello soffritti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.16	OS:	HP/UX (HPUX 11.11)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
I have two DB sessions: session_A and session_B, connecting to MySQL
RDBMS using MySQL native API.
session_A does a "select * from marsof for update"
then session_B does "lock table marsof write"
Why MySQL doesn't block the session_B request?
Has session_A locked all marsof rows?

If I repeat the above test with ORACLE, the behaviour is different:
session_B is waiting for session_A to unlock the marsof rows.
The MySQL behaviour is so not ORACLE compliant.
I have read in InnoDB manual (www.innodb.com/ibman.php) the following:
"...since MySQL does know about row level locks, it is possible that you
get a table lock on a table where another user currently has row level
locks. But that does not put transaction integrity into danger. "

Is it possibile to solve this problem? It's important for me
the ORACLE behaviour.

How to repeat:
Here it is marsof table:

mysql> desc marsof;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| eta   | int(11) |      | PRI | NULL    | auto_increment |
| tipo  | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

mysql> select * from marsof; 
+-----+------+
| eta | tipo |
+-----+------+
|   1 |    1 |
|   2 |    2 |
|   7 |    7 |
|   8 |    8 |
+-----+------+
4 rows in set (0.03 sec)

session_A sql: "select * from marsof for update"
session_B sql: "lock table marsof write"

Hi!

Currently, that is teh expected behavior. MySQL is not aware of InnoDB's row locks when you do LOCK TABLES.

A workaround is to use a 'semaphore row' as a table lock, and rely entirely on InnoDB's row locks.

See the end of section http://www.innodb.com/ibman.php#Cope.with.deadlocks

Regards,

Heikki

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

LOCK TABLES for InnoDB was implemented in ChangeSet@1.1794.1.1.  This fix, which was accidentally released too early, introduced a bug in the auto_inc locking.  That bug was fixed in ChangeSet@1.1811.1.1, which should be included in MySQL 4.0.19 and 4.1.2.

Starting with MySQL 4.0.22 and 4.1.7, there will be a startup option and settable session variable
innodb_table_locks_old_behavior, which makes LOCK TABLES behave in the old way: no InnoDB lock will be taken. (The new behavior has caused some problems for those who were using LOCK TABLES with InnoDB, especially in AUTOCOMMIT=1 mode.)

In a code review, the option was renamed to innodb_table_locks.
By default, InnoDB 4.0.22 and 4.1.7 will behave like before 4.0.20 and 4.1.2: LOCK TABLES acquires no InnoDB lock. In order to make LOCK TABLES acquire InnoDB locks (as in 4.0.20, 4.0.21, 4.1.2..4.1.6), you will have to set innodb_table_locks=1.