maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx> writes:

> On Mar 25, 2010, at 8:39 AM, Kristian Nielsen wrote:

>> Yes. This is simple enough to do with DBUG. Just insert code that
>> makes each
>> engine fail in their prepare() when the appropriate DBUG flag is set.
>>
>> Another test we need is to have similar code to crash the server at
>> the same
>> points. Then on server restart check that the other engine does
>> rollback.
>
> It would be great to have these tests. As Arjen says, this code is not
> well traveled.

An example for how to do this (from a random dive into the source tree) is in
mysql-test/suite/maria/t/maria-recovery.test, which uses
mysql-test/include/maria_verify_recovery.inc to crash the server at specific
point and verify that crash recovery works. It shouldn't be hard to do
something similar for this case (also with just commit fail instead of crash).

Hopefully this could be useful if someone wants to implement such test.

>> (For example current code has no protection against another
>> thransaction
>> seeing a transient state with one engine committed and another not,
>> even using
>> START TRANSACTION WITH CONSISTENT SNAPSHOT. And there are
>> fundamentally
>> unsolvable problems with transactions that span both MVCC- and lock- 
>> based
>> engines).
>
> I would be interested in an actual example of something does not work.
> Right now I have a problem imagining why something would not work.

I just happened to run the following test two days ago:

Consider the following tables

    create table t1 (a int primary key) engine=innodb
    create table t2 (b int primary key) engine=pbxt
    insert into t1 values (1)
    insert into t2 values (1)

I run the following statement repeatedly in one thread:

    UPDATE t1,t2 SET a=a+1,b=b+1

It would be natural to assume that other threads will never be able to see
different values for a and b in a single transaction, but that assumption
would be wrong. I run the following statement repeatedly in a different
thread:

    SELECT a,b FROM t1,t2

After just a few iterations, this will return a row with a=b+1.

(The reason I did this test was that I was looking at the XA multi-engine
code, and not seeing any code to enforce cross-engine consistency. I guess
this test shows there just is no such code ...)

I didn't report it as a bug, as I was not sure if it is a bug or not ... maybe
it is? I'd want a better fix than just taking a global lock over every commit
(which could hurt performance a lot), and such fix may be non-trivial...

Here are the Perl long-liners I used to see this, just run them in parallel to
see the failure:

    perl -MDBI -le 'use strict; my $dbh=DBI->connect("dbi:mysql:database=test", "<user>", "<password>", {RaiseError => 1}); $dbh->do("SET binlog_format=row"); $dbh->do($_) for ("drop table if exists t1,t2", "create table t1 (a int primary key) engine=innodb", "create table t2 (b int primary key) engine=pbxt", "begin", "insert into t1 values (1)", "insert into t2 values (1)", "commit"); for (;;) { $dbh->do("UPDATE t1,t2 SET a=a+1,b=b+1");}'

    perl -MDBI -le 'use strict; my $dbh=DBI->connect("dbi:mysql:database=test", "<user>", "<password>", {RaiseError => 1}); $dbh->do("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ"); for(;;) {my $a=$dbh->selectrow_arrayref("SELECT a,b FROM t1,t2"); print join(" ", @$a); die if $a->[0] != $a->[1];}'

(CONSISTENT SNAPSHOT does not make a difference, as is seen by replacing the
second command with this:

    perl -MDBI -le 'use strict; my $dbh=DBI->connect("dbi:mysql:database=test", "<user>", "<password>", {RaiseError => 1}); $dbh->do("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ"); for(;;) {$dbh->do("START TRANSACTION WITH CONSISTENT SNAPSHOT");my $a=$dbh->selectrow_arrayref("SELECT a,b FROM t1,t2"); print join(" ", @$a); die if $a->[0] != $a->[1];$dbh->do("COMMIT");}'

)

With respect to the fundamental problems with combining MVCC and locking
engines; I know I reported a documentation bug for this long ago, though
google failed to find it for me. And I'm not sure how to repeat it now, as I
don't have any non-mvcc engines easily available (MariaDB has no NDB, and BDB
is gone also). But it goes something like this:

In a locking engine, a transaction sees the (consistent) state of the database
as it is at the *end* of the transactions. So the transaction can see all other
transactions that committed before it did.

In an mvcc engine, a transaction sees the (consistent) state of the database
as it is at the *start* of the transaction. So it sees no transactions that
started after it did.

So to get an inconsistency, something like this should work:

TRN1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
TRN1: BEGIN;
TRN1: SELECT * FROM mvcc_table;

                TRN2: BEGIN;
                TRN2: UPDATE mvcc_table SET amount - amount - 100;
                TRN2: UPDATE locking_table SET amount = amount + 100;
                TRN2: COMMIT;

TRN1: SELECT * from locking_table;
TRN1: COMMIT;

In such a case, TRN1 will see the update of the locking_table, but not the
update of the mvcc_table, which gives an inconsistent view of the database. I
don't really see any way to solve this. (Of course one could add LOCK IN
SHARE MODE to every select, in effect turning the mvcc engine into a locking
engine).

 - Kristian.