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|
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;
>>
>>
>>
>
>
>
