From: Ben Clewett
Date: December 12 2007 1:14pm
Subject: InnoDB table which would not unlock
List-Archive: http://lists.mysql.com/mysql/210446
Message-Id: <475FDEBA.7000800@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear MySql,

Using 5.0.41 I had a single innodb table which would not unlock.  I 
wonder if this might be a bug, or an issue that is known to be fixed in 
later versions?

Any DML like this example:

   UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running'

Would result in:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

However there were no visible locks:

show open tables like 'ws_queue';
+-------------+------------------+--------+-------------+
| Database    | Table            | In_use | Name_locked |
+-------------+------------------+--------+-------------+
| Web_Members | ws_queue         |      0 |           0 |
+-------------+------------------+--------+-------------+

This seems to be an error, and was fixed by bouncing the server, 
something I do not like doing in the middle of a working day.

At the time Innodb status showed the following.  Can any person help me 
understand what this is telling me?

---TRANSACTION 0 3683516087, ACTIVE 6 sec, process no 18537, OS thread 
id 1149135168 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216
MySQL thread id 2738280, query id 50900786 172.16.16.39 dba Updating
UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running'
------- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5619722 n bits 240 index `PRIMARY` of 
table `Web_Members/ws_queue` trx id 0 3683516087 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 20; compact format; 
info bits 0
  0: len 4; hex 0002b3f8; asc     ;; 1: len 6; hex 0000db8363ad; asc 
  c ;; 2: len 7; hex 00001a87d72205; asc      " ;; 3: len 1; hex 80; asc 
  ;; 4: len 1; hex 01; asc  ;; 5: len 4; hex 475fbf76; asc G_ v;; 6: len 
7; hex 63685f61637473; asc ch_acts;; 7: len 8; hex 3236434539424134; asc 
26CE9BA4;; 8: len 8; hex 0000000000107523; asc       u#;; 9: len 4; hex 
80000000; asc     ;; 10: len 8; hex 4a414d455349524c; asc JAMESIRL;; 11: 
len 4; hex 800086bc; asc     ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; 
asc ;; 14: SQL NULL; 15: len 4; hex 80000000; asc     ;; 16: len 4; hex 
80000001; asc     ;; 17: len 1; hex 80; asc  ;; 18: len 4; hex 64626d73; 
asc dbms;; 19: len 1; hex 00; asc  ;;

Regards,

Ben Clewett.