DDC-2906: Atomic operations with nontransactional engines (MyISAM) #3664

Closed
doctrinebot opened this Issue Jan 11, 2014 · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user jack88:

Atomic operations with nontransactional engines (MyISAM)

(INFO: this text is a supplement/clarification to http://www.doctrine-project.org/jira/browse/[DDC-2905](http://www.doctrine-project.org/jira/browse/DDC-2905))

I know, MyISAM tables are nontransactional. That does not mean, that atomic operations are not possible.

(http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html)

The question is how does the ORM system deal with the problem. I think its a database abstraction layer too. That means the ORM can see that the engine MyISAM is nontransactional and it chooses a different strategy to solve this task.

It makes no sense to send commands how „"SET TRANSACTION" to a nontransactional MyISAM engine (it will not work) and Doctrine ORM do that.

(for more details see my example for this behavior:
http://www.doctrine-project.org/jira/browse/[DDC-2905](http://www.doctrine-project.org/jira/browse/DDC-2905))

An example:

(the user- and address-tables are both MyISAM-Tables)

$user = new User();
$user->setName('Mr. Test');

// OK 
$address1 = new Address();
$address1->setCity('Hamburg');
$address1->setZipcode('12345');

// entity can not be saved because zipcode is NULL
$address2 = new Address();
$address2->setCity('New York');

// OK
$address3 = new Address();
$address3->setZipcode('42000');
$address3->setCity('Solingen');

// entity can not be saved because zipcode is NULL
$address4 = new Address();
$address4->setCity('Chicago');

$user->addAddress($address1);
$user->addAddress($address2);
$user->addAddress($address3);
$user->addAddress($address4);

$em->persist($user);

try {
    $em->flush();
} catch(\Exception $e) {
    echo $e->getMessage();
}

Only the user and the first address are stored in the db, but the developer can not see that. An atomic operation is canceled (I think $em->flush() is an implicit atomic operation), we get an exception but the ORM does not go back to the original state. Now we have a system in a inconsistent state and no information about which entities are stored and which are not. Is this an expected behavior of a ORM system or database abstraction layer? I hope not.

I would now to check all entities if they are stored. In this simple example that is no problem, but for large object-graphs is this a big problem.

(sorry for my english - I try my best :-)

@doctrinebot

Comment created by @deeky666:

[~jack88] The problem here is that MySQL differs from other vendors concerning support of transactions. Transactions are supported on a table engine level in MySQL, other vendors do not have different table engines. This is a MySQL specific implementation and therefore we will not add an additional abstraction layer for this. Doctrine abstracts features like transactions and foreign keys on the platform level, not based on specific table options. This does not even make sense, as it is completely non-standard and therefore does not fit into the abstraction layer.
I see the problem you have but I am afraid we cannot support this scenario in Doctrine and I would advise you to stick to InnoDB table engine when working with Doctrine in production. Otherwise it cannot be guaranteed that transactions and foreign key constraints work as expected.

@doctrinebot

Comment created by jack88:

Hello Steve,

thank you for your answer.

I'm a little disappointed. Our company would like to change to Doctrine ORM. We have a Database with over more than 100 MyIsam-Tables. We can not currently change all tables to InnoDB so we can not use Doctrine ORM.

I think:

  • Doctrine ORM supports official MyISAM tables (options={"engine"="MyISAM"}

  • The command flush() is an atomic operation (UnitOfWork),

  • There is only one flush-command for all engines and vendors, so you go normally from the same behavior

  • There is absolutely no notice that the command may behave differently (be carryful when you use it with...)

That means for MySQL-MyISAM ist the abtraction layer broken.

I think it's a bad practice when a system suggests a functionality which can not meet it. Then it is mayby better the support for MyISAM completly to remove (all or nothing - the same behavior for every vendor and every table-engine).

I do not understand why Doctrine ORM can not check if all requirements are ok and why it is a problem to implement a transaction strategy for nontransactional engines. I think it is not difficult to implement.

it would be nice if Doctrine-Team could think about this problem again

@doctrinebot

Comment created by @beberlei:

There is indeed a documentation notice missing on this issue and I am sorry that you have assumed this works. But the options={"engine": "MyISAM"} is not documented as well, as well is there no official statement that this is supported.

The link you provided about Ansi Transactions does not show how easy it is with MyISAM. They say you can LOCK TABLES and then write your SQL in specific ways to get it work for examples operating on one table.

You can achieve the same with Doctrine by calling the flush operation passing only one entity at a time. This will only change this one entity (and execute scheduled inserts+deletes, so its not perfect).

Supporting transactions like InnoDB and other vendors have them in code is impossible.

@doctrinebot

Comment created by @beberlei:

I added MyISAM as known limitation to the documentation:

12556e2

@doctrinebot

Issue was closed with resolution "Invalid"

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment