List:Internals« Previous MessageNext Message »
From:Alfranio Correia Date:May 13 2009 2:10am
Subject:Feedback needed for WL#2687
View as plain text  
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).

ACTIONS:

1 - Feedback
2 - Review of the patch (Jasonh, Luis).
Mats and Andrei, could you both also take a look?

cheers.

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.



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. Unfortunately, we cannot ensure that mixing transactional and
non-transactional tables will be handled correctly and as such we should
properly document this and discourage such modes under this circumstance.
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 or MIXED 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.

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. We do not print out any warning
message or throw an error either. This is done to discourage the use of such
modes when mixing transactional and non-transactional tables.

In RBR mode, non-transactional changes are logged to the binary log upon
committing or rolling back a statement. On the other hand, transactional
changes
are logged upon committing any implicit (i.e autocommit = 1) or explicit
(autocommit = 0/COMMIT or START/COMMIT) transaction and ignored upon
rolling it
back. To understand the details of this approach, 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).

We have what follows:

1) T...T: all the statements are transactional.

In this case, the changes are copied to the binary log upon commit and
ignored
upon rollback.

2) N...N: all the statements are non-transactional.

In this case, the changes are logged to the binary log as soon as each
statement
is completed.

3) N T...: the beginning part is non-transactional, followed by
transactional
statements.
4) T N...: the beginning part is transactional, followed by
non-transactional
statements.

In both cases, same as above.

5) M...: where M is either N T or T N.



IMPROVEMENTS
------------
In STMT and MIXED modes, if we have the 1), 2) or 3) cases described
above, the
replication is completely safe. Otherwise, there may be inconsistencies
among
master and slaves.

TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 1
1.1 - Should we distinguish between safe and unsafe cases?
1.2 - Should we either print out warning messages or throw an error for the
unsafe cases?


TESTS
-----
The test case should check if transactions that mixes transactional and
non-transactional tables are correctly handled.

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.

In RBR, changes on non-transactional tables must be directly logged into the
binary log while changes on transactional tables must be put into a
cache and
moved into the binary log upon commit. The changes in the code has
impact not
only on simple transactions but on how concurrent statements are handled,
creation of tables through select, updates on multiple-tables, functions and
triggers.

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?
2.2 What should we test for events?


IMPLEMENTATION DETAILS
----------------------

There are two different sets of pending events. One for
non-transactional events
which are flushed to the binary log upon committing or rolling back a
statement;
The other one for transactional events which are flushed to the binary
log upon
committing a transaction and ignored upon rolling back it.

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