Locks held 1

[TBTL]

Hi,

I am having Oracle 8i Enterprise running on a linux server, although i monitor my dbase from a Windows machine.The problem i get is regarding Locks.

it happens when there is a high activity on my dbase i.e during peak hours. When the users try to update a particular row and another user also updates the same row just after the firast user has aquired the lock on the row.After the first user has issued a commit the second user should be able to aquire the lock instead of waiting but this does not happen, instead all the two or one of the two applications keep on hanging and i have to release the locks by killing the sessions.

Guys Please help me!!!!!!!!!!!!!!

 

[sem]

Can you check all the sessions requesting a lock on that row? Can it be that more than 2 session need it and the session that you consider as being the second is in fact the third?
Check trace files about any info on deadlocks. If you find them you have to redesign your application.

Regards, Dima

http://seminihin.narod.ru

 

[TBTL]

There are actually two sessions updating the same row but after the first one has released the lock the second session keeps on waiting for a prolonged period eventhough the first session has already commited.I have to kill the first session to allow the second session to complete its updation.

I have checked many views but i dont have a clue, i doubt on the application that it might me requesting for an exclusive lock externally but i am not sure about it.

Please give me some views which are useful to query to get helpful information.

Note that the problem occurs only sometimes not always even if the same scenario occurs.

Reply and please help me get out of the problem.

Help Guys!!!!!!

 

[sem]

Does it mean that even after commiting the first session still holds a lock? How do you know it has finished commiting? If it passed some stage why do you need to kill it rather than close it in normal way?

Regards, Dima

http://seminihin.narod.ru

 

[mozgheib]

Hello,

This seems to be a development issue. Can I take a look
at your select statement that puts the lock and the update statement that does the commit.

 

[TBTL]

Well you see what happens is that User 1 enter some transactions in the database and commits after that while user one is entering the data on a particular block user2 logs in and updates the same data while user1 is commiting after user 1 has committed and user2 is updating the application on user1's machine hangs after the commit. When i check the views the lock is still being held while the user 1 has already committed.

I dont have a clue of what to do?/

Regarding the application code, i will nee to contact my Oracle Developer who develops applications and who is currently not here, he should be back in a few days.


Thanks guys that you all are ready to help me!! i really admire that

 

[TBTL]

Today after diagnosing my database, i figured out that locks usually occurs at 14:00, this is actually peak hours for my database. I dont really understand what is wrong with my database.

Reply Guys!! help please

 

[JtheRipper]

It does not sound like a database issue to me, rather an application coding issue. You say that usually at 14:00 the problems start, have you checked to see if there are any batch jobs that start at that 14:00 that might also cause locks? Also, do you have triggers firing on these objects? If there are, a good idea might be to have a look at these triggers and see if they do not do an exclusive lock on a table.

Also have a look in your alert.log file for any deadlocks that might have ocurred.

Regards,
Jaco

 

[TBTL]

Well i will have to check the alert log because there are actually four professionals in our environment, one is completely a network admin, another is an aplication developer, the third is a DBA and i am the overall caretaker. Well i have asked my DBA to look at the dbase alert log and she will look into it as soon as possible.

As i told you before i have looked into everything from the physical database point of view but is there a tool available to diagnose this problem automatically.Actually i am having a very tough time with the experts under me, especially my DBA and Application Developer. Can you help me diagnose this problem without their help because they always misguide me and give me stupid solutions.

Help Guys

 

[JtheRipper]

Hi,

If you want to have a look at the alert.log file yourself, in sqlplus do: show parameter bdump, it will return with a directory where the alert.log file can be located. Search in the file for any "deadlocks". Before you start looking for tools or anything you need to find the source of the problem, i.e. what is causing the locks.

Run the following script to get more information on the lock (if you do not have it already) next time when the lock occurs:
--------------
set lines 130
set pagesize 999
col object_name for a35
col os_user_name for a15
select a.ORACLE_USERNAME, a.OS_USER_NAME, b.OBJECT_NAME, b.OBJECT_TYPE, c.mode_held
from v$locked_object a, dba_objects b, dba_lock c
where a.object_id = b.object_id
and c.lock_id1 = a.locked_mode
/
--------------

As I have said before, it seems that the problem is in some application/trigger coding. Have you had a look at any batch jobs starting when the problems occur?

To look if there are any triggers on the table:
select table_name, trigger_name
from dba_triggers
where table_name='TABLE_WHERE_LOCK_IS_HELD';

To see the trigger code:

set long 9999999
set pagesize 1000
select trigger_body
from dba_triggers
where trigger_name='TRIGGER_NAME';

Regards,
Jaco

 

[TBTL]

Hi Guys,

I have refered the alert log but i dont find anything in particular about the locks,Then what could now be the problem

I need to resolve this problem as soon as possible, it is very time consuming for me!!