List:Internals« Previous MessageNext Message »
From:He Zhenxing Date:May 18 2009 3:45am
Subject:Re: Feedback needed for WL#2687
View as plain text  
Alfranio Correia wrote:
> Hi all,
> 
> updates:
> 
> Theres is a final patch, at least from my point of view :), available at
> http://lists.mysql.com/commits/74255
> 
> BACKGROUND
> ----------
> Mixing transactional and non-transactional tables is not supported by the
> current code. In a nutshell, non-transactional operations are logged upon
> commit or rollback thus generating invalid sequence of operations.
> For instance, assume the following statements and sessions (connections):
> 
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
> 
> con1: START TRANSACTION;
> con2: START TRANSACTION;
> con1: UPDATE test SET a = 10;
> con2: DELETE FROM test;
> con2: COMMIT;
> con1: COMMIT;
> 
> In RBR mode, the binary log produced would look like this:
> 
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
> 
> con2: START TRANSACTION;
> con2: DELETE FROM test WHERE a = 10;
> con2: COMMIT;
> con1: START TRANSACTION;
> con1: UPDATE test SET a = 10 WHERE a = 1;
> con1: COMMIT;
> 
> Clearly, this would generate an error in the slave as the row (a = 10) to be
> deleted would not be found.
> 
> 
> 
> SOLUTION
> --------
> To correctly handle the mix of transactional and non-transactional
> tables, we
> proceed as follows.
> 
> In STMT mode, statements are logged to the binary log upon committing or
> rolling back a transaction thus preserving any semantic among the
> statements.
> Any statement that modifies a non-transactional table in the boundaries of a
> transaction is classified as unsafe. This includes the case where a single
> statement in auto-commit mode updates non-transactional and transactional
> tables.
> 
> The term unsafe means that we cannot ensure that mixing transactional and
> non-transactional tables will be handled correctly and as such a warning
> message
> is printed out. Find below an example of what may go wrong:
> 
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
> 
> con1: START TRANSACTION;
> con2: START TRANSACTION;
> con1: UPDATE test SET a = 10 where a = 1;
> con2: DELETE FROM test where a = 10;
> con2: COMMIT;
> con1: COMMIT;
> 
> In STMT mode, the binary log produced would look like this:
> 
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
> 
> con2: START TRANSACTION;
> con2: DELETE FROM test WHERE a = 10;
> con2: COMMIT;
> Clearly, the master and the slave would be out of sync.
> 
> In RBR mode, changes to non-transactional tables are logged to the
> binary log
> upon committing or rolling back a statement. On the other hand,
> transactional
> changes are logged upon committing a transaction and ignored upon rolling a
> transaction back. This mode is completely safe and as such no warning
> message
> is printed out.
> 
> The MIXED mode is equivalent to the STMT mode when there is no unsafe
> statement.
> Any unsafe statement, however, has its changes logged as in the RBR mode.
> 
> To understand the details, let's use T to represent a statement that only
> changes transactional tables, N to represent a statement that only changes
> non-transactional tables, and M to represent a statement that changes
> both (i.e.
> multi-table update). Then consider the following cases:
> 
> 1) B T...T C/R: all the statements are transactional.
> 
> In STMT, ROW and MIXED modes, statements or changes are logged to the
> binary log
> upon committing a transaction and ignored upon rolling a transaction back.
> 
> 2) B N...N C/R: all the statements are non-transactional.
> 
> In STMT, the statements are logged to the binary log upon committing or
> rolling
> back a transaction and a warning message is printed out for each statement.

No warnings should be printed in this case, a transaction with only T or
N statements is safe to binlog.

> In ROW and MIXED mode, changes are logged to the binary log as soon as each
> statement is completed.
> 
> 3) B N T... C/R: the beginning part is non-transactional, followed by
> transactional
> statements.
> 
> Combination of 1 and 2.

I don't think this is a simple combination of case 1) and 2), Please
read my previous comments on this issue.

> 
> 4) B T N... C/R: the beginning part is transactional, followed by
> non-transactional
> Combination of 1 and 2.

Here too.

> 
> 5) M...: where M is either N T or T N.
> 
> This case is handled as 3 or 4.
> 

Please note that M is not a sequence of N T or T N, M is a **SINGLE**
statement that modifies both transactional and non-transactional tables.
But of cause, when logging in ROW format, it will become a sequence of N
T or T N rows, but beware that it is different in STATEMENT format, we
cannot split it into NT or TN.

> 
> 
> TEST CASE
> ---------
> The test case should check if transactions that mix transactional and
> non-transactional tables are correctly handled. Thus to check the behavior
> provided by the current code we divided the test as follows:
> 
> 1 - CREATING TABLES through SELECT * FROM
> 2 - MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
>      1.1 - NN TT
>      1.2 - TT NN
>      1.3 - TT NN TT
> 3 - CONCURRENCY:
>      2.1 - NON-TRANSACT TABLES -  SET AUTOCOMMIT = 0  | COMMIT
>      2.2 - NON-TRANSACT TABLES -  SET AUTOCOMMIT = 1  | START - COMMIT
> 4 - UPDATING MULTIPLE TABLES
> 5 - PROCEDURES, FUNCTIONS AND TRIGGERS
> 
> 
> 
> IMPLEMENTATION DETAILS
> ----------------------
> 
> There are two caches: one for transactional changes (trx-cache) and
> another one
> for non-transactional changes (nontrx-cache). The trx-cache is flushed and
> truncated before committing or rolling back a transaction. The
> nontrx-cache is
> flushed and truncated before finishing a statement. For the 5 cases
> described
> above, these caches are used as follows:

I think the nontrx-cache is better be named stmt-cache, since it is
flushed after each statement.

> 
> 1) B T...T C/R: all the statements are transactional.
> 
> In STMT, ROW and MIXED modes, statements or changes are copied to the
> trx-cache
> and flushed to the binary log upon committing a transaction or just ignored
> upon rolling a transaction back.
> 
> 2) B N...N C/R: all the statements are non-transactional.
> 
> In STMT, the statements are copied to the trx-cache and flushed to the
> binary

I think we should flush the statements to binlog after each statement to
be compatible with the old behavior and the behavior of ROW or MIXED
mode you stated below.

> log upon committing or rolling back a transaction. In ROW and MIXED mode,
> changes are copied to the nontrx-cache and flushed to the binary log as
> soon as
> each statement is completed.
> 
> 3) B N T... C/R: the beginning part is non-transactional, followed by
> transactional
> statements.
> 
> Combination of 1 and 2.

See above comments.

> 
> 4) B T N... C/R: the beginning part is transactional, followed by
> non-transactional
> statements.
> 
> Combination of 1 and 2.
> 

See above comments.

> 5) M...: where M is either N T or T N.
> 
> This case is handled as 3 or 4.
> 

See above comments.

> 
> 
> 
> Mats Kindahl wrote:
> > Alfranio Correia wrote:
> >   
> >> Mats Kindahl wrote:
> >>     
> >>> Alfranio Correia wrote:
> >>>   
> >>>       
> >>>> Hi all,
> >>>>
> >>>> Find below a proposal on how to handle WL#2687.
> >>>> There is also a draft of a patch available in a related bug
> (BUG#40278).
> >>>>     
> >>>>         
> >>> There is also BUG#28976 to consider when implementing WL#2687.
> >>>   
> >>>       
> >> ok. Which bug should I reference when committing a patch?
> >> The fix will be same anyway.
> >>     
> >
> > I suggest that you reference the worklog and then close the bugs manually.
> There
> > might be other bugs, that we do not know about right now, that are fixed by
> this
> > as well.
> >
> > [snip]
> >
> >   
> >>>> PROPOSAL
> >>>> --------
> >>>> To correctly handle the mix of transactional and non-transactional
> >>>> tables, we
> >>>> propose what follows.
> >>>>
> >>>> In STMT and MIXED modes, statements are copied to the binary log
> upon
> >>>> commit or rollback thus preserving any semantic among the statements
> in a
> >>>> transaction.
> >>>>     
> >>>>         
> >>> You need to clarify this and point out that the non-transactional
> statements are
> >>> *not* written out of order but will appear in the same order in the
> transaction
> >>> as they are written.
> >>>   
> >>>       
> >> ok.
> >>
> >>     
> >>> However, I am not sure why this need to apply to mixed mode. We can
> easily
> >>> switch to row-based, and write the T(s) part to the transaction cache
> and the
> >>> N(s) part directly to the binary log.
> >>>   
> >>>       
> >> So let's do it. I will change the implementation to do so.
> >>
> >>     
> >>>   
> >>>       
> >>>> Unfortunately, we cannot ensure that mixing transactional and
> >>>> non-transactional tables will be handled correctly
> >>>>     
> >>>>         
> >>> ... in statement-based replication...
> >>>
> >>>   
> >>>       
> >> This is related to statement based if we switch to rows in mixed mode.
> >>     
> >
> > Right.
> >
> >   
> >>>> and as such we should
> >>>> properly document this and discourage such modes under this
> circumstance.
> >>>>     
> >>>>         
> >>> Clarify "this circumstance." Do you mean when using statement-based
> replication?
> >>>   
> >>>       
> >> ok. This is related to statement based if we switch to rows in mixed mode.
> >>     
> >
> > OK. Agree.
> >
> > [snip]
> >
> >   
> >>>> Note that we are not proposing to distinguish the case that
> >>>> non-transactional changes are processed before any transactional
> change, what could be safely
> >>>> handled with minor changes to our proposal.
> >>>>     
> >>>>         
> >>> I don't understand this sentence.
> >>>   
> >>>       
> >> I will rewrite this but I meant to say that we will not distinguish the
> >> cases presented below:
> >>
> >> start;
> >> N
> >> T
> >> commit;
> >>
> >> and
> >>
> >> start;
> >> T
> >> N
> >> commit;
> >>
> >> In other words, we will not implement any sort of optimization based on
> >> the position of the
> >> non-transactional changes.
> >>     
> >
> > OK.
> >
> > [snip]
> >
> >   
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 1
> >>>> 1.1 - Should we distinguish between safe and unsafe cases?
> >>>>     
> >>>>         
> >>> Do you mean unsafe as in "statements containing, e.g.,
> non-deterministic
> >>> changes" or in any other sense. You were using "safe" in the preceding
> sentence,
> >>> so I am not sure which one you are referring to.
> >>>   
> >>>       
> >> I am using safe and unsafe here to denote the mix of non-transactional
> >> and transactional tables in STMT mode.
> >>     
> >
> > OK.
> >
> >   
> >>>> 1.2 - Should we either print out warning messages or throw an error
> for the
> >>>> unsafe cases?
> >>>>     
> >>>>         
> >>> Yes?
> >>>   
> >>>       
> >> If you are switching to rows in MIXED mode when mixing non-transactional
> >> and transactional tables, I think we should print warning messages in STMT
> >> mode.
> >>     
> >
> > OK. Well... strictly speaking, they are not unsafe any more when switching to
> > row format.
> >
> > [snip]
> >
> >   
> >>>> Thus to check the behavior provided by the current code we divided
> >>>> the test as follows:
> >>>>
> >>>> 1 - MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
> >>>>      1.1 - NN TT
> >>>>      1.2 - TT NN
> >>>>      1.3 - TT NN TT
> >>>> 2 - CONCURRENCY:
> >>>>      2.1 - NON-TRANSACT TABLES -  SET AUTOCOMMIT = 0  | COMMIT
> >>>>      2.1 - NON-TRANSACT TABLES -  SET AUTOCOMMIT = 1
> >>>>      2.2 - NON-TRANSACT TABLES -  SET AUTOCOMMIT = 1  | START -
> COMMIT
> >>>> 3 - CREATING TABLES through SELECT * FROM
> >>>> 4 - UPDATING MULTIPLE TABLES
> >>>> 5 - PROCEDURES, FUNCTIONS AND TRIGGERS
> >>>> 6 - EVENTS
> >>>>
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 2
> >>>> 2.1 Are the tests proposed for procedures, functions and triggers
> enough?
> >>>>     
> >>>>         
> >>> You need to ensure that you have different nesting levels for stored
> routines:
> >>> the behavior is different depending on the nesting level (not for RBR,
> but for SBR).
> >>>   
> >>>       
> >> How many levels?
> >> What I have is "statement --> proc --> proc". Three levels?
> >> Is this enough?
> >>     
> >
> > Yes, I think that should be enough. AIUI, stored routines called from stored
> > routines are separate, but then it's all the same.
> >
> > [snip]
> >
> >   
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 3
> >>>> 3.1 Do we really need a cache for non-transactional events? IMHO, a
> cache to
> >>>> address the only the current worklog is overkilling.
> >>>>     
> >>>>         
> >>> I don't think so, and here is why:
> >>>
> >>> Suppose that we start a statement "s" that contain transactional ( T(s)
> ) and
> >>> non-transactional ( N(s) ) changes so that the non-transactional changes
> span
> >>> more than one event. If we flush the N(s) part immediately, the binary
> log will
> >>> look something like this:
> >>>
> >>> Table-map  n1
> >>> Write-rows N(s)  STMT_END_F
> >>> Table-map  n1
> >>> Write-rows N(s)  STMT_END_F
> >>> BEGIN;
> >>> Table-map  t2
> >>> Write-rows T(s)
> >>> Write-rows T(s)
> >>> COMMIT;
> >>>
> >>> When we execute these statements on the master, any concurrent
> operations on n1
> >>> will be blocked, waiting for the statement to complete. However, on the
> slave,
> >>> concurrent operation on n1 will block until the first part has been
> executed,
> >>> then the lock will be released, and it will be allowed to execute. In
> that
> >>> sense, the changes to n1 will not be atomically executed, which can have
> all
> >>> sorts of consequences.
> >>>   
> >>>       
> >> Hummm ok !!! I thought this was not a problem as you are
> >> changing a non-transactional table.
> >>
> >> But I understood your point and agree with you.
> >> We need a cache. :)
> >>     
> >
> > OK. Good. :)
> >
> > Best wishes,
> > Mats Kindahl
> >   
> 
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication?unsub=1

Thread
Feedback needed for WL#2687Alfranio Correia13 May
  • Re: Feedback needed for WL#2687Mats Kindahl13 May
    • Re: Feedback needed for WL#2687Alfranio Correia13 May
  • Re: Feedback needed for WL#2687He Zhenxing14 May
    • Re: Feedback needed for WL#2687He Zhenxing14 May
    • Re: Feedback needed for WL#2687Alfranio Correia15 May
      • Re: Feedback needed for WL#2687He Zhenxing18 May
  • re: Feedback needed for WL#2687Michael Widenius14 May