List:Internals« Previous MessageNext Message »
From:Baron Schwartz Date:October 24 2006 1:09pm
Subject:Re: Statement gets written to binlog after deadlock
View as plain text  
In this particular case, the program would have retried the statement that deadlocked:

INSERT into MyISAM temp table
SELECT from InnoDB table join MyISAM temp table join MyISAM temp table

The tables it's selecting from have millions of rows.  I wonder if that has something 
to do with it.  The statement takes an hour sometimes, and I can see the transaction's 
undo log entries increase then reset to zero over and over.  I wonder if this means 
some entries are being flushed to a log even though the statement hasn't finished. 
(I'm just guessing; I don't really know what it means).  I have always been curious why 
the undo log entries would grow, then start over at zero, then grow again -- if the 
transaction is really being serialized into the binlog, I'd think nothing could be 
written until everything is done, so I'd expect the undo log entries to steadily 
increase until it's done.

However, that's just one case.  I'm pretty sure we have small deadlocks that cause the 
same problem, but I can't find any examples right now.

Rick James wrote:
> What action to you take when the deadlock causes the transaction to fail?
> Maybe there is something subtle going on there. 
> 
>> -----Original Message-----
>> From: Baron Schwartz [mailto:baron@stripped] 
>> Sent: Monday, October 23, 2006 10:29 AM
>> To: internals@stripped
>> Subject: Statement gets written to binlog after deadlock
>>
>> We have been having persistent issues with statements causing 
>> deadlock, yet being 
>> written to the binlog anyway, thus replicated through to the 
>> slave, where of course 
>> there is no deadlock.  The error message is "Query caused 
>> different errors on master 
>> and slave. Error on master: 'Deadlock found when trying to 
>> get lock; try restarting 
>> transaction' (1213), Error on slave: 'no error' (0)"
>>
>> I can only find one bug related to this: 
>> http://bugs.mysql.com/bug.php?id=12495
>>
>> The problem with filing a bug report on it is that it's not 
>> repeatable.  We see it 8 or 
>> ten times a week, then not at all for several weeks.  I don't 
>> think we can possibly 
>> file a usable bug report.  We have lots of deadlocks, but 
>> most of them don't cause the 
>> error.
>>
>> Does anyone have any ideas about how to solve this issue, or 
>> create a good bug report 
>> that won't stagnate?  Should we just add comments to the 
>> existing bug report and hope 
>> that gains it some attention?
>>
>> We're using MySQL's 5.0.21 release.
>>
>> Thanks
>> Baron
>>
>> -- 
>> Baron Schwartz
>> http://www.xaprb.com/
>>
>> -- 
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:    
>> http://lists.mysql.com/internals?unsub=1
>>
>>
> 

-- 
Baron Schwartz
http://www.xaprb.com/
Thread
Statement gets written to binlog after deadlockBaron Schwartz23 Oct
  • RE: Statement gets written to binlog after deadlockRick James24 Oct
    • Re: Statement gets written to binlog after deadlockBaron Schwartz24 Oct
      • RE: Statement gets written to binlog after deadlockRick James24 Oct
      • Re: Statement gets written to binlog after deadlockSergei Golubchik24 Oct