[GENERAL] Do transactions rollback by default?
I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
Re: [GENERAL] Do transactions rollback by default?
[EMAIL PROTECTED] wrote: I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. From the Oracle 7 SQL Language Reference Manual: "Oracle Corporation recommends that you explicitly end every transaction your application programs with a COMMIT or ROLLBACK statement,including the last transaction, before disconnecting from ORACLE. If you do not explicitly commit the transaction and the program terminates abnormally, the last uncommitted transaction is automatically rolled back. A normal exit from most ORACLE utilities and tools causes the current transaction to be committed. A normal exit from an ORACLE Precompiler program does not commit the transaction and relies on ORACLE to rollback the current transaction. See the COMMIT command (Embedded SQL) in the next section." So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Hope that helps, Mike Mascari -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
Re: [GENERAL] Do transactions rollback by default?
I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. Disconnect should abort the transaction. It does in PostgreSQL, and I _hope_ every other database. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Do transactions rollback by default?
Mike Mascari wrote: So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Would be interesting to see what SQL*Plus does if you explicitly kill it with SIGKILL (-9). That would tell if it runs the connection in autocommit mode by default or not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
RE: [GENERAL] Do transactions rollback by default?
I ran a couple of tests against an Oracle 7.3.4 database. I inserted a row into a table, then immediately typed exit. The insert was committed automatically. I also did an insert and then killed SQL*Plus with a SIGKILL from another window. The transaction was NOT committed. So, by default, SQL*Plus starts up with option AUTOCOMMIT set to OFF, and if one performs some INSERT/UPDATE/DELETE, an EXIT will in fact commit the transaction for you. If the session is abnormally terminated, the transaction is NOT committed. Hope this helps. Phil Culberson DAT Services -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 16, 2000 11:07 AM To: Mike Mascari Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [GENERAL] Do transactions rollback by default? Mike Mascari wrote: So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Would be interesting to see what SQL*Plus does if you explicitly kill it with SIGKILL (-9). That would tell if it runs the connection in autocommit mode by default or not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
Re: [GENERAL] Do transactions rollback by default?
Phil Culberson wrote: I ran a couple of tests against an Oracle 7.3.4 database. Thanks! I inserted a row into a table, then immediately typed exit. The insert was committed automatically. I also did an insert and then killed SQL*Plus with a SIGKILL from another window. The transaction was NOT committed. So, by default, SQL*Plus starts up with option AUTOCOMMIT set to OFF, and if one performs some INSERT/UPDATE/DELETE, an EXIT will in fact commit the transaction for you. If the session is abnormally terminated, the transaction is NOT committed. There must be something I have in common with the Oracle engineers (the ones who design and code, not the management and sales staff). What else could cause that I expected exactly that behaviour? Hope this helps. Did. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
Re: [GENERAL] Do transactions rollback by default?
In article [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] writes: Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. Disconnect should abort the transaction. It does in PostgreSQL, and I _hope_ every other database. Thanks for all the replies, and saving my sanity. The heavy lifter confirms what some supposed, that he is only talking about the console / GUI front end. He was guessing about programming based on that. I tried psql, and it does NOT default to confirm, which is fine with me. I personally would find it confusing to have the front end default to confirm on exit. I'd rather have it behave the same as a program. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
