inserts/updates/deletes with no commit 1

[ctyankeeinok]

I have inherited some code that does not regulary employ a COMMIT when the work is done <groan>. There are many "naked" returns - the proc/function just ends. I know the transaction is committed by default, but when? The procs/functions are called via jdbc from a java program.

 

[SantaMufasa]

I know the transaction is committed by default, but when?

By default, transactions are neither committed nor rolledback...They are in "limbo" until something else happens. Those somethings else are:[ul][li]explicit COMMIT or explicit ROLLBACK[/li][li]implicit COMMIT via:[/li][ul][li]successful execution of a Data-Definition Language (DDL) command:[/li][ul][li]CREATE...[/li][li]ALTER...[/li][li]DROP...[/li][li]TRUNCATE...[/li][li]RENAME...[/li][/ul][li]Data-Control Language (DCL)[/li][ul][li]GRANT[/li][li]REVOKE[/li][/ul][li]Graceful EXIT from application session (e.g., exit from SQL*Plus)[/li][/ul][li]implicit ROLLBACK via un-graceful termination of an Oracle connection or an Oracle instance crash.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ctyankeeinok]

Good info. I would like more info on my specific issue (if possible). Here is what is happening - a java program using jdbc calls a proc which updates tables, but may do a return without a commit. Will a commit occur after the proc returns the result? Will it occur later? Will it rollback? For sake of discussion, assume the DB or java app do not crash. Also, the app does not execute any DDL or DCL

 

[SantaMufasa]

Yankee,

In your specific case (i.e., a from a procedure or function without a COMMIT) implies neither a COMMIT or a ROLLBACK...it simply implies a continuation of the same (so far unCOMMITed) transaction. A COMMIT or a ROLLBACK will most certainly occur later (perhaps days later) per the rules I listed, above.

Conventional wisdom asserts that your code should execute an explicit COMMIT as soon as your application completes a valid unit of work for the business. This means that as soon as you have INSERTed the minimum amount of data that represents a legal ("legal" per business rules) to change of status to the business, then you should execute a COMMIT.

For example, hypothetically, if you do INSERTs for five employees before you do a COMMIT, and the power goes off during the data entry of employee #4, then you must go back and re-enter not only the information for Employee #4, but also for the previous three other employees, as well.

Such is not a good business/technology practice. You should COMMIT as soon as you finish a discrete unit of business work.

Resultingly, it sounds as though your application in not COMMITting as early/often as it should. Only you and your business managers can identify the points in your application that it would be appropriate to COMMIT.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ctyankeeinok]

Great answer SantaMufasa. Thanks for taking the time to expound on the subject. I am with you on when the commit or rollback should occur. Now I just need to figure out what the intent is on the return (commit or rollback), so more sleuthing on my end.... Thanks again!