MySQL Bugs: #3276: Deadlock with MyISAM

Bug #3276	Deadlock with MyISAM
Submitted:	23 Mar 2004 16:58	Modified:	26 Mar 2004 2:33
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.16	OS:	Linux (Redhat ES 3)
Assigned to:		CPU Architecture:	Any

Description:
Deadlock on a MyISAM table with no fulltext index.
It appears to be a write deadlock.
Show processlist output:
| Id      | User | Host      | db               | Command | Time | State  | Info                                                                                                 
|
+---------+------+-----------+------------------+---------+------+--------
+--------------------------------------------------------------------------------------
----------------+
| 2545196 | host | localhost | database_name   | Query   | 1581 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2545624 | host | localhost | database_name   | Query   | 1553 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2545893 | host | localhost | database_name   | Query   | 1525 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2546940 | host | localhost | database_name   | Query   | 1462 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2546958 | host | localhost | database_name   | Query   | 1460 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2547079 | host | localhost | database_name   | Query   | 1452 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2547370 | host | localhost | database_name   | Query   | 1435 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27206 WHERE topic_id='4885' and pid='786852' AND bpath=2720 |
| 2547538 | host | localhost | database_name   | Query   | 1421 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2547826 | host | localhost | database_name   | Query   | 1401 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27206 WHERE topic_id='4885' and pid='786852' AND bpath=2720 |
| 2548243 | host | localhost | database_name   | Query   | 1374 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2548371 | host | localhost | database_name   | Query   | 1361 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2548845 | host | localhost | database_name   | Query   | 1331 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2548875 | host | localhost | database_name   | Query   | 1330 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2548953 | host | localhost | database_name   | Query   | 1325 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786872' AND bpath=27210 |
| 2549031 | host | localhost | database_name   | Query   | 1320 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786869' AND bpath=27210 |
| 2549037 | host | localhost | database_name   | Query   | 1320 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549051 | host | localhost | database_name   | Query   | 1319 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549077 | host | localhost | database_name   | Query   | 1318 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549193 | host | localhost | database_name   | Query   | 1312 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786869' AND bpath=27210 |
| 2549283 | host | localhost | database_name   | Query   | 1307 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786869' AND bpath=27210 |
| 2549319 | host | localhost | database_name   | Query   | 1305 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549392 | host | localhost | database_name   | Query   | 1301 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549527 | host | localhost | database_name   | Query   | 1292 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2549954 | host | localhost | database_name   | Query   | 1271 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786864' AND bpath=27210 |
| 2550169 | host | localhost | database_name   | Query   | 1255 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550178 | host | localhost | database_name   | Query   | 1255 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550191 | host | localhost | database_name   | Query   | 1254 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550205 | host | localhost | database_name   | Query   | 1253 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550219 | host | localhost | database_name   | Query   | 1252 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550668 | host | localhost | database_name   | Query   | 1224 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='676' and pid='786867' AND bpath=27210 |
| 2550701 | host | localhost | database_name   | Query   | 1221 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550910 | host | localhost | database_name   | Query   | 1207 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550956 | host | localhost | database_name   | Query   | 1205 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2550958 | host | localhost | database_name   | Query   | 1205 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET author_id='182',use_sig='1',ip_address='68.173.151.66',post_date=' |
| 2551157 | host | localhost | database_name   | Query   | 1186 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET author_id='182',use_sig='1',ip_address='68.173.151.66',post_date=' |
| 2551185 | host | localhost | database_name   | Query   | 1186 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2551916 | host | localhost | database_name   | Query   | 1142 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2552040 | host | localhost | database_name   | Query   | 1135 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2552249 | host | localhost | database_name   | Query   | 1123 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2552318 | host | localhost | database_name   | Query   | 1119 | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET author_id='115',use_sig='1',ip_address='68.103.148.3',post_date='1 |
| 2552527 | host | localhost | database_name   | Query   | 1105 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553026 | host | localhost | database_name   | Query   | 1074 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553052 | host | localhost | database_name   | Query   | 1073 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553073 | host | localhost | database_name   | Query   | 1072 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553118 | host | localhost | database_name   | Query   | 1068 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553679 | host | localhost | database_name   | Query   | 1035 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2553891 | host | localhost | database_name   | Query   | 1021 | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2554452 | host | localhost | database_name   | Query   | 985  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2554878 | host | localhost | database_name   | Query   | 960  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2554938 | host | localhost | database_name   | Query   | 956  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2555096 | host | localhost | database_name   | Query   | 945  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2555198 | host | localhost | database_name   | Query   | 939  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2555837 | host | localhost | database_name   | Query   | 901  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2555885 | host | localhost | database_name   | Query   | 899  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2555948 | host | localhost | database_name   | Query   | 896  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2556102 | host | localhost | database_name   | Query   | 887  | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET author_id='182',use_sig='1',ip_address='68.173.151.66',post_date=' |
| 2556221 | host | localhost | database_name   | Query   | 881  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2556826 | host | localhost | database_name   | Query   | 845  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2557270 | host | localhost | database_name   | Query   | 820  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2557700 | host | localhost | database_name   | Query   | 792  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2557834 | host | localhost | database_name   | Query   | 781  | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786869' AND bpath=27210 |
| 2557962 | host | localhost | database_name   | Query   | 773  | Locked | UPDATE LOW_PRIORITY 
ibf_post5 SET bpath=-27210 WHERE topic_id='644' and pid='786872' AND bpath=27210 |
| 2558133 | host | localhost | database_name   | Query   | 764  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2559064 | host | localhost | database_name   | Query   | 719  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2559455 | host | localhost | database_name   | Query   | 698  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2559650 | host | localhost | database_name   | Query   | 685  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2559714 | host | localhost | database_name   | Query   | 681  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2559855 | host | localhost | database_name   | Query   | 673  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2560241 | host | localhost | database_name   | Query   | 649  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2560356 | host | localhost | database_name   | Query   | 640  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2560415 | host | localhost | database_name   | Query   | 637  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2560868 | host | localhost | database_name   | Query   | 608  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2561019 | host | localhost | database_name   | Query   | 598  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2561364 | host | localhost | database_name   | Query   | 575  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2561835 | host | localhost | database_name   | Query   | 549  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2561974 | host | localhost | database_name   | Query   | 538  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2562218 | host | localhost | database_name   | Query   | 524  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2562496 | host | localhost | database_name   | Query   | 508  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2562639 | host | localhost | database_name   | Query   | 499  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2562680 | host | localhost | database_name   | Query   | 496  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563159 | host | localhost | database_name   | Query   | 466  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563209 | host | localhost | database_name   | Query   | 462  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563221 | host | localhost | database_name   | Query   | 461  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563265 | host | localhost | database_name   | Query   | 459  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563409 | host | localhost | database_name   | Query   | 450  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563749 | host | localhost | database_name   | Query   | 428  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2563935 | host | localhost | database_name   | Query   | 417  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2564040 | host | localhost | database_name   | Query   | 410  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2564688 | host | localhost | database_name   | Query   | 366  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2565102 | host | localhost | database_name   | Query   | 339  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2566440 | host | localhost | database_name   | Query   | 260  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2566853 | host | localhost | database_name   | Query   | 235  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2566870 | host | localhost | database_name   | Query   | 234  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2566971 | host | localhost | database_name   | Query   | 228  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2568377 | host | localhost | database_name   | Query   | 161  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2568826 | host | localhost | database_name   | Query   | 141  | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2570177 | host | localhost | database_name   | Query   | 77   | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2570443 | host | localhost | database_name   | Query   | 67   | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2570798 | host | localhost | database_name   | Query   | 50   | Locked | INSERT INTO ibf_post5 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,forum_id,topi |
| 2572364 | root | localhost | database_name   | Query   | 0    | NULL   | show processlist                                                                                     
|
+---------+------+-----------+------------------+---------+------+--------
+--------------------------------------------------------------------------------------
----------------+
100 rows in set (0.80 sec)

How to repeat:
As it is a deadlock, I can't easily show you how.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Please provide repeatable test case. Otherwise we don't have anything to work with and will have to close this bug report.