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
  • 30. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Bawer
    you have no problem, just insert
    Before insert you have to check there isn't such config. So we have two statements CHECK and INSERT where CHECK can be implemented in different ways - if, catch exception on something else. Since these two operations are not atomic it isn't thread-safe.

    ascheffer
    Checked merge on practice... merge is not thread-safe =(

    Example

    <pre>
    CREATE TABLE "TEMP"
    (     "VALUE" NUMBER
    )

    Run two threads

    begin
    for i in 1..10000 loop
    merge into temp x
    using (select count(*) + 1 value from temp) y
    on (x.value = y.value)
    when not matched then insert (value) values (y.value);
    end loop;
    end;

    select count(*) from temp
    20000 expected

    select count(distinct value) from temp
    18087 unexpected
    </pre>

    Edited by: Denis Kishenko on Jan 14, 2013 11:36 PM
  • 31. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Denis Kishenko wrote:
    Bawer
    you have no problem, just insert
    Before insert you have to check there isn't such config. So we have two statements CHECK and INSERT where CHECK can be implemented in different ways - if, catch exception on something else. Since these two operations are not atomic it isn't thread-safe.
    really? look at this code:
      --step 1, first insert, it will lock another sessions in this place, if it can insert.
      --if duplicate val exception occurs, just ignore and open with for update
      begin
        insert into config (id,name,hash) values ( x,y,z);
      exception
        when DUP_VAL_ON_INDEX then null;
      end;
    
      --step 2, open it with for update, this will lock another sessions in this place if insert fails.
      for i in (select * from config where id = x for update ) loop
          for j in (
                SELECT
                    p.config config_id,
                    LISTAGG(key||'='||value,';') WITHIN GROUP (ORDER BY upper(key)) properties
                FROM property p WHERE p.config = i.id GROUP BY p.config
          )
          loop
                  ...
    
    --commit or rollback
    this is not atomic but thread-safe.
  • 32. Re: Double check and locking or conditional insert and multi-threading
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Denis Kishenko wrote:

    Checked merge on practice... merge is not thread-safe =(
    It is - when used correctly on a properly designed data structure.

    Fact - synchronisation requires serialisation. Your example does not serialise. Obviously it will not work correctly and fail thread safety.

    How does synchronisation/serialisation work with Oracle data? Pessimistic locking. A row lock ensures that this row's data can be changed by a single process at a time.

    However, serialisation kills performance in a multi-user and multi-processing system. So a row lock does not prevent that row from being read - with that read seeing the existing committed version of that row, and not doing a dirty read and seeing the uncommitted changes of that row.

    If you used a proper data structure as table, e.g.
    create table testtab(
            value           number primary key
    ) organization index;
    And then run that merge as 2 separate sessions/threads at the same time - one would have inserted data. The other would have waited (as it uses identical keys). When the first thread commits, the second thread fails - as it is attempting to insert duplicate data.

    The 1st thread does not see the 2nd thread's uncommitted data, and vice versa. The two threads cannot coordinate which keys which thread gets to use as dirty reads is not allowed - and fundamentally flawed.

    And this concept has been explained to you a number times in this thread. Do you grasp and understand this? If not, then further conversation is useless.
  • 33. Re: Double check and locking or conditional insert and multi-threading
    BluShadow Guru Moderator
    Currently Being Moderated
    Billy  Verreynne  wrote:
    And this concept has been explained to you a number times in this thread. Do you grasp and understand this? If not, then further conversation is useless.
    which is one of the reasons I just stepped back out of the conversation.
    The concept of "I don't want duplicates" should simply require a unique constraint (whether primary key, or index etc.), and using a merge makes the check and inserts atomic. Maybe I missed something, but the only problem I'm seeing, like yoursef Billy, is that the concept of database locking is not being understood by the OP.
  • 34. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Of course insert by itself is "check+insert" but your solution is correct if
    - name and hash pair is unique but according original task they are not unique otherwise there is no problem =)
    - config is fully immutable - no deletion, no updates otherwise after "insert" and before "for" you can get a collision. This restriction follows original task so I just pay attention at this point.
  • 35. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    I think bawer is on the right track here, but it's not perfect.

    We should agree on what our words mean. "Atomic" means "all or nothing": when you change the data, either everything gets changed or nothing gets changed. Every statement in Oracle is atomic, and every transaction is atomic. That is not specific to your problem.

    When you talk about "double-check and lock", you mean "serialize". Whenever you insert a configuration set (meaning a CONFIG row and its PROPERTY rows), you want to make sure nobody else is inserting the same configuration. To do that, you need to make sure that only one session at a time can work on the same configuration set. That is what we mean by "serialization".

    Now, the trick is you don't need to serialize on the configuration set as a whole! You could serialize the procedure itself using DBMS_LOCK: that would not be very scalable, but it would be "safe". You could serialize on a hash that represents the configuration set: even if there were a collision between two configuration sets with the same hash, that would only mean one session waits on the other one.

    I suggest you serialize on the configuration name. That will "over-serialize" a bit, but again it will be safe and it should scale OK unless you have thousands of configuration sets with the same name.

    First, create a table that contains just the configuration name:
    CREATE TABLE CONFIG_NAME (
      NAME VARCHAR2(255) PRIMARY KEY
    ) ORGANIZATION INDEX;
    Now, this code should serialize on that name:
    DECLARE
      l_name config.name%type := 'Config 1';
    BEGIN
      begin
        INSERT INTO CONFIG_NAME VALUES (L_NAME);
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        SELECT name into l_name FROM CONFIG_NAME WHERE NAME = L_NAME FOR UPDATE;
      END;
      -- check for duplicate values
      -- do inserts, merges, whatever you want
    END;
    /
    This alternative would work too:
    DECLARE
      l_name config.name%type := 'Config 2';
    BEGIN
      BEGIN
        SELECT NAME INTO L_NAME FROM CONFIG_NAME WHERE NAME = L_NAME FOR UPDATE;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO CONFIG_NAME VALUES (L_NAME);
      END;
      -- check for duplicate values
      -- do inserts, merges, whatever you want
    END;
    /
    Any other session that wants to use the same configuration name will have to wait until you commit or roll back.

    This solution is based on Tom Kyte's solution for reserving a room for a meeting. Here, I "reserve" a configuration name for a configuration set.

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:42171194352295

    Edited by: Stew Ashton on Jan 15, 2013 10:01 AM
  • 36. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Denis Kishenko wrote:
    Of course insert by itself is "check+insert" but your solution is correct if
    - name and hash pair is unique but according original task they are not unique otherwise there is no problem =)
    why do you want to eliminate duplicates by yourself, although oracle can do it already for you? this seems like a typically developer issue who really don't understand how locking works.
    ..otherwise after "insert" and before "for" you can get a collision.
    of course, you should do nothing before you get locking.

    It is already a poor design to change same rows by different sessions at same time.
  • 37. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Bawer,

    Denis may not understand how Oracle handles uniqueness, but I think he has a real problem in addition. If we go back to his original question, he says that CONFIG.NAME is not unique, it's only the combination of name + keys + values that is unique.

    Oracle doesn't handle by itself unique combinations of rows. That is why I suggest serializing on the name, so he can check for duplicates manually while ensuring that no one else is using the same name.
  • 38. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    Stew great thanks for your answer! I will investigate serializable isolation level it should help I think

    It is so strange that many people is tying to change task instead of solve it of course unique name makes this task trivial =)
  • 39. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Bawer,

    Denis may not understand how Oracle handles uniqueness, but I think he has a real problem in addition. If we go back to his original question, he says that CONFIG.NAME is not unique, it's only the combination of name + keys + values that is unique.
    but I assumed along the ID is defined as primary key in config table (already unique, it is just a different table) or am I wrong?

    key+val combinations is stored in property table, which can be checked after locking of config table. This provides a wait event for another sessions in first step so that he can check key+val combinations (as synchronized). if there is no row with same name(I assume every ID has different name) in config, he can first add a row and than can lock it.

    Edited by: Bawer on 15.01.2013 10:34
  • 40. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Bawer wrote:
    but I assumed along the ID is defined as primary key in config table (already unique, it is just a different table) or am I wrong?

    key+val combinations is stored in property table, which can be checked after locking of config table. This provides a wait event for another sessions in first step so that he can check key+val combinations (as synchronized). if there is no row with same name in config, he can first add a row and than can lock it.
    As I understand it, the ID is unique but the name isn't.

    If you lock the CONFIG row on ID, that doesn't stop someone else using the same combination of name + set of key/value pairs.
  • 41. Re: Double check and locking or conditional insert and multi-threading
    DenisKishenko Newbie
    Currently Being Moderated
    As I understand it, the ID is unique but the name isn't.
    If you lock the CONFIG row on ID, that doesn't stop someone else using the same combination of name + set of key/value pairs.
    Absolutely correct
  • 42. Re: Double check and locking or conditional insert and multi-threading
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    As I understand it, the ID is unique but the name isn't.
    maybe it is so, but the query of op shows that he needs id of config to select the key+val combinations from property

    according to your logic, your code
    DECLARE
      l_name config.name%type := 'Config 1';
    BEGIN
      begin
        INSERT INTO CONFIG_NAME VALUES (L_NAME);
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        SELECT name into l_name FROM CONFIG_NAME WHERE NAME = L_NAME FOR UPDATE;
      END;
      -- check for duplicate values
      -- do inserts, merges, whatever you want
    END;
    won't work because of there is no index on l_name

    and your alternative
    DECLARE
      l_name config.name%type := 'Config 2';
    BEGIN
      BEGIN
        SELECT NAME INTO L_NAME FROM CONFIG_NAME WHERE NAME = L_NAME FOR UPDATE;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO CONFIG_NAME VALUES (L_NAME);
      END;
      -- check for duplicate values
      -- do inserts, merges, whatever you want
    END;
    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.
    what happens? (result: same data can be inserted twice)
  • 43. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    Denis Kishenko wrote:
    ... I will investigate serializable isolation level it should help I think
    When you say "serializable isolation level", are you referring to the official documentation?

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT1320

    That is not the kind of "serializable" I am talking about.

    You do not want to say SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    That statement changes the way "read consistency" works, which is not your problem.
    It is so strange that many people is tying to change task instead of solve it
    Well, at one point you tried to simplify your question and I think some people answered the simplified question and not the original question.
  • 44. Re: Double check and locking or conditional insert and multi-threading
    Stew Ashton Expert
    Currently Being Moderated
    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)

    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.
    If session 1 commits, session 2 will get DUP_VAL_ON_INDEX.
    If session 1 rolls back, session 2 will do the insert.

Legend

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