7

When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards.

I want to know whether the newer versions of Oracle still has Auto-Commit OFF by default? I also want to know how to put OFF Auto-Commit in SQL Server 2005.

7

Oracle

From AskTom in 2000:

...the only method Oracle operates in is "client tells us when to commit". We do not have an autocommit mode. Various TOOLS and API's do (eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement).

This is still true and probably always will be. For a detailed description of commit see the Oracle docs

SQL Server 2000+

from SO (inverted):

You can turn autocommit OFF by setting implicit_transactions ON

or if you prefer, from the docs (or Mark's more direct link)

  • 1
    With a recent accidental deletion of rows in SQL Server, I feel, AUTO-COMMIT should be OFF by default. It is hazardous to play when it is ON by default. – RPK Oct 22 '11 at 10:44
  • 1
    I say go with the default and be more careful, sorry :) – Jack Douglas Oct 22 '11 at 10:47
  • We are not careful by default and therefore there is an option to Restore. – RPK Oct 22 '11 at 10:53
  • I don't understand what you mean by that I'm afraid? I'm happy to chat if you like? – Jack Douglas Oct 22 '11 at 10:55
  • 1
    I've "Asked Tom" :) – Jack Douglas Oct 24 '11 at 14:00
5

For SQL Server, you would SET IMPLICIT_TRANSACTIONS ON.

In implicit transaction mode, any DML (SELECT, INSERT, DELETE, UPDATE etc) or DDL (CREATE, ALTER, DROP etc) will start a transaction and you must explicitly COMMIT or ROLLBACK.

Alternatively an explicit transaction can be started with BEGIN TRANSACTION.

4

As has been answered above, in SQL you can set implicit transactions on for your connection with that SET IMPLICIT_TRANSACTIONS ON setting and use the links that Mark and Jack provided.

I would caution against looking at doing this across the board and even doing it often in your own connections. The flip side of making this change is that you now have a transaction for all of your actions. If you don't properly manage or cleanup after yourself, you can now have open transactions hanging around blocking DML in your system.

If you are concerned about accidentally deleting/updating the wrong rows when doing manual cleanup there are some better options:

  1. Take a backup first
  2. Backup the table first by selecting all the rows into a new table (Select * into tmpInCaseIMessUp_TableName_mw FROM TableName)
  3. Get in the habit of typing BEGIN TRANSACTION before writing an update/insert/delete from a system...

Sometimes I'll start a session and type

BEGIN TRAN

-- COMMIT TRAN 

(commented out so it doesn't happen but typing it so I don't get busy and forget)

Then I'll do my work after the begin tran, write a select statement in the same session that shows the after picture to make sure I didn't mess up (or just rely on the number of rows affected, etc.) and then commit if all looks good.

  • "If you don't properly manage or cleanup after yourself, you can now have open transactions hanging around blocking DML in your system." not strictly true. When an implicit_transactions connection is terminated, any open transaction is automatically rolled back. – Mark Storey-Smith Oct 22 '11 at 14:16
  • "When an implicit_transactions connection is terminated, any open transaction is automatically rolled back." That is cleaning up ;-) Open a second window in SSMS, get busy there, answer some e-mails and make a phone call... That isn't cleaning up. – Mike Walsh Oct 22 '11 at 14:18
  • I'm thinking application code, you're thinking SSMS. I'm very much in the habit of BEGIN TRAN on every SSMS window as you suggested, for exactly the reasons you mention. – Mark Storey-Smith Oct 22 '11 at 14:29
  • I think that setting can be even worse in application connections. Have you seen the code and transaction management in most applications? Maybe I only get called in when things are bad and I don't see the good but I see a lot of folks not managing transactions well enough with explicit.. Implicit transactions will undoubtedly make that worse in more cases than not with those apps I'm thinking of. I had presumed the OP was referring to SSMS made changes. – Mike Walsh Oct 22 '11 at 14:34
  • 1
    Regarding #3, SSMS Tools Pack has a New Query Template feature that, by default, is set to BEGIN TRAN ... ROLLBACK. – Nick Chammas Oct 24 '11 at 13:07
4

Well... It is not strictly true that Oracle has no autocommit mode. If you look in the underlying OCI, you can see that there is a OCI_COMMIT_ON_SUCCESS flag you can pass to OCIStmtExecute(). Why would you do this? Well if you are using the very common pattern of INSERT→COMMIT→INSERT→... (e.g. when logging time series data) then this halves the number of network round-trips you need to do, it can be a very significant performance enhancement to use this flag, especially over WAN links.

If you pass OCI_DEFAULT as the flag, then yes, autocommit is off, so this is the default behavior.

3

In SQL server (tested with SQL2005), to set auto-commit at the server level, you can use the TSQL as explained by others above or you can use the SSMS GUI. To use SSMS:

  1. Open SSMS and connect to your SQL server.
  2. On the menu bar click on Tools > options.
  3. In the options window, expand 'Query Execution' > 'SQL server'.
  4. Click on 'ANSI'.
  5. Check\Uncheck the 'SET IMPLICIT_TRANSACTIONS' check box.

If the box is unchecked it means that auto commit is on. If the box is checked it means that auto commit is off.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.