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
