Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2906

Atomic operations with nontransactional engines (MyISAM)

    Details

      Description

      Atomic operations with nontransactional engines (MyISAM)

      (INFO: this text is a supplement/clarification to 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)

      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

        Activity

        Hide
        Steve Müller added a comment -

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

        Show
        Steve Müller added a comment - Jacek Hensoldt 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.
        Hide
        Jacek Hensoldt added a comment - - edited

        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

        Show
        Jacek Hensoldt added a comment - - edited 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
        Hide
        Benjamin Eberlei added a comment -

        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.

        Show
        Benjamin Eberlei added a comment - 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.
        Hide
        Benjamin Eberlei added a comment -

        I added MyISAM as known limitation to the documentation:

        https://github.com/doctrine/doctrine2/commit/12556e2dfeea293c65fb24000622b6327dd27e17

        Show
        Benjamin Eberlei added a comment - I added MyISAM as known limitation to the documentation: https://github.com/doctrine/doctrine2/commit/12556e2dfeea293c65fb24000622b6327dd27e17

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Jacek Hensoldt
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: