Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Really Dumb Question About COMMIT Statement

    In reading my SQL books over the weekend, I found an example of the COMMIT statement and while I've never used this actual statement on a particular database, I'm not sure I understand the purpose of this command and was hoping someone could please explain it to a beginner like myself. Before you post a URL to a document such as this, please know the name of the statement 'COMMIT' seems very straight forward, you issue a command and then finalize it with COMMIT however when I use UPDATE or ALTER statements to a database, why do I never use the COMMIT statement? All my changes are made on the fly. I'm never required to use COMMIT to make changes...for example

    Code:
    gfe=# UPDATE employees
    SET position = 'Configuration Management'
    WHERE emp_id = 234 AND lname = 'Wilson';
    UPDATE 1
    That change was made on the fly w/o using the COMMIT. In trying to be somewhat enabled, I referenced this command in my SQL book / guide at my desk and it says that statements like INSERT, UPDATE, & DELETE are implicit which don't seem to require the use of the COMMIT statement but statements like START are explicit and considered 'open transactions' & appear to be finalized with the use of the COMMIT statement. Can someone please break this down for me to help me better understand? I guess I've never used START statements or understand in what circumstances they would be used. Sorry if this seems really basic to most of you but please note I am learning, I did try and answer my own question, and I'm asking for someone to help me understand it at a basic level.

    Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the purpose of START (or BEGIN TRANSACTION, or similar) is to initiate a series of statements which will be executed together as a group

    they will all complete successfully, in which case the COMMIT command will be given, or else there will be a problem along the way, in which case the ROLLBACK command will be given, and none of the statements, even those that had already ostensibly finished, will take effect

    as you can imagine, suspending these actions in case they have to be "undone" consumes some additional resources

    classic example of a transaction is the transfer of money between bank accounts -- you want both the withdrawal from one account and the deposit to the other to complete succesfully, or else you don't want either of them to take effect

    individual statements, executed one at a time, all have implicit COMMIT

    there wouldn't be much point in putting START/COMMIT around an individual statement, would there -- if it completes successfully, you'd COMMIT, which would seem somewhat redundant, and if it doesn't complete successfully, you'd ROLLBACK, except there would be nothing to roll back since it didn't complete successfully

    it's only when there is a series of interdependent statements that you'd need the transaction concept of all-or-none successful completion


    p.s. in your database, how many employees share an employee id?

    Last edited by r937; 05-01-12 at 13:34.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    individual statements, executed one at a time, all have implicit COMMIT
    That is an overly-generalized statement and not true for all databases and clients e.g. Oracle's SQL*Plus autocommit is off by default.

    If the client turns off auto-commit then individual statement do need a commit as well.

    This is particularly helpful if e.g. you forgot a proper WHERE clause for your DELETE statement. It's a safety net I wouldn't want to miss

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thanks all. I have not gotten to SQL transactions yet so I guess it makes sense I didn't understand the COMMIT statement as of yet.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    This is particularly helpful if e.g. you forgot a proper WHERE clause for your DELETE statement. It's a safety net I wouldn't want to miss
    thanks for the clarification

    as for the safety net, i don't need one

    all you have to do is forget the WHERE clause just once, and you will never, ever do that again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by CarlosinFL View Post
    Thanks all. I have not gotten to SQL transactions yet so I guess it makes sense I didn't understand the COMMIT statement as of yet.
    This is not specific for SQL transactions. Any kind of transaction has a "begin of transaction" and an "end of transaction". You may want to read: Transaction processing - Wikipedia, the free encyclopedia Also very important to understand database transactions are the ACID properties. Those are usually one of the first things taught in database system courses. ACID - Wikipedia, the free encyclopedia
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •