Auto-Commit in SQL Server and Oracle

Question

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.

Answer 1

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)

Answer 2

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.

Answer 3

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.

Answer 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.

Answer 5

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.