This content has been marked as final. Show 15 replies
I am not sure what type of lock during the form access to the table is carried, perhaps it might locked across the table level instead of row.
.(TM) no changes can be either to the table structure or the objects which the table refers to i.e. referenced objects( does referenced objects mean the the tables on which we have the foreign keys etc).
- Pavan kumar N
When multiple user access the same form, the table hangs. Why is it ?.post reproducible test case.
Post results of
SELECT * from v$version;
Please check the v$lock.
and let us know .I think that your users are accessing same block in memory when they query the table so that a contention occurs.
When multiple user access the same form, the table hangs. Why is it ?.Users are accessing different record from the same table.>
It won't be the table that hangs, it would be the user sessions that would be getting hanged.What is the oracle version you are using?Did you check the locks when the user session hangs?Accessing the records, does that mean only select? or even update/delete are fired?
Check the enqueue
Well, somewhere i feel it would be TM lock on the table.Red [http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html]
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request /
what does that mean when it comes to a database transaction?
access the same form
Let me make a wild guess. Do you have indexes on your foreign keys? If not, then create them.
is your Form having a grid that populates the multiple rows of table data?. if so check the form whether records are fetch as select for update. in this case different users accessing data from the same form will leads to a row level lock.
Please fire the below sql when you come across a lock situ.
SELECT gvh.inst_id Locking_Inst, gvh.sid Locking_Sid, gvs.serial# Locking_Serial, gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid, decode(gvh.type, 'MR', 'Media_recovery', 'RT', 'Redo_thread', 'UN', 'User_name', 'TX', 'Transaction', 'TM', 'Dml', 'UL', 'PLSQL User_lock', 'DX', 'Distrted_Transaxion', 'CF', 'Control_file', 'IS', 'Instance_state', 'FS', 'File_set', 'IR', 'Instance_recovery', 'ST', 'Diskspace Transaction', 'IV', 'Libcache_invalidation', 'LS', 'LogStaartORswitch', 'RW', 'Row_wait', 'SQ', 'Sequence_no', 'TE', 'Extend_table', 'TT', 'Temp_table', 'Nothing-') Waiter_Lock_Type, decode(gvw.request, 0, 'None', 1, 'NoLock', 2, 'Row-Share', 3, 'Row-Exclusive', 4, 'Share-Table', 5, 'Share-Row-Exclusive', 6, 'Exclusive', 'Nothing-') Waiter_Mode_Req FROM gv$lock gvh, gv$lock gvw, gv$session gvs WHERE (gvh.id1, gvh.id2) in ( SELECT id1, id2 FROM gv$lock WHERE request=0 INTERSECT SELECT id1, id2 FROM gv$lock WHERE lmode=0) AND gvh.id1=gvw.id1 AND gvh.id2=gvw.id2 AND gvh.request=0 AND gvw.lmode=0 AND gvh.sid=gvs.sid AND gvh.inst_id=gvs.inst_id;
I am using 11g.
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - Production
PL/SQL Release 188.8.131.52.0 - Production
CORE 184.108.40.206.0 Production
TNS for 32-bit Windows: Version 220.127.116.11.0 - Production
NLSRTL Version 18.104.22.168.0 - Production
I ran your query. The below is the output.
LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS MODULE WAITING_INST WAITER_SID WAITER_LOCK_TYPE
1 115 2798 INACTIVE frmweb.exe 1 128 Dml Share-Table
Edited by: Forms User on Oct 17, 2010 7:41 AM
there is a blocking lock detected. This says the session ID 115 is blocking the DML of Session id 128. Kindly check the session details of 115. And let me know the other answers to my earlier reply in this regard.
A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCK TABLE. Reasons are to reserve DML access to the table on behalf of a transaction and prevent DDL operations.Table locks prevent the an exclusive DDL lock on the same table which prevents DDL operations. Example, a table cannot be altered or dropped if any uncommitted transaction holds a table lock for it.
Check the below link for further clarification
Edited by: Kanchana Devasurendra on Oct 18, 2010 8:58 AM
Forms display multiple rows in a grid with a 'Post'. Once we press the save button, it uses statement 'COMMIT'
Session 115 using Table-A with mutilple rows fetched,
Session 128 also Using Table-A but differents records.
When session 128 try to save, it hangs.
I have doubt 'COMMIT' or do_key('Commit_form') using FOR UPDATE. I do not know.
Hi Form User,
Open your form in Forms Builder
Go to your related Data Block and Check these database properties on your form.
Update Changed Columns Only
give us the values for the above properties.
Update Allowed : YES
Locking Mode :Automatic
Update Changed Columns Only : NO
Key Mode :Automatic
probably you need to change the values for these parameters and you might need to do some code changes as well. also check the parameter "fetch all rows ".
Let me know once the changes done.
Strongly recommend to go through the forms manual, so that you will have a fare idea about the same. I have done some forms about 5/6 years back and not in touch these days. :)