Home » RDBMS Server » Performance Tuning » HIGH no of LOCKS exclusive in tables for UPDATE Statement (Oracle 9.2.0.8)
HIGH no of LOCKS exclusive in tables for UPDATE Statement [message #335136] Mon, 21 July 2008 03:23 Go to next message
udara
Messages: 9
Registered: July 2008
Location: COLOMBO
Junior Member

in our production system getting slowness for transation due to exclusive records exist in system. i have kill the process for taking back to normal the system but couldnt recoved.
1. why such exclusive lock hapeen
2. any technique in oracle to avoid such situation
Thanks
Re: HIGH no of LOCKS exclusive in tables for UPDATE Statement [message #335143 is a reply to message #335136] Mon, 21 July 2008 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to show us what you did and saw.

If you modify something an exclusive lock oh this thing is mandatory and there is no way top workaround this otherwise there could get anything and you will never know if it is valid or not.

Regards
Michel
Re: HIGH no of LOCKS exclusive in tables for UPDATE Statement [message #338053 is a reply to message #335143] Sat, 02 August 2008 16:12 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
small explanation...

1. why such exclusive lock hapeen?
To maintain the Data consistency & integrity in database.


2. Any technique in oracle to avoid such situation
If normalization of tables is done properly and code are designed properly we can avoid situation of dead lock. Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:

1. Asking the HOLDER to commit or rollback,
2. Killing the session which holds the lock,
SQL>ALTER SESSION KILL SESSION sid, serial#;
3. killing the shadow process from o/s.
4. By ROLLBACK FORCE or COMMIT FORCE in distributed transaction.
Re: HIGH no of LOCKS exclusive in tables for UPDATE Statement [message #338058 is a reply to message #335136] Sat, 02 August 2008 20:59 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
we would need much more information to truly help you. Oracle's locking and concurrency mechanisms are the best in the database world. It would be a rarer situation in which locks themselves were the culprit of your slowdowns. It is more likely an application desing issue.

Kevin

[Updated on: Sat, 02 August 2008 20:59]

Report message to a moderator

Previous Topic: criteria for partitioning
Next Topic: statspack report
Goto Forum:
  


Current Time: Sat Dec 03 04:13:01 CST 2016

Total time taken to generate the page: 0.29745 seconds