Forum Stats

  • 3,759,513 Users
  • 2,251,557 Discussions
  • 7,870,690 Comments

Discussions

When table is going to be locked?

JackK
JackK Member Posts: 678 Bronze Badge

Hi All.

In our application we are using Java as backend layer.

We have a requirement to allow several operations in a database and changes should be commited only after the user's click on "Save" button.

We obey above requirement by the use of transactionality in DB.

However, we are facing an issues with locks.

When user1 is editing "card1 object" and user2 wants to edit "card2 object" sometimes user2 is even not able to read data from table containing "card objects".

We are not using any "lock table" command.

Is there any limit of number of rows changed in a table that when exceeded results in exclusive table lock?

If yes, is it configurable and may it me increased to some high value to prevent exclusive locks?

If no, what solution do you suggest to prevent locks? I am thinking about involving temporary tables.


Any help is highly welcomed.

Regards,

Jacek

Answers

  • JackK
    JackK Member Posts: 678 Bronze Badge
    edited Oct 14, 2021 10:45AM

    According to Automatic Locks in DML Operations (oracle.com) should be directly

    LOCK TABLE <card_objects_table> IN ROW SHARE MODE?

    share table lock (S) held by one transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE) but allows updates only if a single transaction holds the share table lock.

  • User_H3J7U
    User_H3J7U Member Posts: 494 Bronze Trophy

    Oracle does not escalate a row locks for entire table, only up to table/index block (ITL). The locks do not block a select without for update.

    Holding locks between calls is not common for 3-tier applications due to the migration of sessions with connection pool.

  • Mike Kutz
    Mike Kutz Member Posts: 5,664 Silver Crown

    In Oracle RDBMS: Writers do not block readers. Readers do not block writers.

    Something else is going on.

    I suggest a test. Rewrite that page in APEX and/or test using SQL*Plus.

    If those work, then your application is flawed.

  • JackK
    JackK Member Posts: 678 Bronze Badge
    edited Oct 14, 2021 10:59AM

    Ok, maybe this:

    When user1 is editing "card1 object" and user2 wants to edit "card2 object" sometimes user2 is even not able to read data from table containing "card objects".

    is not caused by exclusive table lock.

    I am seeing that there are situations when INSERT operation is blocked. Is it possible to prevent this by adding LOCK TABLE ... IN ROW SHARE MODE before the INSERT command? (according to the quoted doc.)

    I am not familiar yet with LOCK TABLE command and the situations when it should be used.

  • Mike Kutz
    Mike Kutz Member Posts: 5,664 Silver Crown

    I'm assuming 1 card === 1 row. If users are operating on different rows, there should be no locking issues.

    I can easily UPDATE EMP ... WHERE ID=1234 at the same time someone else UPDATE EMP .. WHERE ID=4321

    But, during edit, you'd want to "lock the row" to prevent it from being edited simultaneously.

    For stateful applications, you can do this with "SELECT .... FOR UPDATE NOWAIT". make sure you catch the error that is thrown when someone else has the lock.

    For stateless applications (eg APEX), you'll need to do things manually. eg calculate current hash value of row and only allow update if the value hasn't changed (which APEX does for you)

    Potential Problem

    There are "bad schema designs" out there that would otherwise prevent "normal DB operations".

    Entity-Attribute-Value (EAV) designs are one.

    Others could include DIY ASSERTIONS. DIY === Do It Yourself. ASSERTION === rules like "each room can handle only X people in it" (technically, all constraints are specialized ASSERTIONs)

    I haven't fully tested, but a stateful application editing a table with a BITMAP Index might cause problems. BITMAP Index implies serialization (to the table) for update,insert,deletes (but, shouldn't bother with selects)

    JackK
  • Mike Kutz
    Mike Kutz Member Posts: 5,664 Silver Crown

    Oh .. so far, you have not described any situation that requires you to alter the default table locking mode.

    At this point in time, I don't see a need for you to run LOCK TABLE ... at all.

  • JackK
    JackK Member Posts: 678 Bronze Badge

    At the moment I have a lock:

    SELECT l.inst_id,DECODE(request,0,'Holder: ','Waiter: ')||l.sid sess,
        id1, id2,
        DECODE(lmode, 0,'none', 1,'null', 2,'row-S (SS)', 3,'row-X (SX)', 4,'share (S)', 5,'S/Row-X (SSX)', 6,'exclusive (X)', lmode) AS lmode,
        DECODE(request, 0,'none', 1,'null', 2,'row-S (SS)', 3,'row-X (SX)', 4,'share (S)', 5,'S/Row-X (SSX)', 6,'exclusive (X)', request) AS request,
        l.type, l.ctime, l.block,
        s.USERNAME,s.osuser,s.PROGRAM,s.MACHINE,
        s.serial#,
        a.SQL_TEXT
     FROM sys.GV_$LOCK l
     join sys.gv_$session s on l.sid=s.SID
     left join sys.gv_$sqlarea a on a.ADDRESS = s.SQL_ADDRESS
     where username like '%OMSW%'
     and (id1, id2, l.type) IN (SELECT id1, id2, type FROM sys.GV_$LOCK WHERE request>0)
     ORDER BY 2,id1, request;
    

    My session in waiting to do DELETE.

    Can someone explain me why?

  • User_H3J7U
    User_H3J7U Member Posts: 494 Bronze Trophy

    Can someone explain me why?

    Delete is waiting for REQUEST='S/Row-X (SSX)'. Probably row locked through fk.

    Jonathan Lewis
  • User_H3J7U
    User_H3J7U Member Posts: 494 Bronze Trophy
    create table t1(n1 number primary key, n2 number references t1(n1));
    
    1: 289> select * from t1 where n1=123 for update;
            no rows selected
    2: 269> delete t1 where n1=987;
            ...waiting...
    
    select session_id, name, mode_held, mode_requested from dba_dml_locks;
    
    SESSION_ID NAME  MODE_HELD     MODE_REQUESTE
    ---------- ----- ------------- -------------
           269 T1    None          S/Row-X (SSX)
           289 T1    Row-X (SX)    None         
    
    


    Jonathan Lewis
  • Paulzip
    Paulzip Member Posts: 8,458 Blue Diamond
    edited Oct 15, 2021 8:54AM

    Sounds like the side effect of an unindexed foreign key...

    JackK