This discussion is archived
1 2 3 4 Previous Next 52 Replies Latest reply: Jan 16, 2013 9:02 AM by DenisKishenko Go to original post RSS
  • 45. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Bawer wrote:
    ... the query of op shows that he needs id of config to select the key+val combinations from property
    ...
    won't work because of there is no index on l_name
    The OP needs to do three things:
    1) serialize
    2) check for duplicates
    3) do the inserts

    Those are separate tasks. I showed the code for 1), not for 2) or 3)
    no no, exactly 1 wont work. you can't lock a row if your query doesn't find any row.

    >
    To check for duplicates, the OP needs to get all the CONFIG rows with NAME = l_name. Yes, an index on NAME would be advisable.
    He can JOIN to the PROPERTY table on CONFIG_ID.
    and your alternative
    ...
    won't work.
    scenario: first session inserts data and not yet finished.
    second sessions insert same data too because of first session has not committed yet.
    That is not what Oracle does. The second session's insert will wait to see if the first session commits or rolls back.
    Wrong
    second session won't wait, if it can't find any committed data with given names (without unique index).

    Edited by: Bawer on 15.01.2013 11:41
  • 46. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Bawer wrote:
    Stew Ashton wrote:
    Bawer wrote:
    scenario: first session inserts data and not yet finished.
    second sessions insert same data too because of first session has not committed yet.
    That is not what Oracle does. The second session's insert will wait to see if the first session commits or rolls back.
    Wrong
    second session won't wait, if it can't find any committed data with given names (without unique index).
    Ah, you said "without unique index".

    Maybe you didn't notice, but I created a table called CONFIG_NAME with NAME as the primary key.

    I am doing the INSERT and/or SELECT FOR UPDATE on the CONFIG_NAME table, not the OP's CONFIG table.

    When I said the second session's insert will wait on the first session, I was referring to the insert to the CONFIG_NAME table, which I use for serialization only.
  • 47. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Bawer wrote:
    Stew Ashton wrote:
    Bawer wrote:
    scenario: first session inserts data and not yet finished.
    second sessions insert same data too because of first session has not committed yet.
    That is not what Oracle does. The second session's insert will wait to see if the first session commits or rolls back.
    Wrong
    second session won't wait, if it can't find any committed data with given names (without unique index).
    Ah, you said "without unique index".

    Maybe you didn't notice, but I created a table called CONFIG_NAME with NAME as the primary key.
    Yes, I didn't. this violates your explanation about op's question (multiple names)
    How do you want to allow duplicated names through primary key on name?

    Edited by: Bawer on 15.01.2013 12:49
    I missed your config_name definition.
  • 48. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Stew Ashton first time I didn't catch your idea. As I see table name "config_name" confused not only me =)

    I have implemented your suggestion with minor modifications and it worls fine! I am experimenting in APEX so some DDL command was added automatically. Stew do I understand your idea correctly?

    <pre>
    CREATE TABLE "CONFIG_LOCK"
    (     "LOCKER" NUMBER,
         PRIMARY KEY ("LOCKER") ENABLE
    ) ORGANIZATION INDEX NOCOMPRESS;

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

    CREATE TABLE "PROPERTY"
    (     "CONFIG_ID" NUMBER,
         "KEY" VARCHAR2(255),
         "VALUE" VARCHAR2(255)
    );

    -- Used single key and value to simplify example but really there are unlimited set of pairs
    create or replace PROCEDURE add_config(p_name varchar2, p_key varchar2, p_value varchar2)
    AS
    l_config_normalized VARCHAR(255);
    l_config_id NUMBER;
    l_config_hash NUMBER;
    l_locker NUMBER;
    BEGIN

    -- Multiple sessions can be here

    l_config_normalized := p_name||';'||p_key||'='||p_value;
    l_config_hash := 123; -- Use same hash value for demo to provoke huge amount of conflicts but really it must be ora_hash(l_config_normalized);

    -- Lock procedure for single config and for all sessions
    l_locker := l_config_hash; -- Value of l_config_normalized also can be used as locker
    BEGIN
    INSERT INTO CONFIG_LOCK VALUES (l_locker);
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    SELECT locker INTO l_locker FROM config_lock WHERE locker = L_locker FOR UPDATE;
    END;

    -- Only one session can be here at any period of time. Following code is absolutely thread-safe. It isn't required to have immutable config it can be changed through similar procedures which use same locking aproach

    BEGIN
    WITH normalized_config AS (
    SELECT config_id, c.name||';'||p.key||'='||p.value AS text FROM config c, property p
    WHERE p.config_id = c.id AND c.hash = l_config_hash
    )
    SELECT config_id INTO l_config_id FROM normalized_config WHERE text = l_config_normalized;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    INSERT INTO config (name, hash) VALUES (p_name, l_config_hash) RETURNING id INTO l_config_id;
    INSERT INTO property VALUES (l_config_id, p_key, p_value);
    END;

    -- Unlock config
    DELETE FROM config_lock WHERE locker = l_locker;
    END;‚Äč

    Run two threads with

    begin
    for i in 1..5000 loop
    add_config('name'||i, 'key'||i, 'value'||i);
    end loop;
    end;

    34.09 seconds

    select count(distinct name) from config
    5000
    </pre>
  • 49. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    It looks OK to me. Using the hash will "over-serialize" less than using the name, so it's probably worth the trouble. A few details:

    1) l_config_normalized VARCHAR(255); should probably be longer, don't you think?

    2) You need a big string for your hash, but when comparing to the actual table you could just compare values directly.

    3) I assume there is a trigger on the insert of CONFIG to populate ID from a sequence? You could actually do that in the code and it would perform a bit better.

    4) In real life, someone is going to commit, right? Since only the commit will actually free the hash for the next session.
  • 50. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Stew
    It looks OK to me. Using the hash will "over-serialize" less than using the name, so it's probably worth the trouble. A few details:
    Which troubles?
    1) l_config_normalized VARCHAR(255); should probably be longer, don't you think?
    Sure, it was just a demo
    2) You need a big string for your hash, but when comparing to the actual table you could just compare values directly.
    Right
    3) I assume there is a trigger on the insert of CONFIG to populate ID from a sequence? You could actually do that in the code and it would perform a bit better.
    Yes trigger exists and foreign key also, just skipped these details to focus on task
    4) In real life, someone is going to commit, right? Since only the commit will actually free the hash for the next session.
    What problem are you talking about?
  • 51. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Denis Kishenko wrote:
    It looks OK to me. Using the hash will "over-serialize" less than using the name, so it's probably worth the trouble. A few details:
    Which troubles?
    I just meant if you serialized simply on NAME you wouldn't have to create the big string and hash it. Serializing on the hash is fine, go ahead.
    4) In real life, someone is going to commit, right? Since only the commit will actually free the hash for the next session.
    What problem are you talking about?
    In your demo, I don't see the COMMIT. Whether you DELETE or not, you have to COMMIT to free that hash for the next session.

    Did you test in two concurrent sessions?

    If you want to serialize on a hash without committing, you can use DBMS_LOCK and release the lock explicitly.
  • 52. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    I just meant if you serialized simply on NAME you wouldn't have to create the big string and hash it. Serializing on the hash is fine, go ahead.
    Agree just name will be faster
    In your demo, I don't see the COMMIT. Whether you DELETE or not, you have to COMMIT to free that hash for the next session.
    Ops, forgot
    Did you test in two concurrent sessions?
    Re-tested and added thread id column to config to check job distribution

    <pre>
    declare
    ln_dummy number;
    begin
    DBMS_JOB.SUBMIT(ln_dummy, 'for i in 1..5000 loop add_config(''name''||i, ''key''||i, ''value''||i, 1); end loop;');
    DBMS_JOB.SUBMIT(ln_dummy, 'for i in 1..5000 loop add_config(''name''||i, ''key''||i, ''value''||i, 2); end loop;');
    COMMIT;
    end;

    select count(distinct name) from config;
    5000

    select thread, count(*) from config group by thread;
    THREAD     COUNT(*)
    1     4566
    2     434
    </pre>
    If you want to serialize on a hash without committing, you can use DBMS_LOCK and release the lock explicitly.
    dmbs_lock dosn;t have "wait-unlock" procedure so I have to create own one
1 2 3 4 Previous Next

Legend

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