Home » SQL & PL/SQL » SQL & PL/SQL » Start Transactional Control in PL/SQL (Oracle Database)
Start Transactional Control in PL/SQL [message #679280] Wed, 19 February 2020 19:17 Go to next message
aks11
Messages: 1
Registered: February 2020
Junior Member
Hi All,

1. How do we start transaction control in the begining of PL/SQL program (eg procedure doing insert and updates)? Is there a special syntax for this?
2. Do we really need to start a Tracsaction Control in the begining of a pl/sql procedure (doing insert / updates)? If yes then why?

I am aksing this question as someone ask me this in an interview.
Though I think it should be implicit in oracle as when the procedures executes insert or update and the transaction should get completed with commit or rollback is done. In case of a crash, the transaction will be rolled back anyway.

I have never heard of starting transaction control transaction control in the begin of a procedure which is doing insert / updates. So I am confused.

Please let me know in case I am missing something here.

Thanks.
AKS
Re: Start Transactional Control in PL/SQL [message #679282 is a reply to message #679280] Thu, 20 February 2020 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. You do not explicitly start a transaction, Oracle starts it as soon as it is needed that is the first time you make a modification or acquire a lock. The transaction is completed and the locks are released when you commit or roll back.
2. No.

Note: these are Oracle specific answers which can be different in other DBMS (which have BeginTransaction and, possibly, EndTransaction statements/methods).

Re: Start Transactional Control in PL/SQL [message #679287 is a reply to message #679280] Thu, 20 February 2020 01:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If I were the interviewer, I would be fine with your answer. If you had added that you could explicitly start a transaction with SET TRANSACTION (not forgetting the ISOLATION LEVEL clause) I would have been impressed.
Re: Start Transactional Control in PL/SQL [message #679289 is a reply to message #679287] Thu, 20 February 2020 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Right! And SQL Language Reference states:

Quote:
A transaction implicitly begins with any operation that obtains a TX lock:
  • When a statement that modifies data is issued
  • When a SELECT ... FOR UPDATE statement is issued
  • When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package
Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction.

It also would says that you also implicitly start a transaction with a distributed query (i.e. using a database link):
SQL> select xid from v$transaction where ses_addr=(select saddr from v$session where sid=sys_context('userenv','sid'));

no rows selected

SQL> select count(*) from scott.emp@mika;
  COUNT(*)
----------
        14

1 row selected.

SQL> select xid from v$transaction where ses_addr=(select saddr from v$session where sid=sys_context('userenv','sid'));
XID
----------------
03001B00E8FD0000

1 row selected.

[Updated on: Thu, 20 February 2020 02:02]

Report message to a moderator

Re: Start Transactional Control in PL/SQL [message #679322 is a reply to message #679289] Fri, 21 February 2020 08:17 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
It fairly easy, issue the following command in your code. You must have completed your previous transaction by using a commit or rollback

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

After this point until you issue a commit or rollback or issue a DDL command such as truncate or create every select, update, insert, and delete will be part of the same transaction and every thing your queries will see will be from the time the set transaction was issued or changes that you made after the set transaction

[Updated on: Fri, 21 February 2020 08:18]

Report message to a moderator

Re: Start Transactional Control in PL/SQL [message #679323 is a reply to message #679322] Fri, 21 February 2020 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SET TRANSACTION is not required to use Oracle in the usual (default) modes.
You use SET TRANSACTION ONLY when you don't want to use the usual modes.

Re: Start Transactional Control in PL/SQL [message #679327 is a reply to message #679323] Fri, 21 February 2020 12:06 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I agree. But the question was how to do a procedure as a single transaction, I probably misunderstood the question.
Previous Topic: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block.
Next Topic: Tab Delimited
Goto Forum:
  


Current Time: Thu Mar 28 10:05:13 CDT 2024