Home » SQL & PL/SQL » SQL & PL/SQL » Too many Commits
Too many Commits [message #198314] Mon, 16 October 2006 09:38 Go to next message
kelliherj
Messages: 11
Registered: October 2006
Location: Killarney
Junior Member
I am doing a code review of a package for my manager where the basic problem is deemed to be that a deadlock is existant which is slowing down the application and in reviewing the code the manager feels it is due to a lack of commits to the Database.

In the process of correcting this I want to pose members here the question - would too many commits slow the application down too?
Re: Too many Commits [message #198318 is a reply to message #198314] Mon, 16 October 2006 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It depends.
If you're thinking of putting commits inside procedural loops, then just don't do it. It slows things down, and increases your chances of getting ORA-1555 Snapshot too old errors.

A Commit or Rollback marks the end of a transaction, and that is really the only point that you should issue one - when a logical unit of work is complete (commit) or everythings gone horribly wrong and you want to start over (rollback).

Deadlocks are more a sign that you've got some problems with your application design than anything else.
Actually - deadlocks don't slow down applications - they bring them to a complete stop with ORA error messages telling you that you were involved in a deadlock and that your session has been chosen to be killed.
Do you mean Deadlock, or do you just mean that you are waiting on other other peoples locks for access to resources?
Re: Too many Commits [message #198319 is a reply to message #198318] Mon, 16 October 2006 09:53 Go to previous messageGo to next message
kelliherj
Messages: 11
Registered: October 2006
Location: Killarney
Junior Member
Not sure was told it is a deadlock caused by one person holding onto information for too long.
Cheers for the information on the commits.
I wasn't involved in the application desing, I only started on the project last week.
My task is to review this code and provide recommendations on how to remove the deadlock issue.
Basically its an exercise in getting me up to speed on understanding the application.
My supervisor suggesting looking for areas to include commits.
Basically the thinking was that because of this one cursor 1 accessor is holding onto the information for too long and therefore if we commmited more it might alleviate this problem.

[Updated on: Mon, 16 October 2006 09:54]

Report message to a moderator

Re: Too many Commits [message #198406 is a reply to message #198319] Tue, 17 October 2006 01:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A deadlock happens when you've got two sessions, with each one holding a lock on a resource that the other is attempting to lock, so that you would end up with each one waiting for the other to release the lock first.

It's difficult to comment further without some idea of how the application works.
Previous Topic: formating the query output
Next Topic: loop problem
Goto Forum:
  


Current Time: Thu Feb 13 15:51:44 CST 2025