This discussion is archived
1 2 3 4 Previous Next 52 Replies Latest reply: Jan 16, 2013 9:02 AM by DenisKishenko RSS

Double check and locking or conditional insert and multi-threading

DenisKishenko Newbie
Currently Being Moderated
Hi all

I have plsql procedure which executes conditional insert like

if <table A doesn't contain row R> then
insert <row R into table A>
end if;

but simultaneous calls do multiple inserts sometimes. In other programming languages problem can be fixed with [double check and lock|http://wikipedia.org/wiki/Double_checked_locking] but plsql has no "synchronized" block.

Thoughts?

Edited by: Denis Kishenko on Jan 14, 2013 12:48 AM

Edited by: Denis Kishenko on Jan 14, 2013 12:52 AM
  • 1. Re: Double check and locking or conditional insert and multi-threading
    BluShadow Guru Moderator
    Currently Being Moderated
    Denis Kishenko wrote:
    Hi all

    I have plsql procedure which executes conditional insert like

    if <condition> then
    insert ,,,
    end if;

    but simultaneous calls do multiple inserts sometimes. In other programming languages problem can be fixed with [double check and lock|http://ru.wikipedia.org/wiki/Double_checked_locking] but plsql has no "synchronized" block.

    Thoughts?
    So what's the problem exactly (I can't read russian)

    Yes, multiple sessions can be calling the same procedure and attempting to do inserts to the same table... and the database will handle the locking, that's what it's designed to do.

    Please be clear in explaining what problem you are experiencing.

    {message:id=9360002}
  • 2. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Sorry, url and example fixed =)

    Due to multi-threading two sessions can pass IF condition and insert row R twice
  • 3. Re: Double check and locking or conditional insert and multi-threading
    ascheffer Expert
    Currently Being Moderated
    Ever thought on creating a unique key on your table?
  • 4. Re: Double check and locking or conditional insert and multi-threading
    Purvesh K Guru
    Currently Being Moderated
    Denis Kishenko wrote:
    Hi all

    I have plsql procedure which executes conditional insert like

    if <table A doesn't contain row R> then
    insert <row R into table A>
    end if;

    but simultaneous calls do multiple inserts sometimes. In other programming languages problem can be fixed with [double check and lock|http://wikipedia.org/wiki/Double_checked_locking] but plsql has no "synchronized" block.

    Thoughts?
    In Oracle Inserts, do not block other session from perform another Insert Operation, unless in a poorly built database architecture, Two sessions attempt to insert same value in a column with Unique index and both not committing, you have a situation of one Session Insert blocking Other session Insert.

    Read a demonstration by Hemant Chitale - Inserts holding Locks ? (Click the Case Study link for detailed steps)
  • 5. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Unfortunately rows are not unique. Let me provide more details

    Some configuration descriptions "name;key1=value1;key2=value2;..." are stored in two tables

    CREATE TABLE "CONFIG"
    (     "ID" NUMBER NOT NULL ENABLE,
         "NAME" VARCHAR2(255),
         "HASH" NUMBER,
         CONSTRAINT "CONFIG_PK" PRIMARY KEY ("ID") ENABLE
    )

    CREATE TABLE "PROPERTY"
    (     "CONFIG" NUMBER NOT NULL ENABLE,
         "KEY" VARCHAR2(255) NOT NULL ENABLE,
         "VALUE" VARCHAR2(255)
    )

    Configurations are equal only if their full descriptions (but not only names) are equal. Column HASH allows improve search performance but doesn't guarantee unique value. Before add new configuration I check it doesn't exist that's why conditional insert was used.

    How can I optimize architecture to avoid conditional insert?

    Edited by: Denis Kishenko on Jan 14, 2013 2:11 AM

    Edited by: Denis Kishenko on Jan 14, 2013 2:14 AM
  • 6. Re: Double check and locking or conditional insert and multi-threading
    BluShadow Guru Moderator
    Currently Being Moderated
    Denis Kishenko wrote:
    Unfortunately rows are not unique. Let me provide more details

    Some configuration descriptions "name;key1=value1;key2=value2;..." are stored in two tables

    CREATE TABLE "CONFIG"
    (     "ID" NUMBER NOT NULL ENABLE,
         "NAME" VARCHAR2(255),
         "HASH" NUMBER,
         CONSTRAINT "CONFIG_PK" PRIMARY KEY ("ID") ENABLE
    )

    CREATE TABLE "PROPERTY"
    (     "CONFIG" NUMBER NOT NULL ENABLE,
         "KEY" VARCHAR2(255) NOT NULL ENABLE,
         "VALUE" VARCHAR2(255)
    )

    Configurations are equal only if their full descriptions (but not only names) are equal. Column HASH allows improve search performance but doesn't guarantee unique value. Before add new configuration I check it doesn't exist that's why conditional insert was used.

    How can I optimize architecture to avoid conditional insert?
    It doesn't make sense.

    You say you want to implement a mechanism to prevent you inserting the same thing more than once, but then you say that the rows aren't necessarily unique... in which case you want to allow duplicates.

    So do you want to allow duplicates or do you want to prevent duplicates? If you want to prevent duplicates you apply a unique key contraint/primary key/index as necessary and then only one of the sessions will be able to successfully insert and the other session can capture the exception that it has a duplicate key.
    If you want to allow duplicates, then you don't have an issue.
  • 7. Re: Double check and locking or conditional insert and multi-threading
    ascheffer Expert
    Currently Being Moderated
    If rows are not unique, how can you check if a row exists?
  • 8. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Original example was simplified. Code below shows real case but I removed some unnecessary business logic

    <pre>
    varchar2 l_testconfig := <normalized configuration "name;key1=value1;key2=value2;....">
    number l_testconfig_hash := <calculate configuration hash>
    BEGIN

    WITH
    testconfig AS (
    SELECT id, name||';'||properties as text FROM config
    LEFT JOIN ( -- left joint used because of configuration may don't have key/value but just name
    SELECT
    c.id config_id,
    LISTAGG(key||'='||value,';') WITHIN GROUP (ORDER BY upper(key)) properties
    FROM config c, property p WHERE p.config = c.id GROUP BY c.id
    ) ON id = config_id
    WHERE id IN (SELECT id FROM config WHERE hash = l_testconfig_hash)
    )
    SELECT id INTO l_testconfig_id FROM testconfig WHERE text = l_testconfig;

    -- configuration exists

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    -- configuration doesn't exist
    insert config ...
    insert property ...
    </pre>

    Edited by: Denis Kishenko on Jan 14, 2013 2:50 AM
  • 9. Re: Double check and locking or conditional insert and multi-threading
    BluShadow Guru Moderator
    Currently Being Moderated
    I'm still not sure what the actual problem is... and still don't know if you want to allow duplicates or prevent duplicates.

    As for the conditional insert statement, that can be achieved more simply using a single MERGE statement.
  • 10. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    I want to avoid duplicates of "name;key1=value2;..."
  • 11. Re: Double check and locking or conditional insert and multi-threading
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Denis Kishenko wrote:

    I have plsql procedure which executes conditional insert like

    if <table A doesn't contain row R> then
    insert <row R into table A>
    end if;

    but simultaneous calls do multiple inserts sometimes. In other programming languages problem can be fixed with [double check and lock|http://wikipedia.org/wiki/Double_checked_locking] but plsql has no "synchronized" block.

    Thoughts?
    Won't work in Oracle. And it not a PL/SQL language or synchronised block issue.

    The 2nd insert is attempting to insert a duplicate - as determined by the primary key/unique index. It can only succeed with its insert, should the 1st insert rollback. And as the 1st insert is first, the 2nd insert has to wait on the 1st insert to either decide to commit (2nd insert fails) or rollback (2nd insert succeeds).

    This is logical and is how database locking, serialisation and concurrency are all suppose to work.

    The real question is why do you need to subvert it? Why should the 2nd insert for example succeed, failing the 1st insert?

    There's an easy way to subvert it. Simply remove the primary key/unique index from the table - and Bob's not only your uncle, but Dr Corruption is messing with your data.
  • 12. Re: Double check and locking or conditional insert and multi-threading
    ascheffer Expert
    Currently Being Moderated
    Looks like a very strange design to me.
    Are you only storing the configurations and not retireving them again?
    How do you retrieve a configuation without knowing the values stored in them?
    And if you know the values of a configuration, why do you want to retrieve that configuration?
  • 13. Re: Double check and locking or conditional insert and multi-threading
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Denis Kishenko wrote:

    In other programming languages problem can be fixed with [double check and lock|http://wikipedia.org/wiki/Double_checked_locking] but plsql has no "synchronized" block.
    One can synchronise PL/SQL code - and assuming this code is solely running specific SQL, doing specific DML, then that SQL is also synchronise. Basic example posted in thread {message:id=9388540}.
  • 14. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Denis Kishenko wrote:
    Unfortunately rows are not unique.

    CREATE TABLE "CONFIG"
    (     "ID" NUMBER NOT NULL ENABLE,
         "NAME" VARCHAR2(255),
         "HASH" NUMBER,
         CONSTRAINT "CONFIG_PK" PRIMARY KEY ("ID") ENABLE
    )

    CREATE TABLE "PROPERTY"
    (     "CONFIG" NUMBER NOT NULL ENABLE,
         "KEY" VARCHAR2(255) NOT NULL ENABLE,
         "VALUE" VARCHAR2(255)
    )

    Configurations are equal only if their full descriptions (but not only names) are equal. Column HASH allows improve search performance but doesn't guarantee unique value. Before add new configuration I check it doesn't exist that's why conditional insert was used.

    How can I optimize architecture to avoid conditional insert?
    First, let me make sure I understand.
    - When you say two "Configs" are equal, do you mean they have the same name and the same properties?
    - Does the HASH cover the name and all the properties?

    Second, let me suggest:
    - rename CONFIG.ID to CONFIG_ID
    - rename PROPERTY.CONFIG to CONFIG_ID
    - create a unique constraint on CONFIG.NAME
    - create a foreign key constraint from PROPERTY.CONFIG_ID to CONFIG.CONFIG_ID
    - create a primary key on PROPERTY(CONFIG_ID, KEY)

    Third, let me suggest that you rethink your data model to make it relational. PROPERTY has an "entity/attribute/value" design, which is not relational and which creates problems of data integrity (as you are seeing) and performance.

    Lastly, PL/SQL provides the DBMS_LOCK package. If you insist on keeping this data model, just serialize at the HASH level and you will avoid the duplicates.

    P.S. The example referred to by Billy will serialize at the procedure level. You only need to serialize at the HASH level.

    Edited by: Stew Ashton on Jan 14, 2013 12:10 PM
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points