CODEWITHSUNDEEP
sqltransactionscommit
JUNGE
JUNGE

Reputation: 73

When do I have to commit?

I heard in SQL I do not have to commit every statement. Perhaps create I don't have to. So can you answer me which Statements I have to commit? I read, that I have to commit all transactions, but I don't know what this is and can't find it anywhere.

Thanks for your help.

Upvotes: 0

Views: 1004

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Per the SQL standard, most statements that require a transaction will automatically open one.

Some database engines, such as SQL Server, will (by default) automatically commit the transaction if the statement completes successfully. See Autocommit Transactions.

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes

SQL Server also has an Implicit Conversions mode which will leave the transaction open until it's explicitly commited.

When operating in this second such mode (which is the default, I believe, for Oracle), or if you've explicitly created a transaction, it's up to you as a developer when to commit the transaction. It should be when you've accomplished a "complete" set of operations against the database.

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

If you BEGIN a transaction then you have to either ROLLBACK or COMMIT

Example:

BEGIN TRAN
    --Your code 
    INSERT INTO
    NewTable
    SELECT *
    FROM TABLE
COMMIT TRAN

If you do not use that, it is committed upon execution. So the follow will either fail or be committed:

INSERT INTO
NewTable
SELECT *
FROM Table

If there is an error (like there is no NewTable in the DB) the execution will raise an error and the transaction will roll back. If there is no error the transaction will be committed.

Upvotes: 0

Related Questions