You must Sign In to post a response.
  • Category: SQL Server

    About Trasaction statement in sqlserver

    In sqlserver auto commit is there so what is the use of commit in sqlserver.
  • #768011
    Hi,
    A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

    Transaction are very important in real time world.
    There are following commands used to control transactions:
    COMMIT: to save the changes.
    ROLLBACK: to rollback the changes.
    SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
    SET TRANSACTION: Places a name on a transaction.

    Lets come to your question.

    Commit command is used to permanently save any transaction into database.
    Following is Commit command's syntax,


    commit;


    Example like when you are buying some online shopping and you have order an item and payment done through your Online Banking Account. So once the money got transferred. like, Update or Insert statement it should be committed to make the transaction done. Once the Commit is done it is like Over.

    Thanks,
    Mani

  • #768024
    Hi Prasanta Kumar

    Transaction is nothing but making use of commit and roll back. If we execute both the statements/conditions successfully it commits. Like any one condition out of two condition is failed then transaction is roll backed(it keeps the records like how it was previously).
    If it executes both the conditions the transaction is committed(it updates the records with new values).

    Sridhar Thota.
    Editor: DNS Forum.

  • #768026
    Hi,

    Refer the below..

    We can write more than one sql statement in transaction with Begin, Commit, rollback transactions. When we put sql statements inside begin transaction, then it means that will be locked, until commit or rollback transaction executed. We can use these because if some issue with some sql statements we can rollback whole transaction instead of commit.
    For example if some insertions happen in some set of table then we need to update some other tables. this scenario if any of insertion is failing we will rollback all transactions instead of inserting and updating.
    SQL statements uses the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements.

    Ex:
    BEGIN TRANSACTION [T1]
    BEGIN TRY
    INSERT INTO Table1...
    UPDATE Table2...
    COMMIT TRANSACTION [T1]
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION [T1]
    END CATCH
    GO


    Hope this will help you

    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring


Sign In to post your comments