1 2 3 4 Previous Next 52 Replies Latest reply: Jan 16, 2013 9:02 AM by Denis Kishenko-Oracle Go to original post RSS
      • 15. Re: Double check and locking or conditional insert and multi-threading
        Denis Kishenko-Oracle
        Looks like a very strange design to me.
        Do you have an idea how it should be?
        Are you only storing the configurations and not retireving them again?
        Of course I am retrieving them.
        How do you retrieve a configuation without knowing the values stored in them?
        I don't need values I have configuration ID (column config.ID) and set of key/value associated with this ID (column property.config). Configuration linked with other table by its ID.
        And if you know the values of a configuration, why do you want to retrieve that configuration?
        For example to find out all configurations with specified key and value

        Have you still found something strange? IMHO its pretty simple structure and task but may be I missed something in me explanation =)
        • 16. Re: Double check and locking or conditional insert and multi-threading
          Denis Kishenko-Oracle
          Hi Stew thanks fro detailed answer and hints!
          - When you say two "Configs" are equal, do you mean they have the same name and the same properties?
          Exactly
          - Does the HASH cover the name and all the properties?
          Yes
          - create a unique constraint on CONFIG.NAME
          Unfortunately can't, see first question
          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.
          What exactly do you suggest?
          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.
          Sure, I have been investigation this already but what does mean "at the HASH level"?
          • 17. Re: Double check and locking or conditional insert and multi-threading
            ascheffer
            You missed (probably) the part that a config name is unique
            You could be missing a part the config name, key combination is unique
            In your logic all these "configurations" are different and stored
            "test:key1=1"
            "test:key1=2"
            "test:key1=3;key2=1"
            "test:key1=1;key1=2"
            In your logic you could be missing a part about changing a key value.
            But then again, you might be keeping some more "irrelevant" details for yourself
            • 18. Re: Double check and locking or conditional insert and multi-threading
              Denis Kishenko-Oracle
              ascheffer     thanks for questions
              You missed (probably) the part that a config name is unique
              Config names are NOT unique otherwise there are no any problem =)
              You could be missing a part the config name, key combination is unique
              Combination of name+keys+values is unique but just name+keys
              In your logic all these "configurations" are different and stored
              "test:key1=1"
              "test:key1=2"
              "test:key1=3;key2=1"
              "test:key1=1;key1=2"
              Yes, they are different
              In your logic you could be missing a part about changing a key value.
              Name, keys and values are immutable
              • 19. Re: Double check and locking or conditional insert and multi-threading
                ascheffer
                Ok, in this case I would say the logic would be

                Get a lock, use the locking procedure from the link from Billy for instance
                Check for duplicates, (avoid the hash, it's not unique and probably cost to much time to calculate)
                Do an insert if necessary
                Release the lock
                • 20. Re: Double check and locking or conditional insert and multi-threading
                  Denis Kishenko-Oracle
                  Guy lets simplify case. One table with unique column. Task : Add row if it doesn't exists.

                  Both methods are not thread safe IMHO

                  <pre>
                  BEGIN
                  INSERT INTO config (name) VALUES (l_config_name);
                  EXCEPTION
                  WHEN DUP_VAL_ON_INDEX THEN
                  -- at this moment row ca be deleted by another thread
                  SELECT id INTO l_testconfig_id FROM config WHERE name = l_config_name;
                  END;
                  </pre>

                  <pre>
                  BEGIN
                  SELECT id INTO l_testconfig_id FROM config WHERE name = l_config_name;
                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                  -- at this moment row ca be added by another thread
                  INSERT INTO config (name) VALUES (l_config_name);
                  END;
                  </pre>
                  • 21. Re: Double check and locking or conditional insert and multi-threading
                    BluShadow
                    Use a MERGE...

                    e.g.
                    merge into config x
                    using (select name from config where name = l_config_name) y
                    on (x.name = y.name)
                    when not matched then
                      insert (name) values (l_config_name);
                    (untested)
                    • 22. Re: Double check and locking or conditional insert and multi-threading
                      Stew Ashton
                      Denis Kishenko wrote:
                      Guy lets simplify case. One table with unique column. Task : Add row if it doesn't exists.
                      You need to understand how Oracle handles concurrency.

                      If you put a unique constraint on CONFIG.NAME, that constraint will be enforced by Oracle with the help of an index. When you try to insert the row, Oracle will check the index to see if the value is already in the index.

                      If the value exists as committed data, Oracle will return an error.

                      If the value exists as uncommitted data, then another session has to commit or roll back that insert. In the meantime, you will wait. If the other session commits, you get an error. If the other session rolls back, you will succeed!

                      The reason why you have a specific problem is that your "uniqueness" covers more than one row.

                      [Edit: removed mention of locks!]

                      Edited by: Stew Ashton on Jan 14, 2013 2:07 PM
                      • 23. Re: Double check and locking or conditional insert and multi-threading
                        ascheffer
                        If you want it simple, just keep inserting your configurations (and don't care about unique name,key etc) and use only the new config id for referencing to a configuration.

                        BEGIN
                        INSERT INTO config (name) VALUES (l_config_name);
                        EXCEPTION
                        WHEN DUP_VAL_ON_INDEX THEN
                        -- at this moment row ca be deleted by another thread
                        SELECT id INTO l_testconfig_id FROM config WHERE name = l_config_name;
                        END;

                        Does that mean that your "immutable" configurations can be deleted (by another thread)?
                        In that case you have more troubles than searching for a thread safe insert and you certainly need a lock an your whole "process"

                        Edited by: ascheffer on Jan 14, 2013 2:06 PM
                        • 24. Re: Double check and locking or conditional insert and multi-threading
                          Bawer
                          Denis Kishenko wrote:
                          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;
                          define this query with kewords FOR UPDATE to lock through a SELECT-statement like follows:
                          set serveroutput on
                          declare
                          str_to_compare varchar2(1000);
                          begin
                            --this select statement locks now your row with given id, so that another sessions must wait on this place
                            for i in (select * from config where id in (select id from config where hash = 0) 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
                                    str_to_compare := i.name || ';'  ||j.properties;
                                    
                                    --do your business logic
                                    
                                end loop;
                            end loop;
                            commit; --don't forget commit to unlock
                          end;
                          • 25. Re: Double check and locking or conditional insert and multi-threading
                            Denis Kishenko-Oracle
                            BluShadow thanks for MERGE, I think this is best solution since its atomic (some kind of "double check with lock")

                            ascheffer sure, lets replace "deleted" to "inserted"

                            Bawer for update locks existing records but doesn't lock inserting
                            • 26. Re: Double check and locking or conditional insert and multi-threading
                              ascheffer
                              Merge may be atomic, but you whole process of inserting or retrieving a config id needs to be atomic as far as I understood it. And that can't be solved by a simple merge.
                              What about a unique key on config name and hash? What are the chances you will get a hash collision with a SHA-256 hash and your dataset?
                              • 27. Re: Double check and locking or conditional insert and multi-threading
                                Denis Kishenko-Oracle
                                Merge may be atomic, but you whole process of inserting or retrieving a config id needs to be atomic as far as I understood it. And that can't be solved by a simple merge.
                                Why? As I mentioned before config+properties are immutable so retrieving is always thread-save, isn't? Merge makes thread-safe insering if required (add row if it doesn't exists), after merge I can quaranty that config is in the table. This is classical double-check-with-lock pattern.
                                What about a unique key on config name and hash? What are the chances you will get a hash collision with a SHA-256 hash and your dataset?
                                Almost any hash is not uniqie in common case. Without merge even unique name doesn't solve thread-safe inserting I have provided examples above.
                                • 28. Re: Double check and locking or conditional insert and multi-threading
                                  ascheffer
                                  But I can't see how merge would help in this case.

                                  Let's say you merge your name into the config table, with the your restriction that the name is not unique but that you new also all the key/value pairs
                                  And that you can detect that a new record is inserted.
                                  You start inserting records in the propery table.
                                  But after the first insert a second thread with the same "config" kicks in
                                  This new thread does a merge into the config, and again decides that it has to do an insert.
                                  So you will end wiith boths threads storing the same config.

                                  Or do I miss something?
                                  • 29. Re: Double check and locking or conditional insert and multi-threading
                                    Bawer
                                    Denis Kishenko wrote:
                                    Bawer for update locks existing records but doesn't lock inserting
                                    Denis, my suggestion is only the idea, you can implement it with your business logic.

                                    If you have no data in table, you have no problem, just insert it in config table (which fires locking because of primary index) as first step (with handling of duplicate key exception) and than open it again with for update. It should work.