tags:

views:

52

answers:

2

In an Oracle stored procedure, how do I write a transaction? Do I need to do it explicitly or will Oracle automatically lock rows?

+7  A: 

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

Vincent Malgrat
I guess you should also mention autonomous transactions here.
be here now
What happens if my stored procedure contains DML but no commit statement?
AdamStevenson
@AdamStevenson - if there is no commit in the procedure, the transaction will be ended by the session that calls it; any locks will be held until the session issues a commit (or rollback). If it doesn't ever explicitly do so then the session will implicitly perform a commit or rollback when it ends, depending on how it is terminated. The transaction may exist before the procedure call to. The procedure is one statement within the transaction.
Alex Poole
@be here now: I see your point, I updated my answer
Vincent Malgrat
@AdamStevenson: I updated my answer, I hope it's a bit clearer now.
Vincent Malgrat
+1  A: 

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

andr