| Transaction Clarification [message #38450] |
Thu, 18 April 2002 07:16  |
Frank Smith
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
I realize this is not an 'expert' level question but it is very important that I get an expert level answer so I am posting it in this forum.
I'm trying to ensure I have the correct understanding of transactions in Oracle.
According to the documentation, as soon as DML statement is made, a transaction is implicitly started and continues until a commit or rollback is hit.
How does this work when combined with a client application? Say a client application opens a connection via ODBC and runs a couple DML statements and PL/SQL procedures. The procedures have no commits or rollbacks and the client has not explicitly wrapped the session in a transaction (i.e. no start transactions, rollbacks or commits coded anywhere).
Is it correct to assume the very first client DML statement starts a new transaction and the remainder of the client's session stays in that transaction?
What happens if the client suddenly terminates? Will the work be rolled-back by Oracle or does Oracle simply commit once the connection times out and disconnects?
Now, say a raise_appliction_error is encountered in a PL/SQL procedures. My understanding is this causes a implicit rollback. Will all the clients work be rolled back from the start of the session?
Is there anything that I forgot about or am missing?
Thanks for the clarification!
Frank
|
|
|
|
| Re: Transaction Clarification [message #38452 is a reply to message #38450] |
Thu, 18 April 2002 09:01  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Oracle does an implied trasaction if a "set transaction" isn't issued. I would suggest you always create your apps with explicit transactions and do commits and rollbacks as needed.
You can test what your looking for yourself. Create a test schema and load some small tables. Using sqlplusw connect to the schema and do an update/insert and close the window before doing a commit or rollback. Log back in and see what it did. You can test all your scenerios this way so you know what will happen. You can also create an ODBC datasource and an test Access DB and link an Oracle table and do some changes and then close it. I beleive ODBC commits so test it. Always the best way.
|
|
|
|
| Re: Transaction Clarification [message #38470 is a reply to message #38450] |
Fri, 19 April 2002 07:26 |
Jay Ramlakan
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
If a client terminates abnormally without committing, Oracle rollbacks the entire transaction (up to the last commit).
When using ODBC, make sure autocommit is OFF.
If an error is encountered in a transaction, processing stops but there is not necessarily a rollback, unless you specify one. However, if you commit after the error, successful updates (not the error) will be committed. See my example below.
USTST59>create table foo (x char(1));
Table created.
USTST59>insert into foo (x) values ('J');
1 row created.
USTST59>insert into foo (x) values ('JL');
insert into foo (x) values ('JL')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
USTST59>commit;
Commit complete.
USTST59>select * from foo;
X
-
J
Hope this helps,
-Jay
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
