ERROR 1205: Lock wait timeout exceeded; AHHHH!

[Mark Hazen]

On 3.23.52, I run this query:

DELETE FROM global WHERE user_id='24512' AND post_id='15';

And get this response:
ERROR 1205: Lock wait timeout exceeded; Try restarting transaction

This table has 50 million rows (dynamic length) in it and takes up 8 gigs.

Please help... the only other solution to fixing this problem that I've
heard is restarting the server. I'm sorry... that isn't a solution. By
3.23.52, problems like this should not exist.

Best,
Mark

---------------------------------------------------------------------
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 <mysql-thr...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-myodbc=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[Heikki Tuuri]

Mark,

----- Original Message -----
From: ""Mark Hazen"" <mark_...@shawfamily.com>
Newsgroups: mailing.database.mysql
Sent: Monday, September 16, 2002 11:48 PM
Subject: ERROR 1205: Lock wait timeout exceeded; AHHHH!

> On 3.23.52, I run this query:
>
> DELETE FROM global WHERE user_id='24512' AND post_id='15';
>
> And get this response:
> ERROR 1205: Lock wait timeout exceeded; Try restarting transaction
>
> This table has 50 million rows (dynamic length) in it and takes up 8 gigs.
>
> Please help... the only other solution to fixing this problem that I've
> heard is restarting the server. I'm sorry... that isn't a solution. By
> 3.23.52, problems like this should not exist.

there is some other transaction probably setting locks on the rows you are
trying to delete. For example, doing inserts there.

Try limiting the size of the DELETE with the LIMIT clause:

"
6.4.6 DELETE Syntax

DELETE [LOW_PRIORITY | QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
"

Also, I assume you have an index on (user_id, post_id) so that MySQL does
not do a table scan when doing the delete.

> Best,
> Mark

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

sql query