Forum Stats

  • 3,816,058 Users
  • 2,259,135 Discussions
  • 7,893,375 Comments

Discussions

oracle nosql db has no row based lock?

Eric Jing
Eric Jing Member Posts: 24 Green Ribbon

Dear sir,

 

Suppose below is the team table:

CREATE TABLE if not exists teamTable

(

    teamid integer GENERATED BY DEFAULT AS IDENTITY

(START WITH 1 INCREMENT BY 1),

    users array(record(name string, userid integer)),

primary key (teamid)

)

 

And suppose there are 2 db requests inserting new users into above table for the same team (same teamid) at the same time for new employees join the company. Does it mean that query 2 cannot been done only until query 1 is finished? If the nosql db is functioning as what I mentioned above, it will reduce it’s write performance if a lot of query want to write/insert/update some data into the same array element at the same time (which below to the same row with the same primary key), and the same applies to the “map”.

So, regarding this, does oracle nosql db has a row lock mechanism similar to oracle database? Or, oracle nosql db does not has a row lock kind of this to improve performance? Thank you.

Comments

  • Eric Jing
    Eric Jing Member Posts: 24 Green Ribbon

    I think oracel nosql only lock the piece of data that the current query is editing with, instead of locking the entire db row to improve the performane. Am i right?

    For nosql db, there are some complicated "big" table which has a lot of nested elements defined for the table (e.g array of array of record), so there is a very high possibility that multiple threads is editing the same db row at the same time.

  • Gmfeinberg-Oracle
    Gmfeinberg-Oracle Posts: 1,333 Employee

    Hi Eric,

    I just answered this, or a similar question. I think you are wondering how to handle concurrent updates of the same row. I don't know which language you are using so I'll use pseudo code that looks like our Java direct driver API. All languages support this feature.

    If updating a row using the API put() operation you need to do a read-modify-write in your application. The API provides a way for you to know if there was a competing update that happened between your read and write:

    Row row = handle.get(key);

    /* save the Version */

    Version = row.getVersion();

    /* modify the row as you need */

    if (handle.putIfVersion(row, version, null, null)) == null) {

    /* if putIfVersion returns null then your operation failed because the actual version didn't match the original, meaning another updater modified the row */

    }

    If your putIfVersion() fails you need to either loop back and re-apply your change or take other action. Also note that putIfVersion() has an option to return the existing (modified) row if it fails so you don't need to re-read (get()) the row. I didn't add that logic into the example.

  • intelcom tracker
    intelcom tracker Member Posts: 1 Green Ribbon
    edited Dec 8, 2021 4:04PM

    Maybe the recent update improves performance by reducing contention on row-based locks, which are used to protect data that is updated.

    For example, if a document with an identity column was updated by one user, another user trying to update the same document would block until the first user finished updating it. This can cause contention on row-based locks and affect performance.

    Now, the new Oracle NOSQLDB release has made some changes to avoid this problem. The new code minimizes the number of times that row-based locks are used, which improves performance and reduces contention on these locks.

  • Gmfeinberg-Oracle
    Gmfeinberg-Oracle Posts: 1,333 Employee

    There appears to be a misconception in a few posts in this thread regarding concurrency and locking in Oracle NoSQL Database.

    In Oracle NoSQL Database locking is at the row level, but what does this mean for concurrent updates?

    Insertion of multiple rows in the same table proceeds in parallel with no significant blocking. This is because they are independent rows.

    If 2 requests update the same row concurrently they will happen independently but the resulting row value will be based on the request that was serviced last. My post earlier in this thread describes the mechanism that can be used by applications to ensure a read-modify-write cycle that may depend on existing values can proceed safely.

    If an update is performed via a SQL query that performs a read-modify-write in the query statement that update is atomic - the read, predicate evaluation, and update all operate in the same transaction.

    The summary is that the concurrency mechanisms in Oracle NoSQL Database are such that unindexed row insertions or updates can occur with single-digit millisecond latency, regardless of whether the rows affected are unique or the same row.

    I hope this helps clarify,

    Regards,

    George