Roman, you can use SHOW INNODB STATUS to look what active transactions you have and how many lock structs they have.
Also innodb_lock_monitor helps. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "Roman Neuhauser" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, June 03, 2003 7:51 PM Subject: debuggine 1205 / LOCK wait timeout exceeded errors > Hi there, > > I have a problem with LOCKs. I use InnoDB tables and transactions. > > [EMAIL PROTECTED] ~ 1001:0 > /usr/local/libexec/mysqld --version > /usr/local/libexec/mysqld Ver 4.0.13 for portbld-freebsd4.8 on i386 > [EMAIL PROTECTED] ~ 1002:0 > uname -sr > FreeBSD 4.8-RC > [EMAIL PROTECTED] ~ 1003:0 > httpd -v > Server version: Apache/1.3.26 (Unix) > Server built: Jul 23 2002 14:12:25 > > mod_php-4.3.1 linked with libmysqlclient 4.0.11a-gamma > > This is one such case (one PHP script run, taken from the query log): > > 030603 18:18:47 19 Init DB webed > 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM editor > WHERE id = 1 > 19 Init DB webed > 19 Query SELECT e.login, e.authorized, > e.firstname, e.lastname, > e.validfrom, e.validtill, > e.createdby, e.createdon, > e.changedby, e.changedon, > e.caps, e.wysiwyg > FROM editor e > WHERE id = 1 > 030603 18:18:48 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM server > WHERE id = 4 > 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM editor > WHERE id = 1 > 19 Init DB webed > 19 Query SELECT g.id AS gid, g.name > FROM acl_group g, acl_member m > WHERE g.id = m.gid > AND uid = 1 > 19 Init DB webed > 19 Query SELECT > MAX(access) AS max, > MIN(access) AS min > FROM acl_access a > WHERE a.objtype = 128 > AND a.objid = 4 > AND a.gid IN (1) > GROUP BY objid > 19 Init DB webed > 19 Query DELETE FROM acl_access > WHERE objtype = 128 > AND objid = 4 > > Here the server emitted 1205. > > DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 > [nativecode = 1205 * * LOCK wait timeout exceeded; Try restarting TRANSACTION ] > > I don't see what should cause it to behave so. I'm the only user ATM, > IOW the listing above is unaltered in any way, and represents exactly > one run of the PHP script. Notice the lack of any transaction-related or > LOCK commands (in most cases, however, the DML command is preceeded by a > BEGIN, and immediatelly [that is, after the timeout] followed by a > ROLLBACK). > > I'd love to see this resolved, and will happily provide any info > requested. > > TIA && HAND > > -- > FreeBSD 4.8-RC > 6:23PM up 14 days, 2:54, 16 users, load averages: 0.04, 0.04, 0.00 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
