Doctrine 1
  1. Doctrine 1
  2. DC-541

MSSQL Server: Enabling MARS prevents the saving of records.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: Connection, Record
    • Labels:
      None
    • Environment:
      PHP 5.3.1
      MSSQL Server 2008
      Windows
      PHP PDO ODBC

      Description

      This is related to issue #540. In order to circumvent that issue, I enabled MARS (Multiple Active Result Sets) - now when attempting to save any record the following error results:

      Fatal error: Uncaught exception 'Doctrine_Transaction_Exception' with message 'Rollback failed. There is no active transaction.' in C:\www\doctrine\lib\Doctrine\Transaction.php on line 319

      This is actually caused by the following MSSQL error:

      exception 'Doctrine_Transaction_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 3997 [Microsoft][SQL Server Native Client 10.0][SQL Server]A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. (SQLExecDirect[3997] at ext\pdo_odbc\odbc_driver.c:247)' in C:\www\doctrine\lib\Doctrine\Transaction.php:212 Stack trace: #0 C:\www\doctrine\lib\Doctrine\Transaction.php(511): Doctrine_Transaction->beginTransaction(NULL) #1 C:\www\doctrine\lib\Doctrine\Connection.php(1377): Doctrine_Transaction->beginInternalTransaction(NULL) #2 C:\www\doctrine\lib\Doctrine\Connection\UnitOfWork.php(64): Doctrine_Connection->beginInternalTransaction() #3 C:\www\doctrine\lib\Doctrine\Record.php(1691): Doctrine_Connection_UnitOfWork->saveGraph(Object(Model)) #4 C:\www\doctrine\tools\sandbox\index.php(83): Doctrine_Record->save() #5

      {main}

      I haven't worked out what's going on yet, but this blog post may shed some light on it:
      http://blogs.msdn.com/cbiyikoglu/archive/2006/11/21/mars-transactions-and-sql-error-3997-3988-or-3983.aspx

      I've attached a script to reproduce this issue in the sandbox (MARS is enabled in the DSN).

      1. index.php
        1.0 kB
        Craig Marvelley
      2. mssql_transaction.patch
        2 kB
        Craig Marvelley

        Activity

        Hide
        Jonathan H. Wage added a comment -

        I am still not sure of the exact problem/error that is happening. When isn't happening with the current code? or is something happening that should not?

        Show
        Jonathan H. Wage added a comment - I am still not sure of the exact problem/error that is happening. When isn't happening with the current code? or is something happening that should not?
        Hide
        Craig Marvelley added a comment -

        As far as I understand it (I'm no SQL Server expert, unfortunately!) the issue is this:

        The Doctrine_Transaction_Mssql class is used for both the MSSQL and ODBC PDO extensions. It manages transactions by executing manual queries against the database (ROLLBACK TRANSACTION, COMMIT_TRANSACTION, etc.).

        This isn't compatible with the ODBC PDO extension and SQL Server when Multiple Active Record Sets is enabled; I believe MARS allows multiple transactions to co-exist, and this query-style way of managing them isn't compatible with that as transactions aren't 'namespaced'. MARS is critical because without it there's no way of looping over more than one record set at the same time, something we take for granted in MySQL et al.

        From that link to the Symfony wiki I posted earlier, it appears that the query method of managing transactions is only necessary for the MSSQL PDO extension - quote:

        "dblib doesn't support transactions so we need to add a workaround for transactions, last insert ID, and quoting"

        This is backed up by the patch - when we let the ODBC driver handle transactions natively, everything works fine.

        Does that make sense?

        Show
        Craig Marvelley added a comment - As far as I understand it (I'm no SQL Server expert, unfortunately!) the issue is this: The Doctrine_Transaction_Mssql class is used for both the MSSQL and ODBC PDO extensions. It manages transactions by executing manual queries against the database (ROLLBACK TRANSACTION, COMMIT_TRANSACTION, etc.). This isn't compatible with the ODBC PDO extension and SQL Server when Multiple Active Record Sets is enabled; I believe MARS allows multiple transactions to co-exist, and this query-style way of managing them isn't compatible with that as transactions aren't 'namespaced'. MARS is critical because without it there's no way of looping over more than one record set at the same time, something we take for granted in MySQL et al. From that link to the Symfony wiki I posted earlier, it appears that the query method of managing transactions is only necessary for the MSSQL PDO extension - quote: "dblib doesn't support transactions so we need to add a workaround for transactions, last insert ID, and quoting" This is backed up by the patch - when we let the ODBC driver handle transactions natively, everything works fine. Does that make sense?
        Hide
        Jonathan H. Wage added a comment -

        Now that I look at everything again, do we need to even be overriding those methods at all in Doctrine_Transaction_Mssql (_doRollback, _doCommit, _doBeginTransaction). We should just let the database driver handle it?

        Show
        Jonathan H. Wage added a comment - Now that I look at everything again, do we need to even be overriding those methods at all in Doctrine_Transaction_Mssql (_doRollback, _doCommit, _doBeginTransaction). We should just let the database driver handle it?
        Hide
        Jonathan H. Wage added a comment -
        
        Index: lib/Doctrine/Transaction/Mssql.php
        ===================================================================
        --- lib/Doctrine/Transaction/Mssql.php	(revision 7673)
        +++ lib/Doctrine/Transaction/Mssql.php	(working copy)
        @@ -65,28 +65,4 @@
         
                 $this->conn->execute($query);
             }
        -    
        -    /**
        -     * Performs the rollback.
        -     */
        -    protected function _doRollback()
        -    {
        -        $this->conn->getDbh()->exec('ROLLBACK TRANSACTION');
        -    }
        -    
        -    /**
        -     * Performs the commit.
        -     */
        -    protected function _doCommit()
        -    {
        -        $this->conn->getDbh()->exec('COMMIT TRANSACTION');
        -    }
        -    
        -    /**
        -     * Begins a database transaction.
        -     */
        -    protected function _doBeginTransaction()
        -    {
        -        $this->conn->getDbh()->exec('BEGIN TRANSACTION');
        -    }
         }
        

        It seems like we don't even need to do that?

        Show
        Jonathan H. Wage added a comment - Index: lib/Doctrine/Transaction/Mssql.php =================================================================== --- lib/Doctrine/Transaction/Mssql.php (revision 7673) +++ lib/Doctrine/Transaction/Mssql.php (working copy) @@ -65,28 +65,4 @@ $ this ->conn->execute($query); } - - /** - * Performs the rollback. - */ - protected function _doRollback() - { - $ this ->conn->getDbh()->exec('ROLLBACK TRANSACTION'); - } - - /** - * Performs the commit. - */ - protected function _doCommit() - { - $ this ->conn->getDbh()->exec('COMMIT TRANSACTION'); - } - - /** - * Begins a database transaction. - */ - protected function _doBeginTransaction() - { - $ this ->conn->getDbh()->exec('BEGIN TRANSACTION'); - } } It seems like we don't even need to do that?
        Hide
        Craig Marvelley added a comment -

        For ODBC, we definitely don't. That link suggests the above approach caters to the MSSQL extension. I've not tried that one because I'm on Windows and as you probably know it's not recommended; I imagine I'd have way too many other problems were I to try! I didn't want to break anything for anyone using the MSSQL driver though, which led to my awkward patch.

        From my point of view, I'd imagine the majority of people using SQL Server will be using a Windows stack, and therefore ODBC (and possibly the new SQL Server Driver for PHP PDO extension, which definitely won't need manual queries, in future) - so I'd favour an approach that didn't compromise those drivers.

        As an aside - are there any plans afoot to support the new Microsoft endorsed driver? I imagine if you guys were to do so it would be for Doctrine 2, but do you know of any community effort to get it working in 1.2?

        Show
        Craig Marvelley added a comment - For ODBC, we definitely don't. That link suggests the above approach caters to the MSSQL extension. I've not tried that one because I'm on Windows and as you probably know it's not recommended; I imagine I'd have way too many other problems were I to try! I didn't want to break anything for anyone using the MSSQL driver though, which led to my awkward patch. From my point of view, I'd imagine the majority of people using SQL Server will be using a Windows stack, and therefore ODBC (and possibly the new SQL Server Driver for PHP PDO extension, which definitely won't need manual queries, in future) - so I'd favour an approach that didn't compromise those drivers. As an aside - are there any plans afoot to support the new Microsoft endorsed driver? I imagine if you guys were to do so it would be for Doctrine 2, but do you know of any community effort to get it working in 1.2?

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Craig Marvelley
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: