locking problems

[Mike Beck]

Hi all,

i am experiencing problems with locking on innodb-tables on a 4.1.8a-Debian_6-log MySQL - Server on Debian Sarge. The Problems have been occuring for some time (and therefore some versions) now, starting with 4.0.x on Woody.
The lock wait timeouts do not occur when i convert the tables to MyIsam, but for some tables i can't do this because i need transactions or foreign keys.

i now checked with a 'show innodb status' when it happened and found this in the output:
<snip>
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 3996, query id 138195 localhost mbeck update
insert into Zeiterfassung (Personalnummer,Datum,ProjNr,Lsts,HKZ,Stunden,satz,zuschlag,Bemerkung) values(108,'2005-02-09','082 15','11','H',1,21.60,25.00,'DFK + ALB')
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `zeiterfa/Zeiterfassung` trx id 0 13974163 lock mode AUTO-INC waiting
------------------
---TRANSACTION 0 13974162, ACTIVE 28 sec, process no 16836, OS thread id 917537 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320
MySQL thread id 3998, query id 138192 localhost mbeck update
insert into Zeiterfassung (Personalnummer,Datum,ProjNr,Lsts,HKZ,Stunden,satz,zuschlag,Bemerkung) values(131,'2005-02-09','G-Kosten','32','G',1,24.50,25.00,'')
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6768 n bits 128 index `PRIMARY` of table `zeiterfa/Zeiterfassung` trx id 0 13974162 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
</snip>

so we have two 'normal' inserts, nothing flashy. i understand they need a lock, but they both should work in exactly the same way, therefore needing the saming kind of lock and this should just work first in/first out.
But instead i get a lock wait timeout, when i try to restart the transaction (=reload the page) i get a timeout again.

The problem is occuring on a rather fast server (Pentium IV which usually is having a workload of something like 0.05) with an intranet made with php4 to write down working hours on projects and stuff like this. So there are a lot of inserts.
The table looks like this:
<snip>
Field Type Null Key Default Extra
erfKey mediumint(7) unsigned PRI NULL auto_increment
Personalnummer smallint(4) unsigned MUL 0
Datum date MUL 0000-00-00
Stunden float(6,2) YES 0.00
ProjNr varchar(10) MUL
Lsts tinyint(3) 0
Bemerkung mediumtext YES NULL
HKZ enum('H','G','N') MUL H
satz decimal(5,2) unsigned 0.00
zuschlag decimal(5,2) unsigned 0.00
ausgeblendet enum('Y','N') MUL N
lastchange timestamp YES CURRENT_TIMESTAMP
</snip>

Any help appreciated
Cheers
Mike