SQL Server Performance

Rollback Log

Discussion in 'General Developer Questions' started by vaanee, May 8, 2003.

  1. vaanee New Member

    We are working on handheld applications and use the following model to transmit data to and from the SQL server2000.
    The handheld application connects to a middle tier through the internet and seeks a connection to the database. THe middle tier written in VC++ uses ADO to execute a set of pre-stored SQL commands specific to the session.
    Data from the palm is stored into the temporary table and then stored into the main tables after processing.
    Each session has a transaction started explicitly.
    The problem is if the internet connection is down somewhere after a few statements, the rollback does not happen automatically.

    Is it right to expect rollback to occur or should we explicitly mention a rollback statement.
    Does SQL Server truncate the transaction log in these conditions or does it commit them to the database.

    Default ANSI setting is set off as web based applications require it to be that way.
  2. Chappy New Member

    If you explictly start a transaction, the opreations in the transaction will neither be committed nor rolled back unless you explicitly COMMIT TRANSACTION or ROLLBACK TRANSACTION.

    Perhaps if you need to tidy up the pending transactions when a session expires, you could have a server side event in your session handler, which simply rolls back transactions for that connection until @@TRANCOUNT == 0. I believe most server side technlogies would allow a suitable place to put this code for when a session expires.
  3. gaurav_bindlish New Member

    If I am not mistaken, all the pending transactions should be rolled back when the session expires. So I think if you handle the Data Manipulation within a BEGIN TRANSACTION statement as Chappy suggested, it should rollback all pending transactions as soon as session is disconnected.

    Gaurav
  4. vaanee New Member

    Thanks
    Will try it out
  5. rushmada New Member

    For this Please refer Sql server books online topic Begin transaction - Explicit transactions.

    Rushendra
  6. vbkenya New Member

    ...Remember that ALL data modifications in SQL Server (whether included within an explicit BEGIN TRANSACTION) will always get a BEGIN_XACT and a END_XACT code in the transaction log.

    Rollback is a built-in feature in SQL Server. The reason you might want to include it explicitly is if you want to control the behaviour of your transactions yourself - but SQL Server will always give you one just in case SQL Server itself crashes and has to rollback stuff you've done with it.

    Thanks.



    NHO

Share This Page