List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 5 1999 10:30am
Subject:Serious locking problem
View as plain text  
Hi!

>>>>> "Patrick" == Patrick Verdon <patrick.verdon@stripped> writes:

Patrick> Hi,

Patrick> I have a serious deadlock problem occurring where
Patrick> mysqld dumps core. I'm making use of LOCK TABLES
Patrick> and I can reproduce the problem every time by 
Patrick> clicking the submit button simultaneously on two
Patrick> different browsers (it's a Web application). 
Patrick> mysqld dumps core and I get these messages in 
Patrick> Apache's error_log:

Patrick> DBD::mysql::db do failed: Lost connection to MySQL server during query at
> /usr/local/kan_lib/SWW/CallLogging.pm line 12624.
Patrick> DBD::mysql::db do failed: Lost connection to MySQL server during query at
> /usr/local/kan_lib/SWW/CallLogging.pm line 12669.
Patrick> [Wed Aug  4 23:45:08 1999] [error] Couldn't do: Lost connection to MySQL
> server during query at
Patrick> /kan/www-dev/sww-cla-dev/document_root/perl/call_logging/call_logging.pl line
> 238

Patrick> [Wed Aug  4 23:45:08 1999] [error] Couldn't lock tables: Lost connection to
> MySQL server during query at
Patrick> /kan/www-dev/sww-cla-dev/document_root/perl/call_logging/call_logging.pl line
> 238

Patrick> The set of queries that are trying to run are as
Patrick> follows:

Patrick> LOCK TABLES keyword_tmp_table_1 WRITE, keyword_tmp_table_2 WRITE, call_log
> READ, call_log_product READ, contact READ, keyword READ,
Patrick> keyword_call_log  READ;

Patrick> INSERT INTO keyword_tmp_table_1 SELECT distinct(call_log.call_log_id) FROM
> call_log, call_log_product, contact, keyword,
Patrick> keyword_call_log WHERE call_log.call_log_product_id =
> call_log_product.call_log_product_id AND call_log.contact_id = contact.contact_id
Patrick> AND keyword.keyword_id =
Patrick> keyword_call_log.keyword_id AND call_log.call_log_id =
> keyword_call_log.call_log_id AND keyword.keyword like 'oracle%';

Patrick> INSERT INTO keyword_tmp_table_2 SELECT
> distinct(keyword_tmp_table_1.call_log_id) FROM keyword_tmp_table_1, keyword,
> keyword_call_log
Patrick> WHERE keyword.keyword_id = keyword_call_log.keyword_id AND
> keyword_tmp_table_1.call_log_id = keyword_call_log.call_log_id AND
Patrick> keyword.keyword like 'magik%';

Patrick> SELECT call_log.call_log_id, call_log.load_date, call_log.load_time,
> call_log.open_date, call_log.open_time, call_log_product.product,
Patrick> call_log.brief_description, contact.first_name, contact.last_name FROM
> keyword_tmp_table_2, call_log, call_log_product, contact WHERE
Patrick> keyword_tmp_table_2.call_log_id
Patrick> = call_log.call_log_id AND call_log.call_log_product_id =
> call_log_product.call_log_product_id AND call_log.contact_id =
Patrick> contact.contact_id ORDER BY load_date DESC, load_time DESC LIMIT 0, 10;

Patrick> DELETE FROM keyword_tmp_table_2;
Patrick> DELETE FROM keyword_tmp_table_1;

Patrick> UNLOCK TABLES;

Patrick> I'm running MySQL 3.22.25 on Solaris x86 2.6, compiled with 
Patrick> egcs 1.1.1.

Hi!

I was just able to reproduce this in MySQL 3.22.25; It works however
in MySQL 3.23, so I am just now trying to check what I can move back
from 3.23.

In the mean time:

Change the code:

DELETE FROM keyword_tmp_table_2;
DELETE FROM keyword_tmp_table_1;

to

DELETE FROM keyword_tmp_table_2 WHERE 1=1;
DELETE FROM keyword_tmp_table_1 WHERE 1=1;

and it will work.

(This is a bug in the optimization of deleting all rows in a table)

Regards,
Monty

Thread
Serious locking problemPatrick Verdon5 Aug
  • Serious locking problemMichael Widenius5 Aug
  • Serious locking problemMichael Widenius5 Aug