Wouter, ----- Original Message ----- From: "Wouter Zelle" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, September 04, 2002 10:59 AM Subject: Re: InnoDB locks disappear
> 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 did you take into account that a lock wait timeout error rolls back the WHOLE transaction and releases ALL locks of that transaction? Regards, Heikki > 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 > > -- > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php