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
gfe=# UPDATE employees
SET position = 'Configuration Management'
WHERE emp_id = 234 AND lname = 'Wilson';
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.
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?