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

Question

In an Oracle stored procedure, how do I write a transaction? Does this need to be done explicitly or is Oracle automatically blocking rows?

Answer 1

You might want to review the manual for a particular chapter on the transaction :

A transaction is a logical unit of work that contains one or more SQL statements executed by a single user. [...] The transaction begins with the first executable SQL query of the user. A transaction ends when it is explicitly transferred or rolled back by this user.

You do not need to explicitly start the transaction, this is done automatically. You will need to indicate the end of the transaction with commit (or rollback).

The locking mechanism is a fundamental part of the database, read about it in the chapter Concurrency data and consistency .

---

Regarding Stored Procedures

A stored procedure is a collection of statements that execute in the same transaction as the calling session (*). Typically, transaction management (commit and rollback) belongs to the calling application. The calling application has a broader view of the process (which may include several stored procedures), and therefore, in a better position, you can determine if the data is in a consistent state. Although you can commit in a stored procedure, this is not the norm.

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

Answer 2

@AdamStevenson Regarding DDL, there is a quote from a concept guide:

If the current transaction contains any DML instructions, Oracle first transacts, and then starts and commits the DDL statement as a new, One transaction statement.

So, if you started a transaction before the DDL statement (for example, you wrote INSERT, UPDATE, DELETE, MERGE statements), the transaction started will be implicitly committed - you should always consider this when processing DML statements.

I agree with Vincent Malgrat, you can find very useful information on transaction processing in the Concept Guide.