How do transactions within Oracle stored procedures work? Is there an implicit transaction? How do transactions within Oracle stored procedures work? Is there an implicit transaction? oracle oracle

How do transactions within Oracle stored procedures work? Is there an implicit transaction?


You might want to browse the concept guide, in particular the chapter about transactions:

A transaction is a logical unit of work that comprises one or more SQL statements run by a single user. [...] A transaction begins with the user's first executable SQL statement. A transaction ends when it is explicitly committed or rolled back by that user.

You don't have to explicitely start a transaction, it is done automatically. You will have to specify the end of the transaction with a commit (or a rollback).

The locking mechanism is a fundamental part of the DB, read about it in the chapter Data Concurrency and Consistency.


Regarding stored procedures

A stored procedure is a set of statements, they are executed in the same transaction as the calling session (*). Usually, transaction control (commit and rollback) belongs to the calling application. The calling app has a wider vision of the process (which may involve several stored procedures) and is therefore in a better position to determine if the data is in a consistent state. While you can commit in a stored procedure, it is not the norm.

(*) except if the procedure is declared as an autonomous transaction, in which case the procedure is executed as an independent session (thanks be here now, now I see your point).


@AdamStevenson Concerning DDL, there's a cite from the Concept's Guide:

If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.

So if you have started a transaction before the DDL statement (e.g. wrote an INSERT, UPDATE, DELETE, MERGE statements), the transaction started will be implicitly commited - you should always keep that in mind when processing DML statements.

I agree with Vincent Malgrat, you might find some very useful information about transaction processing at the Concept's Guide.