Home » RDBMS Server » Server Administration » Row/table/database locks
| Row/table/database locks [message #52895] |
Mon, 19 August 2002 15:11  |
krushanu
Messages: 4
Registered: August 2002
|
Junior Member |
|
|
Hope everyone is doing well.
I, on the other hand am not doing well at all !!
I need help !!
I am an oracle newbie.
Our production database locked up once again. Actually it has been happening pretty periodically and the customer is slowly losing patience.
The last time it happened, I ran a query which gave me the following results.
os_user os_pid db_user sid type lmode request block owner table
------------------------------------------------------------------------------------------------------------------------------------------
admin 534:415 user1 142 DML None Share Not Blocking SWIC table1
admin 534:442 user1 290 DML Row-X None Not Blocking SWIC table_trg_2
admin 534:442 user1 290 DML Row-X None Blocking SWIC table_trg_2
admin 534:442 user1 290 Trans Exclu None Not Blocking SWIC table_trg_2
admin 534:442 user1 290 DML Row-X None Not Blocking SWIC table1
admin 534:442 user1 290 DML Row-X None Blocking SWIC table1
admin 534:442 user1 290 Trans Exclu None Not Blocking SWIC table1
admin 534:408 user1 155 DML None Row-X Not Blocking SWIC table1
admin 534:486 user1 187 DML None Row-X Not Blocking SWIC table1
admin 534:474 user1 172 DML None Row-X Not Blocking SWIC table1
admin 534:506 user1 250 DML None Share Not Blocking SWIC table1
admin 534:497 user1 96 DML None Row-X Not Blocking SWIC table1
As you can see, the sid=290 is holding row_exclusive locks on the table table_trg_2 and table1.
The table table_trg_2 is being updated in the before insert trigger on table1 just to keep track of who last updated table1.
I really could not understand, how a row-excusive lock could be a blocking lock.
In the above table1, the primary key has a client_id in it. It is extremely unlikely that all the sessions are working wih the same client and thus the same rows. So why is the row-x (sx) lock blocking other dml statements which, most probably are update different rows ?
Also, why does the complete database freeze up because of locks on these tables ?
After the tables start getting locked, even connecting to the instance using sql plus does not work. Sql plus just hangs.
Can somebody decipher what might be going on from the above data ?
Thanks in advance.
The query that i ran to get the above result is:
--------------------------------------------------------------
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID session_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID;
-------------------------------------------------------------------
Regards,
Krushanu.
|
|
|
|
| Re: Row/table/database locks [message #52897 is a reply to message #52895] |
Mon, 19 August 2002 16:05  |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
You have to make sure that you have indexes on the FK for the tables involved in the transaction. The explanation is:
"When your application has referential integrity and attempts to modify the child table, Oracle will get a TABLE LEVEL SHARE LOCK on the parent table when there is NO index on the foreign key. To bypass this problme, the most efficient way is to create indexes for all foreign key defined in the database."
Hope that helps,
clio_usa
OCP - DBA
[Updated on: Thu, 15 October 2009 10:53] by Moderator Report message to a moderator |
|
|
|
| Re: Row/table/database locks [message #52912 is a reply to message #52895] |
Tue, 20 August 2002 07:17 |
krushanu
Messages: 4
Registered: August 2002
|
Junior Member |
|
|
Hello,
Thanks a lot for such a quick response !
what i found was when i update the parent table, oracle obtains a share lock on the child table - and i think this is the lock that is causing the problem.
I understand what you are saying and i checked to see if there was an index on the foreign key, and indeed there was !
But let me give you some more information.
The parent table table_parent has about 15,000 rows.
The child table table_child has about 36,000 rows.
The foreign key is made up of 2 columns - custid and start_date.
What i found was that the start_date cloumn in the table_child, which is part of the foreign key, has mostly null values.
It might be having say only 5000 rows out of the 36,000 with not null values.
Do you think that when oracle is trying to enforce referential integrity, these null values are causing a problem ?
Have you had such an experience before ?
I have had problems in the past with subqueries:
e.g.
select table1_col1 from table 1
where table1_col1 in (select table2_col2 from table2).
Now if table2_col2 is a mix of null and not null values, and the tables are large enough, my query does not return - it just hangs.
I have a feeling that the problem i am facing with the foreighn keys is similar to this situation.
I would really appreciate any insight into this!
Thanks once again !
Regards,
Krushanu.
|
|
|
|
| Re: Row/table/database locks [message #52918 is a reply to message #52895] |
Tue, 20 August 2002 12:55 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
When you say the FK's are indexed - do you mean that there are Foreign Key constraints between table1 and whatever other table (where you do the "auditing")? If that's the case then your trigger is probably actuall a pair of triggers (before and after) and a package to avoid the mutating table problem. The issue of locking the entire child table only applies to the case where a FK constraint exists AND the FK is not indexed (or indexed incorrectly).
Your statement that sqlplus doen not work makes me think that record locking isn't the problem. Does killing the blocking SID (290) resolve the problem? What platform and version DB are you running. Can you monitor machine CPU when the problem occurs?
|
|
|
|
| Re: Row/table/database locks [message #426473 is a reply to message #52918] |
Thu, 15 October 2009 10:50 |
yomghi
Messages: 3
Registered: October 2009
Location: paris
|
Junior Member |
|
|
Hello,
I meet the same kind of problem with oracle Forms 10g and database 11g.
Does anybody know the way I can solve this. It is a real mess and I don't know what to do...
Thanks for your help.
Yomghi
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 12:35:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
