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

    Table lock

    ADFBeginer
      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
          Hi,

          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
            sb92075
            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
              Hello,
              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
              Mohamed
              • 4. Re: Table lock
                Anand...
                Hi,
                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]


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

                    regards
                    • 7. Re: Table lock
                      KanchDev
                      Hi,

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

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

                          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
                          • 10. Re: Table lock
                            KanchDev
                            Hi

                            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

                            http://www.indiana.edu/~dbateam/Documents/oracle_locking.ppt

                            Cheers
                            Kanchana

                            Edited by: Kanchana Devasurendra on Oct 18, 2010 8:58 AM
                            • 11. Re: Table lock
                              ADFBeginer
                              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
                                KanchDev
                                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.

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

                                    Cheers
                                    Kanchana.
                                    1 2 Previous Next