Start Transactional Control in PL/SQL [message #679280] |
Wed, 19 February 2020 19:17 |
|
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 #679287 is a reply to message #679280] |
Thu, 20 February 2020 01:26 |
John Watson
Messages: 8937 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 |
|
Michel Cadot
Messages: 68658 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 |
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
|
|
|
|
|