1 2 Previous Next 15 Replies Latest reply on Oct 21, 2010 12:16 PM by ADFBeginer

    Table lock

      When multiple user access the same form, the table hangs. Why is it ?.
      Users are accessing different record from the same table.
        • 1. Re: Table lock
          Pavan Kumar

          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
          • 2. Re: Table lock
            When multiple user access the same form, the table hangs. Why is it ?.
            post reproducible test case.

            Post results of
            SELECT * from v$version;
            • 3. Re: Table lock
              Mohamed ELAzab
              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.
              Kind regards
              • 4. Re: Table lock
                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
                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 
                Well, somewhere i feel it would be TM lock on the table.Red [http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html]

                • 5. Re: Table lock
                  access the same form
                  what does that mean when it comes to a database transaction?
                  • 6. Re: Table lock
                    Let me make a wild guess. Do you have indexes on your foreign keys? If not, then create them.

                    • 7. Re: Table lock

                      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
                                 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;
                      • 8. Re: Table lock
                        I am using 11g.

                        Oracle Database 11g Enterprise Edition Release - Production
                        PL/SQL Release - Production
                        CORE Production
                        TNS for 32-bit Windows: Version - Production
                        NLSRTL Version - Production
                        • 9. Re: Table lock

                          I ran your query. The below is the output.


                          1 115 2798 INACTIVE frmweb.exe 1 128 Dml Share-Table

                          Edited by: Forms User on Oct 17, 2010 7:41 AM
                          • 10. Re: Table lock

                            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
                            • 11. Re: Table lock
                              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.
                              • 12. Re: Table lock
                                Hi Form User,
                                Open your form in Forms Builder

                                Go to your related Data Block and Check these database properties on your form.
                                Update Allowed
                                Locking Mode
                                Update Changed Columns Only
                                Key Mode

                                give us the values for the above properties.

                                • 13. Re: Table lock
                                  Update Allowed : YES
                                  Locking Mode :Automatic
                                  Update Changed Columns Only : NO
                                  Key Mode :Automatic
                                  • 14. Re: Table lock
                                    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. :)

                                    1 2 Previous Next