Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

5 Answers 5

up vote 6 down vote accepted

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)

share|improve this answer
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
show 5 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 this gets to the heart of the issue I think –  Jack Douglas Oct 22 '11 at 14:11
    
"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
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
show 2 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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