Post reply

Question re inserts and updates

  • January 4, 2006 at 6:58 pm

    #166665

    Greetings, I am doing inserts and updates using the query analyzer, for example, UPDATE table_name set ; or insert into table_name ; This is not within a transaction block. When are inserts, updates and deletes committed? Must I do an explicit commit? WHen I tried it a message was returned indicating it was not part of a transaction block. THanks.

  • January 4, 2006 at 9:11 pm

    #612766

    There is a setting that determines when the transaction is commited: Set Implicit_transactions.  If this is off, the transaction is committed immediately unless you use BEGIN TRANSACTION first.  When I use Query Analyzer, my statements are automatically committed so it must be off by default.

    If you want an explicit transaction and the setting is off,you will do this

    Begin tran

    ---update, insert, delete

    commit trans

    If you have the setting on, you can use explicit transactions or do this

    -- update, insert or delete

    commit tran

    There is a good example in SQL Books Online on this topic, be sure to check it out.

     

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • January 4, 2006 at 11:46 pm

    #612784

    Many ppl on here (I've been too lazy to do it unless doing big bulk updates!) ALWAYS start their scripts with a BEGIN TRANSACTION when working in QA.

    They also automatically put a ROLLBACK TRANSACTION at the bottom of their script.  This allows them to test it out, etc and, once happy, they replace the ROLLBACK with a COMMIT.  This has saved my bacon once or twice   Otherwise QA commits things there and then when you execute them.

    Beware that QA keeps a single connection open per window by default - the same connection (and thus the same transaction context) is reused between subsequent executions.  This means that I could do something like

    BEGIN TRANSACTION

    delete from table A

    and then execute this.  Note that the transaction is still alive.  When I quit QA or close that query window, I get a question about committing or rolling back existing transactions...

  • January 5, 2006 at 12:11 pm

    #613007

    Thank you both.

  • January 6, 2006 at 7:22 am

    #613213

    The suggestion Ian gave you is fine in development environment, but be carefull with this in production with multiple users using your database. The Begin Transaction and running test SQL statements in QA without ROLLBACK or COMMIT are locking up the records and any users hitting the tables you are working on may get stuck...

    I use this technique sometimes when I need to see what the impact of the statement is going to be without the need to restore the DB if I mess up. But this requires some follow up query to find out if the results are the one I expected before ROLLBACK or COMMIT. And in the meantime stuff is locked...

     

     

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 8, 2006 at 6:04 am

    #613431

    Yeah - most certainly not to use in a busy production environment   Same goes for normal app programming - don't have user interactions in the middle of a DB transaction.  Unless you are the DBA and using Query Analyser and don't give a stuff about the other users    

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply