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 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.

Hi Ben,

On Dec 12, 2007 8:14 AM, Ben Clewett <ben@clewett.org.uk> wrote:
> 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 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 ;;

The SHOW OPEN TABLES statement isn't going to show you anything
relevant here, because the lock is on the InnoDB level, not the MySQL
server level. The server is unaware of storage-engine locks.

Another transaction had the record locked. The transaction needed to
commit or rollback to release the locks. You can see locks held (as
opposed to locks waited for) by using the InnoDB lock monitor, or via
a patch I created.

http://www.xaprb.com/blog/2007/09/18...db-lock-waits/
http://bugs.mysql.com/bug.php?id=29126

You may also find innotop (http://innotop.sourceforge.net/) ful.