Bug #8187 table lock for myisam within transaction
Submitted: 28 Jan 2005 19:57 Modified: 28 Feb 2005 16:49
Reporter: Andre Steffens Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.23 OS:Windows (Win2k)
Assigned to: Assigned Account CPU Architecture:Any

[28 Jan 2005 19:57] Andre Steffens
Description:
Hi,

we have a prob with mixed innodb/myisam transactions.

using 2 connections:
the first connection do select/update/insert from innodb tables and selects from a myisam table (x).

the second connection do's like the first but also updates the myisam table (x). it happens that both connections get a table lock to the myisam table.

if the "innodb timeout" is set, we get an error code 1205. otherwise both connections hang and nothing happens.

I've tried to do the update by a third connection, without the transaction. but it doesn't work.

How to repeat:
many scripts, many traffic and good luck ;-)

sorry, up to now i don't have succes to repeat in a test case!
[28 Jan 2005 20:19] Andre Steffens
with using HIGH_PRIORITY in selects, only the update statement gets a lock. all other connections work fine.
[28 Jan 2005 20:30] Andre Steffens
it seems to me that a select statement locks the myisam table and don't unlock it bevor the whole connection is closed!
the lock still exists, albeit the transaction is commited.
[30 Jan 2005 14:38] Dean Ellis
I have tried a few variations in testing and am unable to reproduce this so far.  We will need a repeatable test case (ie: SQL for both connections, execution steps) which demonstrates the behavior.
[30 Jan 2005 16:49] Heikki Tuuri
Andre,

please print SHOW INNODB STATUS from such hang situation.

Do you use

LOCK TABLES ...

?

A lock wait timeout can only result from:

1) you have a dangling transaction holding InnoDB row locks,

or

2) you are using LOCK TABLES, and the lock wait happens on the MySQL side.

Regards,

Heikki
[1 Mar 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".