1 2 3 4 Previous Next 52 Replies Latest reply: Jan 16, 2013 11:02 AM by Denis Kishenko-Oracle RSS

    Double check and locking or conditional insert and multi-threading

    Denis Kishenko-Oracle
      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
          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
            Denis Kishenko-Oracle
            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
              Ever thought on creating a unique key on your table?
              • 4. Re: Double check and locking or conditional insert and multi-threading
                Purvesh K
                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
                  Denis Kishenko-Oracle
                  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
                    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
                      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
                        Denis Kishenko-Oracle
                        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
                          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
                            Denis Kishenko-Oracle
                            I want to avoid duplicates of "name;key1=value2;..."
                            • 11. Re: Double check and locking or conditional insert and multi-threading
                              Billy~Verreynne
                              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
                                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
                                  Billy~Verreynne
                                  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
                                    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