MySQL Forums
Forum List  »  InnoDB

locking problems
Posted by: Mike Beck
Date: February 09, 2005 10:47AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
locking problems
3863
February 09, 2005 10:47AM
2579
February 13, 2005 04:21AM
2644
February 15, 2005 10:06AM
2462
February 15, 2005 02:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.