Re: InnoDB locks disappear

[Wouter Zelle]

Heikki,

Unfortunately it is not that easy. I've set the 
innodb_lock_wait_timeout to 1 because I want locks to fail quickly, 
so my program can move on to the next request. In pseudocode:

Fetch a bunch of requests with status=unprocessed
Try to obtain a lock through a select * from x for update
If lock: process
If lock-timeout: move on to the next request.

This works perfectly except that the locks disappear suddenly for no 
good reason at all. This takes far longer than the 
innodb_lock_wait_timeout. On second thought, it doesn't seem to be a 
timer at all, because the locks don't end sooner if I step through my 
program in the debugger. I'm not sure, but it seems to take a number 
of queries to do it. Could InnoDB release locks after x failed 
attempts to lock the same row?

The Inno Lock Monitor is supposed to be useful for debugging, does 
the report in my original post contain anything that could cause 
this? I think that the table locks seem fishy since I only use record 
locks at that point in my code. Could there be something wrong with 
that?

Wouter Zelle

--- Part of the INNODB MONITOR OUTPUT
--
TABLE LOCK table rosetta/request trx id 0 370099 lock_mode IX
RECORD LOCKS space id 0 page no 50 n bits 80 table rosetta/request 
index PRIMARY
  trx id 0 370099 lock_mode X
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 
73757072656d756d00; asc
  supremum.;;
Record lock, heap no 2 RECORD: info bits 0 0: len 8; hex 
80000000000000c5; asc .
.......;; 1:
Record lock, heap no 10 RECORD: info bits 0 0: len 8; hex 80000000000000e8; asc
.......Þ;; 1:
Suppressing further record lock prints for this page
TABLE LOCK table rosetta/rosetta_user trx id 0 370099 lock_mode IX
RECORD LOCKS space id 0 page no 53 n bits 288 table 
rosetta/rosetta_user index P
RIMARY trx id 0 370099 lock_mode X
Record lock, heap no 220 RECORD: info bits 0 0: len 8; hex 
8000000000000d28; asc
  .......(;; 1:
---

>the default for
>
>innodb_lock_wait_timeout
>
>is 50 seconds in recent versions. It may be that the manual at www.mysql.com
>is outdated and claims it is infinite.
>
>A lock wait timeout rolls back the whole transaction and releases all locks.
>
>You should check the return value of your queries and look if they are
>reporting lock wait timeouts or other errors.
>
>>  I would really appreciate some help,
>>
>>  Wouter Zelle
>
>Best regards,
>
>Heikki Tuuri
>Innobase Oy
>---
>Order technical MySQL/InnoDB support at https://order.mysql.com/
>See http://www.innodb.com for the online manual and latest news on InnoDB

--