Wouter,
now that I read the query log more carefully I notice that it was thread 23
who was holding the row locks.
>From the query log wee see that thread 23 says 'Quit' around 14:47.
Could it be that you have some 5 minute timeout for a silent connection?
When the connection is terminated, MySQL rolls back the transaction, which
explains why the locks disappeared.
Regards,
Heikki
.......
I've cleaned up the log a bit for easy reading.
---
14:42:42 23 Connect wouter@localhost on
23 Init DB rosetta
23 Query SHOW VARIABLES
23 Query SET autocommit=0
23 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:43:13 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:43:18 24 Connect wouter@localhost on
24 Init DB rosetta
24 Query SHOW VARIABLES
24 Query SET autocommit=0
24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:43:43 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:44:12 24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:44:33 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:44:53 24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:45:18 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:45:24 24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:45:42 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:45:54 24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:46:23 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
22 Query commit
14:46:41 24 Query SELECT [t0.columns] FROM REQUEST t0,
ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND
T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE
14:47:08 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE
t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC
14:47:09 22 Query commit
23 Quit
14:47:28 3 Query DROP TABLE innodb lock monitor
---End Query Log
----- Original Message -----
From: ""Heikki Tuuri"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, September 06, 2002 7:20 PM
Subject: Re: InnoDB locks disappear
> Wouter,
>
> the Lock Monitor output tells that trx 370099 has been committed or rolled
> back by the user. That is why the locks have disappeared.
>
> Have you taken into account the following:
> 8.5 When does MySQL implicitly commit or rollback a transaction?
> a.. MySQL has the autocommit mode switched on in a session if you do not
> do set autocommit=0. In the autocommit mode MySQL does a commit after each
> SQL statement, if that statement did not return an error.
> b.. If an error is returned by an SQL statement, then the
commit/rollback
> behavior depends on the error. See section 13 for details.
> c.. The following SQL statements cause an implicit commit of the current
> transaction in MySQL: ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE,
DROP
> TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES. The CREATE
TABLE
> statement in InnoDB is processed as a single transaction which is
> independent of the current user transaction. It means that a ROLLBACK from
> the user does not undo CREATE TABLE statements the user made during his
> transaction.
> d.. If you in the autocommit mode use LOCK TABLES it is like BEGIN
> TRANSACTION in the sense that it switches the autocommit mode off until
you
> call UNLOCK TABLES.
> e.. If you you have the autocommit mode off and end a connection without
> calling an explicit COMMIT of your transaction, then MySQL will roll back
> your transaction.
> Regards,
>
> Heikki
>
>
> ..........
> Heikki,
>
> Yep. That's why I use seperate connections for holding the lock and
> to do the subsequent locking attempts. Besides, if that were the
> problem, I would see the lock disappear at the very first failed
> locking attempt, but that's not the case.
>
> I thought it might be a connection timeout, but the settings don't
> seem to allow for that (unless my debug session takes 8 hours).
> Aargh, why isn't there a nice log that tells me why the lock is
> released. Or is there? I remember a debug-option, do you think that
> may work?
>
> Wouter Zelle
>
> >----- Original Message -----
> >From: "Wouter Zelle" <[EMAIL PROTECTED]>
> >
> > > 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
>
> --
> sql, query, stupid filter
>
>
>
>
> ---------------------------------------------------------------------
> 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
