Sven Sandberg wrote:
> alfranio correia wrote:
>
>> Sven Sandberg wrote:
>>
>>> alfranio correia wrote:
>>>
>>>
>>>> After the thorough analysis made by Sven I have little to say.
>>>> Note that using only sleep(s) to produce the desired behavior is
>>>> not reliable. To increase the chances that everything will execute as
>>>> desired
>>>> I suggest to do what follows:
>>>>
>>>> 1 master: BEGIN
>>>> 2 insert 10 rows into t3
>>>> 3 INSERT INTO t2
>>>> |
>>>> save_master_pos
>>>>
>>>> | 4 INSERT INTO t1
>>>> 5 COMMIT
>>>> 6 slave: BEGIN
>>>> 7 insert 100 rows into t4
>>>> 8 SELECT * FROM t1 FOR UPDATE
>>>> 9 START SLAVE
>>>>
>>>> | sync_slave_with_master|
>>>>
>>>>
>>> Unfortunately, this does not work with the current implementation of
>>> sync_slave_with_master. The saved master position is in the middle of a
>>> transaction. sync_slave_with_master waits until the Exec_Master_Log_Pos
>>> returned by SHOW SLAVE STATUS reaches the saved position. However,
>>> Exec_Master_Log_Pos is only updated at transaction boundaries. So it
>>> will not reach the saved position until the master transaction commits.
>>>
>>> /Sven
>>>
>>>
>> Sven, the idea is not to wait for the uncommitted transaction
>> but to wait until the slave applies the committed transactions thus
>> reducing the probability of the sleep value being not enough.
>>
>
> Right, that would work. But I also think we could add a
> non-transactional update to the transaction (INSERT INTO myisam table),
> just before the statement causing deadlock. Then the slave can wait
> until it sees the row in the myisam table. So it will be synced to
> within the transaction, just before the statement that deadlocks (but
> will not be guaranteed to deadlock: we have to use sleeps for that).
>
> /Sven
>
Both approaches work and both need the sleep.
/Alfranio
>
>> Alfranio.
>>
>>
>>>
>>>
>>>> 10 sleep 2
>>>> 11 SELECT * FROM t2 FOR UPDATE
>>>> 12 COMMIT
>>>>
>>>>
>>>> master: BEGIN
>>>> INSERT INTO t2
>>>>
>>>> |save_master_pos
>>>>
>>>> | COMMIT
>>>> slave: BEGIN
>>>> SELECT * FROM t2 FOR UPDATE
>>>> start slave;
>>>> |
>>>> sync_slave_with_master
>>>>
>>>> | sleep 10
>>>> COMMIT
>>>>
>>>> /alfranio
>>>>
>>>>
>>>> Sven Sandberg wrote:
>>>>
>>>>
>>>>> Hi,
>>>>>
>>>>> OK, I think I managed to grasp what this test tries to do and why it
>>>>> fails. See comment on the bug report.
>>>>>
>>>>> The patch tries to fix problem (4) of my comment. Please also fix
>>>>> problems (1)-(3) and (5).
>>>>>
>>>>> I think the patch only works partially. It will wait until the IO
> thread
>>>>> has copied everything from master, so that is good. However, it will
> not
>>>>> synchronize the SQL thread. The problem is that Exec_Master_Log_Pos
> is
>>>>> not updated inside a transaction. Exec_Master_Log_Pos is only
> updated
>>>>> after commit.
>>>>>
>>>>> I think you could do the following:
>>>>>
>>>>> (1) synchronize the IO thread (as in your patch)
>>>>>
>>>>> (2) add a non-transactional update to another table (t5) in the
> master
>>>>> transaction, just before it does 'insert into t2'. On slave, you can
>>>>> sync by waiting until you see the row in t5. That will only sync up
> to
>>>>> the statement before the deadlock. I don't think it's possible to
> sync
>>>>> up to the actual deadlock in any easy way. The best I can think of is
> to
>>>>> sleep 5 seconds after that.
>>>>>
>>>>> /Sven
>>>>>
>>>>> Serge Kozlov wrote:
>>>>>
>>>>>
>>>>>
>>>>>> #At bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-sea
>>>>>>
>>>>>> 2697 Serge Kozlov 2008-09-15
>>>>>> Bug#37716. Remove real_sleep and and waiting proper values
> of Read_Master_Log_Pos and
>>>>>> Exec_Master_Log_Pos on slave.
>>>>>> modified:
>>>>>> mysql-test/extra/rpl_tests/rpl_deadlock.test
>>>>>>
>>>>>> === modified file 'mysql-test/extra/rpl_tests/rpl_deadlock.test'
>>>>>> --- a/mysql-test/extra/rpl_tests/rpl_deadlock.test 2008-02-03
> 09:00:49 +0000
>>>>>> +++ b/mysql-test/extra/rpl_tests/rpl_deadlock.test 2008-09-15
> 07:46:42 +0000
>>>>>> @@ -44,6 +44,7 @@ enable_query_log;
>>>>>> insert into t1 values(1);
>>>>>> commit;
>>>>>> save_master_pos;
>>>>>> +let $master_pos= query_get_value(show master status, Position,
> 1);
>>>>>>
>>>>>> connection slave;
>>>>>> begin;
>>>>>> @@ -59,12 +60,15 @@ enable_query_log;
>>>>>> select * from t1 for update; # t1,t2 on local slave's
>>>>>> start slave;
>>>>>>
>>>>>> -# bad option, todo: replicate a non-transactional t_sync with
> the transaction
>>>>>> -# and use wait_until_rows_count macro below
>>>>>> ---real_sleep 3 # hope that slave is blocked now
>>>>>> -#let $count=11;
>>>>>> -#let $table=t_sync;
>>>>>> -#--include wait_until_rows_count.inc
>>>>>> +# wait while slave read all events from master
>>>>>> +let $slave_param= Read_Master_Log_Pos;
>>>>>> +let $slave_param_value= $master_pos;
>>>>>> +--source include/wait_for_slave_param.inc
>>>>>> +
>>>>>> +# wait while slave executed up to start of transaction
>>>>>> +let $slave_param= Exec_Master_Log_Pos;
>>>>>> +let $slave_param_value= 549;
>>>>>> +--source include/wait_for_slave_param.inc
>>>>>>
>>>>>> select * from t2 for update /* dl */; # provoke deadlock,
> repl-ed should be victim
>>>>>> commit;
>>>>>>
>
>
>
