Home » SQL & PL/SQL » SQL & PL/SQL » Lock
Lock [message #264448] Mon, 03 September 2007 06:47 Go to next message
Messages: 5
Registered: September 2007
Junior Member
I have an application software designed for hospital. this solution software uses a schema of 1200 MB size. it has 541 tables. But very often it hangs and shows locks (DML and transaction lock). How can i get rid of this problem?
Re: Lock [message #264465 is a reply to message #264448] Mon, 03 September 2007 07:46 Go to previous messageGo to next message
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
you need to implement transaction control
Re: Lock [message #264466 is a reply to message #264448] Mon, 03 September 2007 07:57 Go to previous message
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member

what is the problem? Just some users waiting for others to commit?
Or dead locks (one of the user is killed and there is a log in the alert.log)?

In both cases, maybe the sofware is the problem.
First case Normal lock:
Some user open a window, begin to modify data. The sofware asks Oracle to put a lock on the modified data (a row lock in a table) to prevent anyone to modify it until the current user validate or abort his input.
- Tell the users to modify and validate as fast as possible, so the time while the line is locked is short (easier to tell than to do).
- Check the software to see if there is a 'non locking mode'. If so, the software doesn't lock lines, but when the user validates his input, the software checks if someone else modified the line and prompt the user so he can check his input (roughly).

There may be other solutions, but the main things are here.

Second case DeadLock:
This case is typically a sign of bad sofware.
- Rewrite the software to avoid the deadlock.

I think there are no other ways. Maybe someone else will have ideas?



PS: are you sure your sofware was written for Oracle? Are you sure you have no user doing big batch (they lock a lot of line) while the others do TP (they lock one line)?
Previous Topic: %age use using COUNT()
Next Topic: update column problem
Goto Forum:

Current Time: Tue Jul 25 10:03:12 CDT 2017

Total time taken to generate the page: 0.07374 seconds