none
ASP.NET SQL transaction disable auto-commit by C++ code RRS feed

  • Question

  • Hi every body,

    I am facing a problem of auto committing without call COMMIT.

    My problem occurs when I break the program when it reach the break point at the position after executing a query under transaction and before call COMMIT. I find the data row already stored in the database table. It happens only with ado.net but it does not if I use OLEDB or ODBC native driver.


    String^ s = gcnew String(SQL);
    try__
    {
                           SqlTransaction= m_cn->BeginTransaction(IsolationLevel::Serializable);
    SqlCommand^ cmdSQL = gcnew SqlCommand(s ,m_cn, m_SqlTransaction);
    cmdSQL->ExecuteNonQuery();

                           ....... MY Break point is here -------- it stores in the table before reach commit

                           m_SqlTransaction->Commit();

    }
        catch(SqlException^ ex)
    {
    _ReadDotNetError( ex, (__oBase*) this );
    hr = __tS_FALSE;
    }
    return hr;

    Thanks in advance


    Wednesday, July 17, 2019 8:01 PM

Answers

  • Hi,

    Thank you for posting here.

    >>It happens only with ado.net but it does not if I use OLEDB or ODBC native driver.

    In auto-commit mode, every database operation is a transaction that is committed when performed. This mode is suitable for many real-world transactions that consist of a single SQL statement. It is unnecessary to delimit or specify completion of these transactions. In databases without transaction support, auto-commit mode is the only supported mode. In such databases, statements are committed when they are executed and there is no way to roll them back; they are therefore always in auto-commit mode.

    Open the database connection before calling the BeginTransaction() method. Use BeginTrans and CommitTrans methods with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit.

    Not all providers support transactions. Verify that the provider-defined property "Transaction DDL" appears in the Connection object's Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

    I suggest you could refer to the links:
    https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-ado?view=sql-server-2017
    https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/connection-object-ado?view=sql-server-2017

    Best Regards,

    Jeanine Zhang

    • Marked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    Thursday, July 18, 2019 3:30 AM
    Moderator
  • If your queries, which are used to check the data, include ‘WITH (NOLOCK)’, ‘WITH (READUNCOMMITTED)’ or ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED’, then it is normal to see uncommitted data.

    Do you have more details or a real example that demonstrates the problem?

    • Marked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    Thursday, July 18, 2019 5:38 AM

All replies


  •                        ....... MY Break point is here -------- it stores in the table before reach 

    I mean by that line when I stop debugging by (shift-F5) in this line the transaction is committed before reaching the function "Commit"

    • Marked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    • Unmarked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    Thursday, July 18, 2019 3:23 AM
  • Hi,

    Thank you for posting here.

    >>It happens only with ado.net but it does not if I use OLEDB or ODBC native driver.

    In auto-commit mode, every database operation is a transaction that is committed when performed. This mode is suitable for many real-world transactions that consist of a single SQL statement. It is unnecessary to delimit or specify completion of these transactions. In databases without transaction support, auto-commit mode is the only supported mode. In such databases, statements are committed when they are executed and there is no way to roll them back; they are therefore always in auto-commit mode.

    Open the database connection before calling the BeginTransaction() method. Use BeginTrans and CommitTrans methods with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit.

    Not all providers support transactions. Verify that the provider-defined property "Transaction DDL" appears in the Connection object's Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

    I suggest you could refer to the links:
    https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-ado?view=sql-server-2017
    https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/connection-object-ado?view=sql-server-2017

    Best Regards,

    Jeanine Zhang

    • Marked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    Thursday, July 18, 2019 3:30 AM
    Moderator
  • If your queries, which are used to check the data, include ‘WITH (NOLOCK)’, ‘WITH (READUNCOMMITTED)’ or ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED’, then it is normal to see uncommitted data.

    Do you have more details or a real example that demonstrates the problem?

    • Marked as answer by Tamer Ismael Friday, July 19, 2019 6:33 AM
    Thursday, July 18, 2019 5:38 AM