On 5/8/06, Alessandro Vercelli <alever@(protected)> wrote: > > 1. Is it correct an insert/update without a commit into a sql package? If > yes, when are the inserted/updated data commited?
That really depends on the app.
eg. A stored procedure is used to update the data, the user has to push a button to do the commit, or another button to abandon the transaction and rollback.
Possible problems with that approach is that the user may start the transaction and then go to lunch, leave N row locked.
This however does not cause a deadlock, just blocking, which is not really the same thing.
An ORA-4020 (See ORA-4020.ora-code.com) occurs when 2 sessions each hold a resource that the other session wants to lock. Oracle breaks the tie.
2. Would this the possible cause of the deadclock, as the table indexes > could be locked by a large number of records inserted/updated?
You should probably look on MetaLink for documents related to ORA-4020 (See ORA-4020.ora-code.com). It has been written about exhaustively, and well documented. Search on asktom as well.
ORA-4020 (See ORA-4020.ora-code.com) is caused by inconsistently written SQL. Rather than try to explain it, it would be best if you just read what is already written about it.
3. Is this the correct way to get the choice of performing a rollback?
That really depends on the app requirements.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
On 5/8/06, <b class="gmail_sendername">Alessandro Vercelli</b> <<a href= "mailto:alever@(protected)">alever@(protected)</a>> wrote:<div><span class="gmail _quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb (204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> 1. Is it correct an insert/update without a commit into a sql package? If yes, when are the inserted/updated data commited?</blockquote><div><br>That really depends on the app.<br><br>eg. A stored procedure is used to update the data, the user has to push a <br>button to do the commit, or another button to abandon the transaction and rollback.<br><br>Possible problems with that approach is that the user may start the transaction<br>and then go to lunch, leave N row locked.<br> <br>This however does not cause a deadlock, just blocking, which is not really the same thing.<br><br>An ORA-4020 (See ORA-4020.ora-code.com) occurs when 2 sessions each hold a resource that the other<br>session wants to lock. Oracle breaks the tie. <br><br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb (204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">2. Would this the possible cause of the deadclock, as the table indexes could be locked by a large number of records inserted/updated? </blockquote><div><br>You should probably look on MetaLink for documents related to ORA-4020 (See ORA-4020.ora-code.com). <br>It has been written about exhaustively, and well documented.<br>Search on asktom as well.<br><br>ORA-4020 (See ORA-4020.ora-code.com) is caused by inconsistently written SQL. <br>Rather than try to explain it, it would be best if you <br>just read what is already written about it.<br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> 3. Is this the correct way to get the choice of performing a rollback?< /blockquote><div><br>That really depends on the app requirements.<br><br></div>< /div><br>-- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist <br>
