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ä
Triage: D5 (Feature request)

[26 Mar 2004 6:20] marcello soffritti
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"
[26 Mar 2004 6:25] Heikki Tuuri
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
[7 May 2004 11:38] Marko Mäkelä
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.
[15 Oct 2004 12:12] Marko Mäkelä
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.)
[20 Oct 2004 20:41] Marko Mäkelä
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.